List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:September 19 2011 4:32pm
Subject:Re: table design question
View as plain text  
Thank you very much for all the insightful advice. I will keep the
separated.

2011/9/19 Jerry Schwartz <jerry@stripped>

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