On 23/05/09 06:05, Rodrigo E. De León Plicet wrote: >>> Is there a better way? >> >> Yeah, natural keys. > > +1. > > Also, what Ben described reeks of EAV. > > Ben, please read: > > http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html Ah yes, the great Celko. I had the honor of being "lectured" by him on a similar topic, and to tell the truth, I'm still unconvinced. I had inherited a generally well-designed database with a clear relational structure, apart from one table about which I was uncertain. This table collected several groups of attributes/values that were not necessarily related to each other, and could (should?) be split into separate tables. It was nowhere near as bad as the example in the linked article, and we did have check constraints in place. The values were used to classify and sometimes "tag" rows in other tables. I hesitated to break this table up, because that would have meant - doubling the number of existing tables (70 to 140) - significant code changes all over the place - having a lot of very small tables with identical layout "id" (surrogate key) "entry" (string; used to refer to this row by the application) "label" (string; visible on the front end) Here's an example of the value groups that were contained in the table: fax status: pending, active, sent, error department: office, accounting, it, legal, experts deadline type: official, unofficial ... Using the "entry" field as natural keys would have been possible in some places, but not everywhere, and the labels still had to be editable by the users. Some of the "entry" strings were rather verbose (>40 characters), which would have made the other tables a lot larger. It also didn't "feel right" to have so many tiny tables with only 2-5 rows. Is it really advisable to put all these values into 70 separate tables with the exact same layout? I don't quite see the benefit. - Conrad -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
