One to Many, but Identifying One of the Many as the 'Default'

Question

I have a tables Person and Address. A Person can have multiple Address records, so a simple 1..* relationship with Address having a field referencing 'Person ID'.

Now, for a given Person I wish to identify their 'default' or 'primary' Address.

I've came up with two ideas, but I'm not convinced about either. Before I decide, can anyone offer any comments with regards to potential issues I could face down the line with either option...

(a). Could have a 'Default Address ID' on Person which would store the ID of the default Address record. Possible pitfall here is that an Address not belonging to this Person could be set here, so would need an additional check constraint to prevent this.

(b). Could have a 'Default' flag on the Address table, but this has the possibility of allowing multiple selections, so would need further checks so that when setting the flag, it is also cleared on all records belonging to the same Person.

Any

Answer 1

I would go with (B) and then safeguard the setting of the default bit.

From reading your comments on your question I want to add that to enforce that there is always at least 1 address set with the default bit you just need to handle that in your stored procedure.

Something like:

for an insert:

DECLARE @IsDefault bit;
SET @IsDefault = 0;

IF NOT EXISTS (SELECT * from tblAddresses WHERE PersonID = @PersonID And Default = 1)
BEGIN
   SET @IsDefault = 1;
END

INSERT INTO tblAddress (.... Default ... )
       VALUES (... @IsDefault ... );

for an update:

IF (@Default = 1)
BEGIN
   Update tblAddress
   SET
   tblAddress.Default = 0
   FROM tblAddress
   WHERE tblAddress.PersonID = @PersonID;

   Update tblAddress
   SET
   tblAddress.Default = 1
   WHERE ID = @AddressID;
END
ELSE
BEGIN
   IF EXISTS (SELECT * FROM tblAddresses WHERE PersonID = @PersonID AND Default = 1 AND AddressID != @AddressID)
   BEGIN
      UPDATE tblAddresses 
      SET Default = 0
      WHERE AddressID = @AddressID;
   END
END

In addition, you could prevent this from your user interface as well, but it doesn't hurt to have an extra layer of protection in the DB.

Answer 2

Another option is (normalizing the data by) adding another table DefaultAddress:

Person
------
PersonId
... other stuff
PRIMARY KEY (PersonId)

Address
-------
AddressId
PersonId 
... other stuff
PRIMARY KEY (AddressId)
FOREIGN KEY (PersonId)
  REFERENCES Person(PersonId)

DefaultAddress
--------------
AddressId
PersonId 
PRIMARY KEY (AddressId)
UNIQUE KEY (PersonId)           --- every person has (max) one default address
FOREIGN KEY (PersonId, AddressId)
  REFERENCES Address(PersonId, AddressId)