From: Jan Steinman Date: September 21 2011 8:30pm Subject: Re: table design question List-Archive: http://lists.mysql.com/mysql/225796 Message-Id: <110B90BC-6A08-4512-8C0D-626B62077E7C@Bytesmiths.com> MIME-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable > From: Richard Reina >=20 > 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 ::::