>-----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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
