One to Many, but Identifying One of the Many as the 'Default'
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.
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)