On Tue, Jul 26, 2011 at 11:06 AM, David Johnston <polobo@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
