Aasmund,
> Well, the first time I thought your solution, I chose not to use it
> as it requires large modifications in my data-structure. However it
> has grown on me and I am now considering whether or not to use it. My
> first thought though: your usq is very much like postgres' oid's.
Yes. My original design had been to use the OIDs for this purpose, but
a couple members of the core team pointed out some difficulties in using
OIDs as an integral part of your data structure.
> I
> have read somewhere that the postgres oid's are not really unique, is
> this true?
Only for very large databases. OIDs are a 4-byte signed integer,
meaning an upper limit of 2.4 billion. If your database accumulates
more total objects than that, the OIDs will start to "roll over".
However, this only becomes a problem for the database when objects of
the same type get assigned the same OID ... which nobody has yet
reported in the field.
>secondly do you use your usq's to perform updates on views
> which are joins of tables where rows in two or more tables share a
> usq (but being unique in each table).
Nope, since the whole point of USQs is to be unique in the database.
This is entirely possible due only to PostgreSQL's independent sequence
implementation, which allows me to be sure that now two distinct rows
will ever have the same USQ.
> Finally, do you not feel that these USQ are in contradiction to many
> of the philosophies entrenched in SQL? (not that it matters :).
Yes. Fabian Pascal and CJ Date would chew me out for this design, I
think. It does not adhere to the exisiting rules for Relational
Database Design, and the implementation is entirely proprietary, due to
the dependance on a platform-specific sequencing mechanism. Further, as
Dado pointed out, it's very difficult from the multi-child table to tell
what where the parent record is. Lastly, I cannot use standard
REFERENCES constraints, having to instead write my own constraints and
triggers, further limiting RDBMS platform independance.
However, given all that, the convenience of this design is enormous, and
consistency-checking procedures are easy to write given the
consolidation of most activity into a few tables.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
