Re: My honours project - databases using dynamically attached entity-properties

Edward Stanley wrote:
> Was wondering if people would mind having a read over what I plan to do for my
> undergraduate honours project - you can get the proposal here:
>
> http://www.mcs.vuw.ac.nz/~eddie/489_Proposal.pdf

ISTM that what you're calling "dynamically attached properties" are
generally called Entity-Attribute-Value schemas. In my experience, EAV
schemas are usually result of improper database design by someone not
understanding the relational theory and the principles of normalization.
EAV offers a false sense of flexibility: sure, you can store whatever
you want in an EAV database, but because the data isn't well structured,
building queries becomes extremely complicated. No amount of syntactic
sugar is going to fix that, you just need to normalize your data.

> What I'd basically like to know is
> a) Is this problem worth solving?
> b) Is there already a good solution (particularly, within PostgreSQL)?

You could create a view on top of the normalized schema. There's also a
contrib module called "hstore" which might be relevant.

> c) Any other feedback or comments you may have.

I don't know how wedded you are to your subject or your ambitions, but
let me suggest a few other ideas anyway:

- Temporal extensions. IIRC, two people/groups have recently come up on
hackers, planning to do their thesis on that subject. It's a very real
problem, so more people working on it wouldn't hurt. See
http://www.amazon.co.uk/Temporal-Data-Relational-Model-Investigation/dp/1558608559
for the right way to solve it

- Recursive queries.

- Materialized views.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com