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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
