Inserting rows with foreign key

Other SQL Server Topics
1

Hi! I'm newbie in SQL and for porpouse of leanging I want to create a system to register users.
I made these tables:

  • account(id, email, password)
  • address(id, city, street, number, phone)
  • user(id, name, lastName, c_id, d_id)
  • c_id = foreign key for account
  • d_id = foreign key for address

I'm doing this way because if I want to register company for instance, which has account and address I could create a table for "company" and use the other ones.

So, if I want to register a new user I run three commands of insert.
INSERT INTO account(email, password) VALUES('user01@gmail.com', '12345')
INSERT INTO address(city, street) VALUES('city01', 'street01')

And here is the problem. For c_id I know how to get the id.
INSERT INTO user(name, lastName, c_id, d_id) VALUES('user01', 'lastname01', SELECT id FROM account WHERE(email='user01@gmail.com' AND password='12345'), ?)

But how can I get the id from address considering the user doesn't type all fields only the mandatory(city, and street)?

Maybe my table scheme is not right. Could you make it better?

2

Use scope_identity() ?

INSERT INTO account(email, password) VALUES('user01@gmail.com', '12345')
SELECT scope_identity() -- Get the ID of the just-inserted record
3

Or I could change a little bit the tables. I could create a foreign key in address for account id and remove d_id from user table. Then i could make this insertion like this:

  • INSERT INTO account(email, password) VALUES('user01@gmail.com', '12345')

  • INSERT INTO address(city, street, c_id) VALUES('city01', 'street01', SELECT id FROM account WHERE (email='user01@gmail.com' AND password='12345'))

  • INSERT INTO user(name, lastName, c_id) VALUES('username01', 'lastname01', SELECT id FROM account WHERE (email='user01@gmail.com' AND password='12345'))

What do you think? Which one is better?

But the problem is if I want to select a user that lives in a x city. How do I do that?

4

I don;t think using the email in the WHERE clause is a good idea. Better to use a well-defined single value not a text string.

e.g. if the account table has an id field:

Insert into account...
DECLARE @id int = scope_identity()
insert into address ... values (city, street, @id)
insert into user... -- same idea