Hi,
For some time I'm struggling to get my schema "optimised" for a sort of
"message exchange" (or "document circulation") system.
For every record in the table of those messages I have:
1. SENDER
2. RECEIPIENT
3. unique (sender assigned)SN
4. ... and naturally all the other stuff, like the message itself,
timestamps, etc.
My plan is to have it unique-constraint against 1+3, for joins and to
keep the "sanity bonds" in force all the time.
So I figure to have:
ALTER ...msgs ADD CONSTRINT sender_uniq UNIQUE (sender,SSN);
Unfortunately all that proved to be "not so good" for application level,
since there I "almost always" a need to select "MY" messages, which lead to:
SELECT * FROM msgs WHERE sender = "ME" UNION ALL SELECT * FROM msgs
WHERE receipient = "ME";
Which does not look so bad, but when one has to JOIN it with other
stuff, the application becomes "obfuscated" with complexity of those joins.
So I tried other approach. A table with columns like:
1. ME
2. THEM
3. FROMME bool (true if ME is sender, false otherwise).
4. sender unique serial (SSN)
6. .... and the rest of it.
But this time I had to partition this table (on FROMME value), to be
able to correctly create different constraints depending on FROMME being
true or false. So I have:
ALTER ...msgs_from_me ADD CONSTRINT me_uniq UNIQUE (ME,SSN);
ALTER ... msgs_to_me ADD CONSTRINT them_uniq UNIQUE (THEM,SSN);
Now application level selects and joins are much simpler, like:
SELECT * FROM msgs m JOIN partners p USING (them);
But along the run, the specs for the system evolve, and currently I need
to asssign an additional unique serial, which sequentially lables every
message that "belongs" to ME irrespective if ME originated it or ME is a
recepient. And it have to be explicitly unique constrained for FK.
My problem is, that currently the table is partitioned.
Is there a way to have a unique constraint across partitions (inharited
tables)? And I'm not looking back to the initial (single table) schema,
since I'm unable to sreach my head around the concept of a unique
constraint that is able to cover IDs, which sometimes are in the SENDER
column, while on other times in RECEPIENT.
Can anybody suggest any other way out of this mass? that is, apart from
siging off ;7
Thenx,
-R