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?
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?