I am designing a mySQL database.

I have the problem however whereby I have a table representing relationships, i.e. person1 and person2 are related. I only want one record representing this relationship however and I'm not sure how to define the primary key so that both records (person1-person2 and person2-person1) are not allowed in a single table, rather only one of the two records, as having both records is redundant.

Regards,

Stephen Adams

There is no primary key variation that covers what you are looking to achieve, however, by using a simple technique during the insert by always adding the lower value in the first field and the higher value in the second field then a primary key on person1, person2 will work. The technique can be done programmatically through your programming language or by creating a stored procedure and using this as an API. Any time an insert needs to be performed on this table then use the stored procedure which includes the logic of placing the lower value first and the higher value second.

the drawback to this is that any query looking for friends will require a UNION

if you were to add both rows -- person1,person2 and person2,person1 -- then your query would be a lot simpler and more efficient

it's a tradeoff, which i know i woulda solved one way back in the day when disk space was expensive (hunnerts of bucks per kilobyte), and which i would solve the other way in this day when a terabyte of space is only a couple bucks