I have a table called user which has fields user_id, username, password, email. This table just contains baasic login information of the user.
I have a second table called user_info which has user_id, first_name, last_name, location, age. user_id is a foreign key pointing towards user_id in user.
Really these two tables could be combined into one, but I kinda like having the core info in one table and all the extra junk in another. Should they be combined or left seperate?
Second problem:
in the user_info table the location field should actually be a foreign key, city_id. I am not quite sure how to normalize the city, state, and country tables. This is what I was thinking:
table: city
city_id primary key
state_id foreign key
name
(composite unique key on (state_id, name))
table: state
state_id primary key
country_id foreign key
name
abbreviation
(composite unique key on (country_id, name))
table: country
country_id primary key
name
abbreviation
(unique key on (name))
Is this normalized properly? Or is there a better way to go about this? Any comments or suggestions on ANYTHING I posted is more than welcomed. Feel free to tear this schema apart
Thanks,
Shawn