Re: normalization question
> I was thinking I could store the state as an
> integer StateID. I could then have a table of
> states, something like:
> ______________
> |ID| State |
> |1 | California |
> |2 | New York |
Seems ok to me, only if you need to refer to do countries as well, you need to add a column so the state knows what country it belongs to
> One possiblity is to have a
> cities table like this:
> ____________________________
> |StateID | CityID | City |
> | 1 | 1 | Los Angeles |
> | 1 | 2 | San Francisco|
> | 2 | 1 | New York City|
I assume StateID and CityId together would be unique, yes?
> The other option is to have tables for each state,
> named something like Cities_NewYork and
> Cities_California. That way, each cities table
> would only contain a city ID and city name.
?uhhh...Why would you shun a reference to a the state the city belongs to?
> I think the better choice is the second option.
Why do you think that?
> However, when I read some tutorials on
> normalization nobody mentioned doing anything that
> involved figuring out the name of a table on the
> fly.
That's because you almost never need to do this, at least not to logically structure your data. A typical relational solution relies on creating a (that's one) table for each distinct type of information, and avoiding redundance by using references (with or without explicit foreign keys) between different relation. Cutting up a table is usually done either for storage managment or query performance puposes, and only in case one needs to handle really huge amounts of data.
And ,as for the piece of you program that is going to generate that table name, where's that going to get the information from to do that? yet another table? Doesnt seem like a good idea to me..
> So, which would you choose? Or is there an even
> better structure I haven't thought of?
I don't know if there's a better solution, a solution needs to be as good as your problem requires it. In your case, I'd do like this:
1) let's not worry about country right now. your example requires only a cityname and statename,right?
2) State. In a minimal case, it would at least contain the name of the state. We're not worrying about countries right now. Statenames would be unique, and NOT NULL.
3) City. In a minimal case, it would at least contain the name of the city, as well as the name of the state it was in. Both cityname and statename should be unique, and statename references State.
This is your minimal scenario.
G.Luck