Re: variant column type

On Tue, Jul 26, 2011 at 11:06 AM, David Johnston <polobo@yahoo.com> wrote:

> Given “feature” and “car-feature” tables the presence of absence of an entry
> in “car-feature” will accomplish your desire for true/false - i.e., “the car
> has an airbag”.  By abstracting just a little every “feature” can be boiled
> down to a label/ID and then the added to “feature” and associated via
> “car-feature”.
>
> 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.

I would personally create three tables:

1)  car (id, top_speed, ec)
2)  features (id, feature_name, etc)
3)  car_has_feature (car_id, feature_id)

This has the benefits of allowing you to track additional information
about features.  For example, you could track that seatbelts are
required in Washington State on all cars manufactured after a certain
date.  The array functions in PostgreSQL are powerful enough to handle
queries of features by car pretty well, or that federal law requires
that certain airbag features are required.

Now, there are a few cases however where key-value-mapping is both
necessary and works and where variant column types are needed (for
example, storing application settings, or argument lists for the
functions that menu items call).  In those cases you have to have
somewhere that knows what the type is supposed to be and checks it.
That's really not a trivial problem because keeping things to a single
point of truth approach is very difficult with such relatively
unstructured data, which is why in applications where I have to do
this, we require that the table be updated through stored procedures
which do this checking.

Best Wishes,
Chris Travers