Hi,
W dniu 04.03.2016 o 18:59, Francisco Olarte pisze:
[----------------]
>
> Make sender_person_id NOT NULL in messages if you want to insure every
> message ahs exactly ONE SENDER, leave it out if you want to allow
> senderless messages. An FK column must either link to a record or be
> null.
>
> Then, if you want to have a msgs-person ''table'' I would use a view:
>
> CREATE VIEW msgs_persons as
> SELECT message_id, sender_person_id as person_id, 'SENDER' as role from messages
> UNION ALL
> SELECT message_id, recipient_person_id as person_id, 'RECIPIENT' as
> role from recipients
>
Ha!
This was my initial schema .. with the addition of one "super table",
that the two above (sender_person_id and recipient_person_id) both
inharited from (to avoid the UNION ALL when selecting everything).
With that layout, the NEXT column worked just fine.
Only then came the requirement to have a "possibly sequence-continues"
unique ID assigned to every message irrespectively if a particular
person was a sender or a recipient of that message_id. And I couldn't
figure out how to implement it across separate (even if inharited) tables.
So came the concept of single table of messages, with ROLE field and a
partial unique constraint on sender+sender-message-id ... and I've
sterted to rewrite the schema, but at certain point I realized that it
broke the NEXT functionality and I cannot imagine any way to reintroduce
it into the new table layouts.
Now I'm quite stuck here.
[--------------]
>
> Sorry for the tme I've taken, but I feel I can not be of any help here.
>
It's quite all right. Sometimes help comes from the discussion alone (as
opposed to direct explanations)... It also happened to me that the
solution came to me while I was describing my problem to the list ...
but haven't send the email yet, and didn't have to since the problem got
solved :)
So thenx,
BTW: I'm considering your sugestion of replaceing NEXT with the
timestamp. The primary reason for the NEXT is to be able to fetch a row
"just preceeding" currently inserted new one AFTER the insert is done
(in trigger after), so that some elaborated "statistics" get updated in
that "one before" message record. May be timestap would do instead...
-R
