Re: New DB-design - help and documentation pointers appreciated

On 20/07/10 18:14, Rikard Bosnjakovic wrote:

> However, I feel that this design is the same design I seem to use for
> all my databases, and in the end I always find that I designed them
> wrong from the beginning. The table "components" feels like that one
> is going to be locked into a corner; it seems to "fixed" and not as
> flexible as I want this database to be.

Flexibility in storing and structuring data isn't a strong point of SQL
databases. That said, there *are* some workarounds. I've mentioned EAV
and why it's ... ugly.

A potentially superior option I didn't think to mention before is
hstore. You can use a hstore field to store key/value "extension" data
that isn't easy to model in a generic relational way without landing up
with hundreds of tiny tables. See:

http://www.postgresql.org/docs/8.4/static/hstore.html

That way you can store the common stuff in a typical relational form for
easy querying and manipulation, but can fall back to key/value for
hard-to-model attributes that might be quite specific to particular
classes of component.

I guess you could even have your subcategories carry a column that
listedrequired hstore keys as an array, so that you could require that
all components of a particular subtype have a certain list of hstore keys.

(I often wish SQL would see some extensions to support a more ...
flexible ... representation of data. SQL is great for accessing purely
relational data, but it'd be so nice to be able to break the relational
mould where required without having to replace the entire database
system just because some of your data doesn't quite fit. There are
workarounds of a sort (EAV - ugh!, hstore, etc) but the lack of
convenient language support limits them somewhat.)

--
Craig Ringer