List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:September 19 2011 2:08pm
Subject:Re: table design question
View as plain text  
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 <gatorreina@stripped> 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@stripped

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