Hi Rafal: On Fri, Mar 4, 2016 at 11:46 PM, Rafal Pietrak <rafal@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
