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('email@example.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(firstname.lastname@example.org' 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?