I've got a couple of tables that store contacts and their relationships for storing contacts. Simplified versions are as follows:Contacts========ContactId int (PK)ContactName nvarchar(50)Relationships=============RelationShipId int (PK)ContactId1 intContactId2 intRelationshipTypeId int
There are foreign key relationships between ContactId and ContactId1, and ContactId and ContactId2I need to be able to mark one relationship for each user as the default and contacts sharing a relationship may have different default relationships.e.g.ContactId ContactName1 Person A2 Person B3 Person C4 Person DRelationshipId ContactId1 ContactId2 RelationshipTypeId1 1 2 12 1 3 23 2 3 14 2 4 25 4 1 16 1 2 2
Defaults may then be:ContactId DefaultRelationshipId1 22 13 34 5
My question is how should I record this default relationship in the database? Given it's to do with a Relationship the obvious place is the Relationships table but the problem here is that a Contact can exist on either side of a Relationship and the default must be across both sides. I could add Default1 and Default2 columns but this becomes quite messy to handle with setting and clearing the defaults.I could add a DefaultRelationshipId to the Contact table but this would allow me to set relationship that might have nothing to do with the Contact (not a good option, I would have thought).Any advice as to the best approach?Many thanksAlan
Database design question
Posted - 2010-01-12 : 15:57:17
