Re: multiple UNIQUE indices for FK - Mailing list pgsql-general

Hi Rafal:

On Fri, Mar 4, 2016 at 11:46 PM, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
> 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
> 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).

Wuf. I do not like it. I would add a column named sender_person_id to
messages ( to distinguish its role ) and put a recipient_person_id, or
just person_id, in recipients ( the role is clear in that table ) to
avoid problems. Otherwise, what do you call the parent table and the
fields? It's a naming issue, nut I've found the hard way naming is
important in this things. Bear in mind you do only avoid TYPING the
union all when selecting everything ( as inheritance DOES do a union
all, it would have to do it with both kids AND the parent, so it MAY
be slower ). And you introduce several problems, the naming ones, a
very strange foreign-key relationship between kids, the possibility of
having a row inserted in the parent.

> With that layout, the NEXT column worked just fine.

I do not doubt the NEXT column works, I just doubt it's a good thing
on a relational dessign.

> 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.

Which is exactly the functionality of the NEXT column ? I mean, I see
you have messages with ONE sender and MANY? (Can they be zero? )
recipients. What are you trying to achieve with it? How are you
planning to maintain it in your dessign?

> Now I'm quite stuck here.

I ask these questions because I think we are in a case of
http://xyproblem.info/ .

> 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...

If you are planning on updating the previous row for a message ( or a
person ? ) on a trigger, this smells fishy. You may have a reason, not
knowing what you are exactly planning to do, I cannot tell, but it
sounds really weird.

Francisco Olarte.