table design question

--0016367fa516f75bbd04ad4bb1a0
Content-Type: text/plain; charset=ISO-8859-1

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?

Thanks,

Richard

--0016367fa516f75bbd04ad4bb1a0--

Re: table design question

--001517440f1460aa6904ad4be1ae
Content-Type: text/plain; charset=ISO-8859-1

I would design three tables:

Table1 (states):
ID, name, abbreviation

Table2 (state_item):
ID, state_id (from states), item_id (from item_type), item_value (varchar)

Table3 (item_type):
ID, item_name

Into the item_type table you can insert:

Nick Name
Motto
Name origin
Facts
SomeOtherDataPoint
SomeOtherDataPoint2

etc

Now, you can have as many nick names per state as needed, some states may
have 1, some 50, etc. Same for every other data point you want to keep track
of for each state as well.



On Mon, Sep 19, 2011 at 8:55 AM, Richard Reina wrote:

> 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?
>
> Thanks,
>
> Richard
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001517440f1460aa6904ad4be1ae--

RE: table design question

>-----Original Message-----
>From: Richard Reina [mailto:gatorreina@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: jerry@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=gcdmg-mysql-2@m.gmane.org

Re: table design question

--0016364d2c79c584c604ad4de380
Content-Type: text/plain; charset=ISO-8859-1

Thank you very much for all the insightful advice. I will keep the
separated.

2011/9/19 Jerry Schwartz

> >-----Original Message-----
> >From: Richard Reina [mailto:gatorreina@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: jerry@gii.co.jp
> Web site: www.giiresearch.com
>
>
>
>

--0016364d2c79c584c604ad4de380--