Re: arc relationship [was: db design question]

Folks,

Getting back to Jules' original question about having a universal
"blob" table, as I said, I've done this. An example:

Table Clients(usq PK NEXTVAL('universal_sq'), client_name, etc ...)
Table Orders(usq PK NEXTVAL('universal_sq'), order_date, etc ...)
Table Invoices(usq PK NEXTVAL('universal_sq'), invoice_no, etc ...)

And the multi-relational tables:

Table mod_data (ref_usq PK INT, mod_user, mod_date, create_user,
create_date);
Table notes (note_id SERIAL, ref_usq INT, note_type, note_date,
note_user, note_text);

A simplified query:
SELECT clients.*, mod_data.* FROM clients JOIN mod_data
ON clients.usq = mod_data.ref_usq;

Both of the above tables, through the ref_usq, are related to any of
the tables possessing a USQ as the primary key. This is in use in a
production system, and has been quite reliable.

Advantages of the above approach:
1) It allows you to "attach" the "flexible child" tables to any
qualified parent table without changing the schema.
2) It reduces the number of tables in your schema, simplifying and
reducing clutter and confusion.

Disadvantages of the above approach:
1) The relationships are not enforcable through the standard SQL
foreign key constraints. As a result, you need to write your own
custom triggers and rules to enforce the desired relationships. This
can get annoying, with up to 3 triggers per relationship.
2) Any "flexible child" table has, of necessity, as many rows as the
sum total of the rows in the parent tables, or the requisite multiple
for one-to-many relations. This results in a child table that is much,
much larger than the standard model of having several different child
tables would be. As I have previously mentioned, this is a
performance drag as one large table is, in practice, slower than
several small tables.
3) Any auto-journaling system or auto-archiving system of record
changes will have difficulty working around the above ambiguous
relationships.

My conclusion is that the above is a fine approach for small databases
(the production one in which I'm using it has about 1,000-2,000 records
in each of the 6 main tables) but a poor approach for very large
databases for performance reasons.

-Josh Berkus