Thank you very much for all the insightful advice. I will keep the separated.
2011/9/19 Jerry Schwartz <je...@gii.co.jp> > >-----Original Message----- > >From: Richard Reina [mailto:gatorre...@gmail.com] > >Sent: Monday, September 19, 2011 9:55 AM > >To: mysql@lists.mysql.com > >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: je...@gii.co.jp > Web site: www.giiresearch.com > > > >
