List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 19 2011 2:21pm
Subject:RE: table design question
View as plain text  
>-----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



Thread
table design questionRichard Reina19 Sep
  • RE: table design questionJerry Schwartz19 Sep
    • Re: table design questionRichard Reina19 Sep
  • Re: table design questionJohnny Withers19 Sep
Re: table design questionJan Steinman21 Sep