Indicating Primary with a Many to Many Relationship
I've got a people table and a locations table that have a many to many relationship with each other. I need to be able to flag a single location as the primary location for each person, but if I put this flag in the location table I'm afraid it will make it the primary for other people connected to that location. I'm not sure how I can indicate a single location on the people table to make it primary, but ensure the rest of the locations are still on the list.
Add a reference association on People that referes to the PrimaryLocation.
In business logic you can make sure that PrimaryLocation is allways a member of the many to many reference set association
A validation that checks the membership of the Primary location in the reference set before commit and raises an error with validation feedback if it is not a member.