W dniu 28.02.2016 o 03:35, David G. Johnston pisze: > W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze: > > Can anybody suggest any other way out of this mass? > > > The only thought that sticks while reading your prose is: > > message ----> message-person <---- person > > > message-person (message_id, person_id, relationship_type[sender, receiver]) Sorry for the prose. The only way I think I can explain myself about concepts that I don't fully grasp is ... the flood of words. But regarding the matter at hand. If I understand it correctlyl, your suggestion for me is to have: CREATE TABLE persons(person_id primaty key, ...); CREATE TABLE msgs_person(msg_id, person_id references persons(person_id), rel_type, the_message_itself, primary key(message_id, person_id,rel_type),....); I must say, that this is like my 10th version of my response to your post ... with every iterration I've figured out more functionality that I get from your suggestion ... and actually the message-person table is pretty much what I currently have. Even the FK from MOST_RECENT table, was doable, after I've suplemented it with RELATION_TYPE field with a constant value of "SENDER", thus hitting the index that is on the messages-person table. The later got me thinking of SQL definition missing a way to put constant into FK definition, like this: ... ADD CONSTRAINT messages_fk FOREIGN KEY (person_id, message_id, "sender") REFERENCES msgs_person(person_id,message_id,rel_type); but that's beyond this thread. > > Partitioning and partial indexes both have considerable limitations that > you might need to work around. That said normalization exists for a > reason and having multiple "person" columns in a table is a form of > duplication that if left presents just the problems you are seeing. > > I suspect your SSN should fit onto the message-person table. > > The following doesn't make sense - if the SSN is sender unique then > there is no expectation that a receiver would not receive two messages > with the same SSN from different senders. I don't get it. Of cource it's possible to receive two messages with the same SSN. By "sender unique" I mean, that every sender has full control of whatever he/she wishes to use for SSN, provided that he/she does not assign duplicates. It also means, that there is no relation between SSN assigned by different senders and collisions *should* be expected unless UNIQUE covers both THEM/SENDR and SSN. Thus: > ALTER ... msgs_to_me ADD CONSTRINT them_uniq UNIQUE (THEM,SSN); > > David J. But thenx for the answer. -R -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
