-----Original Message-----
From: Chris Travers [mailto:chris.travers@gmail.com]
Sent: Tuesday, July 26, 2011 2:32 PM
To: David Johnston
Cc: salah jubeh; pgsql
Subject: Re: [GENERAL] 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.