-----Original Message----- From: Chris Travers [mailto:chris.travers@xxxxxxxxx] Sent: Tuesday, July 26, 2011 2:32 PM To: David Johnston Cc: salah jubeh; pgsql Subject: Re: variant column type > In your example you could create a feature called ?Top Speed ? 240kph? > > If every car is going to have a particular ?feature? and only the ?value? > matters you could considering adding a ?car-properties? table: > > car_property (car id, top_speed, etc?) and populate the top_speed > column with whatever value is applicable or leave it NULL if unknown > or N/A. The relationship between ?car? and ?car_property? would be > one-to-one (1-to-1) > I don't like this approach for a couple of reasons. 1) Storing non-applicable and unknowns as interchangeable in a database schema introduces semantic ambiguity issues that are best avoided if possible. 2) While wide tables win in terms of supporting more complex constraints, they lose in terms of storage, etc. ---------------------------------------------- Agreed. But I was suggesting using the "wide-table" in addition to the "car;feature;car_has_feature" tables. Basically limit the extended table to those properties that are truly (or at least almost truly) global. There should only be a few fields. The fact that the car has a top-speed can be assumed to be global and thus calling it a "feature" is possibly abstracting things too much. If you need to display it in a "feature list" you can readily write a VIEW that will pull out that integer value from the extended table, convert it into a meaningful "name/description", and present it as a list of "Fixed Features". My main concern with the whole "feature" table is you end up going down the path of everything being a "feature" - the VIN, Make, Model, Year - where in most sane cases you'd be better off having fields for those fields since every car has one. And so, while I say use an "wide-table" to capture some of these additional values you can just add the "top-speed" field to the main car table. In this specific example there is not semantic ambiguity since we know that a car has a top-speed and so a NULL must represent an UNKNOWN value. If the NULL could represent "Not Applicable" I would probably leave it to the "feature" table. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
