List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:September 21 2011 8:30pm
Subject:Re: table design question
View as plain text  
> From: Richard Reina <gatorreina@stripped>
> 
> 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...

To me, the key question is cardinality.

You gave a big clue with "some states have more than one." This "cardinality rule" clearly
indicates you need a separate table for nick names.

I'd look carefully at cardinality, and any field in which you can say, "some states may
have more than one," put it in a separate table.

(One exception to cardinality-driven table design would be if a field is a clearly
defined, relatively unchanging set of constants. The classic example is when different
states in a process need to be recorded -- "membership" might include the set "applied",
"paid", "accepted", "withdrawn". You could have multiple states in a SET field, which
would be much less cumbersome than having a fifth-normal-form join table.)

----------------
A low-energy policy allows for a wide choice of lifestyles and cultures. If, on the other
hand, a society opts for high energy consumption, its social relations must be dictated
by technocracy and will be equally degrading whether labeled capitalist or socialist. --
Ivan Illich
:::: Jan Steinman, EcoReality Co-op ::::

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