> > From: Stefan Hinz <[EMAIL PROTECTED]> > Date: 2003/11/28 Fri PM 04:45:24 CST > To: [EMAIL PROTECTED] > CC: David T-G <[EMAIL PROTECTED]>, mysql users <[EMAIL PROTECTED]> > Subject: Re[3]: Please analyze my project table design > > >> % 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. > http://www.databaseanswers.com/normal_forms.htm > > If you're ambitious, you might want to try to abide by the other 330 > requirements for relational databases as well, but in most cases, NF 1 > through 3 is sufficient: > > http://www.aisintl.com/case/olais/pb96/biblio.htm#Codd74 > > Regards, > -- > Stefan Hinz <[EMAIL PROTECTED]> > iConnect GmbH <http://iConnect.de> > Heesestr. 6, 12169 Berlin (Germany) > Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 > > [filter fodder: sql, mysql, query] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
