>-----Original Message-----
>From: Richard Reina [mailto:gatorreina@stripped]
>Sent: Monday, September 19, 2011 9:55 AM
>To: mysql@stripped
>Subject: table design question
>
>I want to create a US geography database. So far I have categories such as
>state nick names (some states have more than one), state mottos (text 25 to
>150 characters), state name origins (100-300 characters), state "trivial
>facts", entry into union. My question is; would it be better to keep at
>least some of this information in separate tables like:
>
>state_basic
>ID | name | Incorporation | Entry in Union| Name_origin | Motto
>
>state_nicknames
>ID | name | nick_name|
>
>state_trivia
>ID | name | fact
>
>or would it be batter for queries to try to put all this information in one
>table?
>
[JS] Use separate tables. Unless you have a //very// good reason, you should
always try to normalize your data.
In other words, use separate tables unless you are positive that you will
//always// have 1:1 relationships between the various fields. For example,
even such a simple thing as the data of incorporation might have more than one
value in the case of the original colonies, the independent republics (Texas,
California), and (I'm not sure about these) the Dakotas and West Virginia.
Did you know that Maine was once part of Massachusetts? You could put that
kind of thing into a trivia record, but that might make it harder to use in
the future. My personal philosophy is that it is easier to scramble an egg
than to unscramble it. You might someday need to keep track of which states
were originally part of other states.
And remember, those things that will never happen will happen the day before
your vacation. The last thing you want to hear is "Richard, before you leave I
need you to..." (I have 45 years of experience with that.)
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: jerry@stripped
Web site: www.giiresearch.com
