>> >> % I don't know of a DB theory rule that says it's a bad idea to have the same >> >> columns in many tables, but it might make the design more compact to take the >> >> common stuff and put it into one >> table.
>> >> Oh, there is, indeed. CF "normalization" :-) >> > Well of course, but that begs the question, which normal form? >> 1, 2, and 3. There's an abundance of explanations on E. F. Codd's >> normal forms on the web; I just picked the first Google result: > Again, which normal form? >> There are three main normal forms, each with increasing levels of normalization: >> 2.1 First Normal Form (1NF): Each field in a table contains different >> information. >> For example, in an employee list, each table would contain only one >> birthdate field. > But he never gave any indication that that's an issue. >> 2.2 Second Normal Form (2NF): No field values can be derived from another field. >> For example, if a table already included a birthdate field, it could not >> also include a >> birth year field, since this information would be redundant. > But he never gave any indication that that's an issue. >> 2.3 Third Normal Form (3FN): No duplicate information is permitted. >> So, for example, if two tables both require a birthdate field, the birthdate >> information >> would be separated into a separate table, and the two other tables would >> then access the >> birthdate information via an index field in the birthdate table. Any change >> to a birthdate >> would automatically be reflect in all tables that link to the birthdate >> table. > Right, that's *nominally* possible with the tables he gave, but the chance that a > particular person is both an "agent" and a "lawyer" (or whatever) is probably about > zero. So the _true_ redundancy > is not very much at all. > Which is why I asked: how does it violate normalization rules? And I mean in a > practical sense, not in a "well, perhaps some single person *could* be both a laywer > and an agent" sense. > And I'm not disagreeing that he shouldn't combine the fields into a single table. > It's just not obvious why it follows from normalization considerations. Sorry for being unclear -- I didn't mean to suggest to consider cases when a laywer is also an agent. I might have mixed normalisation issues with other suggestions for good database design, too. What I was to say is, whenever you have the same kind of information for a number of database entities (like lawyer, agent, etc.), it's always a good idea to keep that information in a separate table. Phrased in a more hands-on way: If you store contact information for lawyer, agents, and other groups, that should go to a separate table. Regards, Stefan Hinz -- Are you MySQL certified? http://www.mysql.com/certification/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Stefan Hinz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL Documentation Team /_/ /_/\_, /___/\___\_\___/ Berlin, Germany <___/ www.mysql.com +49 30 8270294-0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
