Page 1 of 1

normalized city, state, country

Posted: Sat Jul 10, 2010 8:01 am
by shawngoldw
So over the past couple days I've been trying to get a deeper understanding of mysql. I've come across a lot of normalization and I've wrapped my head around the principals behind it but when I tried to put together a simple schema things got much more complicated. I tried to put together a user table and I'm not quite sure of some of my attempts to normalize it. My first problem:

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 :D

Thanks,
Shawn

Re: normalized city, state, country

Posted: Mon Jul 12, 2010 12:03 am
by liljester
wow, i dont know that i would normalize it that much. in your current scheme, what happens if you need to put in a secondary address for someone? a db guy once told me your database can be too normal :) at some point it becomes too complex. he suggested to me that you shouldnt normalize for the sake of normalization, and certainly dont let it complicate your table to the point its difficult to use.

just for fun, i think the most normalized i would do is:

<users>
user_id
email
username (if different from email)
password

<biograph>
user_id
first_name
middle_name
last_name
birthdate

<address>
user_id
addr_type (primary or secondary etc)
addr_line_1
addr_line_2
city
state
zip

now im no db professional, and all this info could be bad, but it seems to work for me and makes sense.

Re: normalized city, state, country

Posted: Mon Jul 12, 2010 8:05 am
by shawngoldw
Thank you. I've also read that you don't want to normalize too much or as you said, for the sake of normalizing, but I don't quite know where to the draw the line right now 8O . But in my case the address, or more specifically city, state, and country, are very important in my database. I need to be able to pull individual cities, states, and countries, quickly and individually even when they are not related to a user. And I want to be able to minimize miss spelling of addresses.

With this extra info, am I still taking it too far?

Re: normalized city, state, country

Posted: Mon Jul 12, 2010 10:40 pm
by liljester
if you are that concerned about the city/state/country then what you had in mind looks pretty good to me.