Stuck on a Database design issue

Question

Say I have the following tables:

• GearType : snowboard/helmet/shoes/whatever

• GearModel : which describes the manufacturer, size, and GearType

• Gear : represents a physical instance of a gear, belongs to a GearModel and describes its barcode, number of times it has been worn...

• Member : a person who may be assigned a Gear for each existing geartype

• GearAssignation : has a double PK MemberId/GearTypeId so a Member can be assigned 0..1 gear per geartype, also has FK GearId

My question is : Since a Gear cannot be assigned to more than 1 Member at once, how to make up a relation between Gear and GearAssignation of cardinality 1 to 0..1 ?

At best I managed to do this using a relation between Gear.Id and GearAssignation.GearId, with an unique key (remember it cannot be the PK because of the double PK MemberId/GearTypeId) on GearId. But I'd love to get rid of that UK because my software development tool doesn't recognize it.

As suggested, here is the diagram from SSMS :

Bringing heavy changes to the current model is not a problem at all for me atm. Suggestions ?

PS : dunno if it helps, I'm using Microsoft's SQL Server 2008

Answer 1

I'd do this by having a field in Gear for the member_id who it is currently assigned to.