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

Edward, Heikki,

In my experience, EAV
schemas are usually result of improper database design by someone not
understanding the relational theory and the principles of normalization.

Edward, you should be aware that EAV schema are a source of disagreement among
database designers. Some developers like Heikki and David Fetter hate them,
and other like me use them frequently when the application business logic
warrants it -- primarily cases where each item has a variable number of
attributes and a large number of the attributes are null for most items, or
when the application is expected to define new attributes which can't be
anticipated by the database designer.

No matter how much Heikki hates them, I think he'd agree that EAV tables are
better than having the application execute DDL at runtime. ;-)

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

It's pretty easy to do EAV ad-hoc using existing tools. I'm not clear on what
you're planning to implement that's not already available. Could you be more
specific?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Mar 11, 12:47 pm, j...@agliodbs.com (Josh Berkus) wrote:

No matter how much Heikki hates them, I think he'd agree that EAV tables are
better than having the application execute DDL at runtime.

EAV moves the structure that is typically in the design of the tables
into the contents of the tables. With an EAV database you have
effectively destroyed the semantic difference between DML and DDL. I'm
willing to concede that there may be situations where EAV is actually
the right answer to a design problem. I have yet to encounter one, but
Josh has more experience, and more varied experience than I do. To me,
EAV is a perfect example of ignoring the YAGNI principal.

http://c2.com/xp/YouArentGonnaNeedIt.html

What I'd basically like to know is
a) Is this problem worth solving?

I think you're solving a symptom, not the root cause of the problem.
Clarify the root cause, and then you have an interesting project.

Andrew

Amdrew,

I have yet to encounter one, but
Josh has more experience, and more varied experience than I do. To me,
EAV is a perfect example of ignoring the YAGNI principal.

I've done plenty of applications where part of the specification for the
application was "User Defined Fields" allowing the users to customize the
data structure at runtime. This is a very, very common thing; of the 15
or so commercial applications I implemented from scratch as a consultant
probably 10 of them had it.

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:

Amdrew,

I have yet to encounter one, but
Josh has more experience, and more varied experience than I do. To me,
EAV is a perfect example of ignoring the YAGNI principal.

I've done plenty of applications where part of the specification for the
application was "User Defined Fields" allowing the users to customize the
data structure at runtime. This is a very, very common thing; of the 15
or so commercial applications I implemented from scratch as a consultant
probably 10 of them had it.

Just to add a note from YAJ, custom fields are unfortunately a
requirement in most apps I have seen, from a CMS to Quickbooks. The
reality is, you don't know everything the person wants to know about a
particular set of data.

Joshua D. Drake

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Josh Berkus wrote:

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific additions to specific schemas/tables, but why not?

--
Richard Huxton
Archonet Ltd

On 3/12/07, Richard Huxton <dev@archonet.com> wrote:

Josh Berkus wrote:

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific additions to specific schemas/tables, but why not?

More to the point, since EAV is effectively smearing the semantics of
DDL with DML, what, if any of the arguments against doing DDL at
runtime don't apply equally to EAV? Well, aside from being able to say
"hey, I'm not executing DDL at runtime". :)

I see the issue as one of cost: it's substantially harder to implement
DDL at runtime than to work around the problem using EAV. If that
analysis is reasonable, then it would be a very interesting research
project to see how to cut down that cost of implementation.

Andrew

Andrew Hammond wrote:

On 3/12/07, Richard Huxton <dev@archonet.com> wrote:

Josh Berkus wrote:

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific additions to specific schemas/tables, but why not?

More to the point, since EAV is effectively smearing the semantics of
DDL with DML, what, if any of the arguments against doing DDL at
runtime don't apply equally to EAV? Well, aside from being able to say
"hey, I'm not executing DDL at runtime". :)

I see the issue as one of cost: it's substantially harder to implement
DDL at runtime than to work around the problem using EAV. If that
analysis is reasonable, then it would be a very interesting research
project to see how to cut down that cost of implementation.

Well the cost depends on where/how complex the extra fields are. If
you're just talking about adding columns usercol01..NN with different
types and possibly a lookup to a single client_attributes table, it's
not difficult.

Of course, if inheritence worked fully, you could just have core and
user versions of relevant tables.

--
Richard Huxton
Archonet Ltd

"Richard Huxton" <dev@archonet.com> writes:

Well the cost depends on where/how complex the extra fields are. If you're just
talking about adding columns usercol01..NN with different types and possibly a
lookup to a single client_attributes table, it's not difficult.

And then what? dynamically construct all your SQL queries?
Sure, sounds like a simple solution to me...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

And then what? dynamically construct all your SQL queries?
Sure, sounds like a simple solution to me...

Not to mention DB security issues. How do you secure your database when
your web client has DDL access?

So, Edward, the really *interesting* idea would be to come up with a
secure, normalized way to do UDFs *without* EAV tables. People would be
very impressed.

BTW, Google Summer of Code opens Wednesday:
http://www.postgresql.org/developer/summerofcode.html

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Gregory Stark wrote:

"Richard Huxton" <dev@archonet.com> writes:

Well the cost depends on where/how complex the extra fields are. If you're just
talking about adding columns usercol01..NN with different types and possibly a
lookup to a single client_attributes table, it's not difficult.

And then what? dynamically construct all your SQL queries?
Sure, sounds like a simple solution to me...

No different to dynamically constructing a query for a report. Simpler,
since in my experience most of these user-defined fields are just slots
for extra codes/tags/notes rather than something you'd join on.

--
Richard Huxton
Archonet Ltd

And then what? Make the search box on www.postgresql.org able to handle an
email address as search text without throwing a shoe?

Search for linus@torvalds.com or any other 'email' address from the postgres
home page. Barfage every time.
Easy for some isn't easy for all, apparently. Left that out as a test case
did we? Someone searching a mailing list for an email address? Who wudda
thunk it? It works without the . -- don't know why, but then I also don't
know why someone hasn't tried that before me.

"Sure, sounds like a simple solution to me..." Richard said sarcastically.
Would be funnier if the search on the website wasn't broken in a completely
stupid, almost ironic way. Ah, irony and sarcasm -- the ugly twins.

Yeah, we have to dynamically generate queries day in and day out. But then
some of us actually work for a living.
Since we already have to do that, maybe someone could make that easier?
Isn't that really the point here? Someone asked if something would be
useful, and the people who use the database to do real work said YES, and
here's how I might use it. Like full text seach and recursive queries, user
defined (fields|attributes|properties) and the ability to manage them would
be BUTTER! Is it a difficult problem? YES, but if it wasn't, why should it
be worth an advanced degree?

Or maybe 'we' only solve the trivial and obvious problems, like searching a
mailing list for an email address?

Sarcastically yours,
Sean

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Gregory Stark" <stark@enterprisedb.com>
Cc: "Andrew Hammond" <andrew.george.hammond@gmail.com>; <josh@agliodbs.com>;
<pgsql-hackers@postgresql.org>
Sent: Monday, March 12, 2007 7:30 PM
Subject: Re: [HACKERS] My honours project - databases using dynamically
attached entity-properties

On Mon, Mar 12, 2007 at 10:05:58PM -0700, Sean Utt wrote:

And then what? Make the search box on www.postgresql.org able to handle an
email address as search text without throwing a shoe?

Search for linus@torvalds.com or any other 'email' address from the
postgres home page. Barfage every time.
Easy for some isn't easy for all, apparently. Left that out as a test case
did we? Someone searching a mailing list for an email address? Who wudda
thunk it? It works without the . -- don't know why, but then I also don't
know why someone hasn't tried that before me.

Obviously not, since nobody has reported it before.
Fixed, insofar that we don't barf on it, but show you the no hits message.
Because all email addresses are obfuscated in the archives, they're not
actually searchable.

Thanks for reporting the issue. You're advised to report issues with the
website to pgsql-www instead for a faster response, since web people
don't always monitor all the other lists.

//Magnus

On Tue, 2007-03-13 at 00:43, Richard Huxton wrote:

Josh Berkus wrote:

I really don't see any way you could implement UDFs other than EAV that
wouldn't be immensely awkward, or result in executing DDL at runtime.

What's so horrible about DDL at runtime? Obviously, you're only going to
allow specific additions to specific schemas/tables, but why not?

Well, exclusively locking the table for DDL is not always possible in
production systems. We also shortly had a solution where we added new
columns on the fly, and we had to ditch it... it was simply not working.
The users were usually adding columns in the peek business hours, and in
the same hours it was simply impossible to take an exclusive lock on
that table. I think DDL will actually also exclusively lock parent
tables of FK relationships (I might be mistaken here, but I think I have
seen such lock), which is even worse.

After it caused extensive perceived downtime for hours, we simply
redesigned the feature so that the users need now to announce beforehand
how many different types of new columns they will need and we just
create a few extra of them, and assign them to the users needs as they
need it... the unused columns stay null and hopefully don't have too big
overhead, but it's the simplest solution we found which actually works.
When the user runs out of spare columns, the admin will create some new
spares in quiet hours.

Cheers,
Csaba.

Sean Utt wrote:

And then what? Make the search box on www.postgresql.org able to handle
an email address as search text without throwing a shoe?

Search for linus@torvalds.com or any other 'email' address from the
postgres home page. Barfage every time.
Easy for some isn't easy for all, apparently. Left that out as a test
case did we? Someone searching a mailing list for an email address? Who
wudda thunk it? It works without the . -- don't know why, but then I
also don't know why someone hasn't tried that before me.

Had a bad day?

And I'll be damned if you're not right - it doesn't return results for
dev@archonet.com but does for dev@archonet. Presumably something to do
with (not ?) splitting the email address on ".". Can't believe no-one
has noticed this before (me, for example). I guess that even though I
search a lot, it's not on email addrs.

Have you reported it to the www team?

"Sure, sounds like a simple solution to me..." Richard said
sarcastically. Would be funnier if the search on the website wasn't
broken in a completely stupid, almost ironic way. Ah, irony and sarcasm
-- the ugly twins.

Actually, it was Greg who said that. And it was *me* the (really very
gentle) sarcasm was directed at.

Yeah, we have to dynamically generate queries day in and day out. But
then some of us actually work for a living.

Umm - like all of us?

Since we already have to do that, maybe someone could make that easier?

Good idea!

Isn't that really the point here?

Not as I was reading the discussion.

Someone asked if something would be
useful, and the people who use the database to do real work said YES,
and here's how I might use it. Like full text seach and recursive
queries, user defined (fields|attributes|properties) and the ability to
manage them would be BUTTER! Is it a difficult problem? YES, but if it
wasn't, why should it be worth an advanced degree?

I think the main discussion has been around:
1. Whether Edward's final-year project is basically EAV (in which case
he'll probably need to work hard to get good marks).
2. Whether dynamically generating DDL is safe/practical in a business
setting. This seems to split some *very* experienced people about 50:50.
Half of them agree with me and the other half are inexplicably wrong ;-)

If you read section 0.3 of Edward's project proposal he argues that
dynamic DDL causes problems for the application because: "However, SQL
does not provide an easy syntax for querying these properties." (meaning
the changed structure of the database). I'm not saying this part is
easy, but I'm not convinced it's harder than doing it the "wrong" way.
At least not if you do as Edward does and enforce types.

Now, in 0.3.1 he describes a normalised webpage=>tags table pair and
shows some cumbersome-looking SQL. However, I'd argue this is due to the
verbose nature of the SQL rather than the underlying expressions.

He then looks at what I think of as the "system settings table"*
problem, where you have a bunch of configuration-settings you'd tend to
store in a single table (setting_name, value), except you'd like to have
different types for each setting (a further wrinkle is that you might
like lists of settings - do you use arrays or a sub-table?). This is
your classic EAV scenario.

Now, he proposes a set of tables - one per property, so you can enforce
type constraints, but he will need to create syntax to make this easier
to query. Presumably it'll need an automatically-generated view over the
top. (Edward - tip: read up on Date's thoughts on automatically
determining what table you can insert a row in based on its type).

This certainly looks like a variant on EAV to me, and I'm not convinced
that it's gaining much since you'll have possibly huge numbers of joins
going on in the background while not having any semantics to your table
definitions.

The other variant (which I've used myself) is to have a "type" column
and a trigger to enforce the text-value is valid for "type".

Now, if you do it "properly", that is define tables as you would as a
human designer, then you do have the problem of informing your
application on the relevant structure. Now, the syntax issues of this
are largely solved - plenty of ActiveRecord-style classes out there for
Ruby,Python,Perl,PHP,...

The interesting question then becomes where the practical limits in such
flexibility are. Simple attribute/value pairs are easy enough. How do
you feel about changes to existing tables though? How about new tables
that add relationships between existing tables? At what point does the
application just become pgAdmin?

* Another good example is the "questionnaire". Users need to be able to
define their own lists of questions and if "driving_licence=no" then
don't bother to ask "type of car=?". Edward - if you want to see a
schema that implements this sort of thing, contact me off list and I'll
see what I can do. The client for that project will probably be fine
with sharing it with one student.

--
Richard Huxton
Archonet Ltd

On Tue, Mar 13, 2007 at 09:31:45AM +0000, Richard Huxton wrote:

Sean Utt wrote:

And then what? Make the search box on www.postgresql.org able to
handle an email address as search text without throwing a shoe?

Search for linus@torvalds.com or any other 'email' address from the
postgres home page. Barfage every time. Easy for some isn't easy
for all, apparently. Left that out as a test case did we? Someone
searching a mailing list for an email address? Who wudda thunk it?
It works without the . -- don't know why, but then I also don't
know why someone hasn't tried that before me.

Had a bad day?

Today was a good day ;)

Since we already have to do that, maybe someone could make that easier?

Good idea!

Hear, hear!

Someone asked if something would be
useful, and the people who use the database to do real work said YES,
and here's how I might use it. Like full text seach and recursive
queries, user defined (fields|attributes|properties) and the ability to
manage them would be BUTTER! Is it a difficult problem? YES, but if it
wasn't, why should it be worth an advanced degree?

I think the main discussion has been around:
1. Whether Edward's final-year project is basically EAV (in which case
he'll probably need to work hard to get good marks).

As Josh mentioned, I'm on of the people who says EAV is never
justified. We do have a way of expressing wide ranges of constraints
not known in advance: it's called SQL, and people need to do some
design using it, however much they may fear that they've left
something out somehow. :)

2. Whether dynamically generating DDL is safe/practical in a
business setting. This seems to split some *very* experienced people
about 50:50. Half of them agree with me and the other half are
inexplicably wrong ;-)

For the record, I'm in the "ag'in" column.

* Another good example is the "questionnaire".

With all due respect, this is a solved problem *without EAV or
run-time DDL*. The URL below has one excellent approach to this.

<http://www.varlena.com/GeneralBits/110.php&gt;

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

David Fetter wrote:

On Tue, Mar 13, 2007 at 09:31:45AM +0000, Richard Huxton wrote:

* Another good example is the "questionnaire".

With all due respect, this is a solved problem *without EAV or
run-time DDL*. The URL below has one excellent approach to this.

<http://www.varlena.com/GeneralBits/110.php&gt;

Which broadly speaking was the solution I used for my questionnaire,
except I had a restricted set of types so basically just coerced them to
text and side-stepped the inheritance issue. To the extent that it's
dynamic, it's still just EAV though.

It doesn't remove the need for run-time DDL if you allow users to add
their own questions. If I add a "National Insurance No." (Social
security No) then either it's:
1. Plain text, and can take clearly invalid codes
2. A user-space construct with regexp matches etc (basically recreating DDL)
3. DDL.

And as the example says, you need to create the table types in advance.
If you want to add e.g. "Work History" (employer, from_date, to_date) to
a questionnaire then you'll need dynamic DDL (of form #2 or #3 above).

--
Richard Huxton
Archonet Ltd

David Fetter wrote:

I think the main discussion has been around:
1. Whether Edward's final-year project is basically EAV (in which case
he'll probably need to work hard to get good marks).

As Josh mentioned, I'm on of the people who says EAV is never
justified. We do have a way of expressing wide ranges of constraints
not known in advance: it's called SQL, and people need to do some
design using it, however much they may fear that they've left
something out somehow. :)

ISTM that the biggest problem with EAV is that is is far too seductive,
and allows for lazy design (or lack of design). There might be odd cases
(pace David) that require it, but I strongly suspect in most cases it is
not necessary.

Perhaps we need some sort of discriminant record type ;-)

cheers

andrew

Hmm, hstore + (optionally) functional indexes. Is it answer?

Edward Stanley wrote:

Hi,

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

What I'd basically like to know is
a) Is this problem worth solving?
b) Is there already a good solution (particularly, within PostgreSQL)?
c) Any other feedback or comments you may have.

Regards
Eddie Stanley

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/