One for many, but defining one of many as "Default",

Question

I have a table Person and Address. A person can have several address records, so a simple connection 1 .. * with the address has a field that refers to the "Identifier".

Now for this Person, I want to define their "default" or "primary" Address.

I came up with two ideas, but I'm also not sure. Before I decide if anyone can suggest any comments regarding potential issues that I might encounter in turn with any option ...

(but). May have a "Default Address Identifier" for a Person that will store the ID of the default address entry. A possible mistake here is that an address that does not belong to this Person can be set here, therefore, to prevent this, an additional restriction on verification will be required.

(b) It may have the “Default” flag in the Address table, but this has the ability to allow multiple selection, so further verification is required so that when the flag is set, it will also clear all records belonging to the same Person.

Any

Answer 1

I would go with (B) and then save the bit setting to default.

From reading your comments on your question, I want to add that to ensure that there is always at least one address set with the default bit, you just need to process it in a stored procedure.

Something like:

to 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 ... ); 

to 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 can interfere with this and your user interface, but it is not harmful to have an additional level of protection in the database.

Answer 2

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

 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) 

Answer 3

Option (a) - saving the link to the address as the default address can cause a problem if you ever change the values ​​for the default address.

Answer 4

I could go with subset B, I would add the AddressType field to the address table, where you could define a primary and secondary or some common type, then you also tuned in for future address types without having to change the scheme.

Answer 5

We have a default flag and a trigger that ensures that one and only one record has a default value of 1. Be sure to write a trigger to handle multiple attachments / updates to records, as well as delete and test them. So, if this is the first record, then the field will be automatically set to 1. If the record with the field set to one is deleted, we have a business rule trigger that determines which remaining record will receive the default value. If another record is updated to 1, then an existing record that has 1 is updated to have 0 in the field. If you are not using a trigger, it is very likely that at some point you will have problems with data integrity.

Answer 6

instead of (a) it can have a "Default Address ID" for a person, what about a boolean? isDefaultAddr

To explain further, instead of having a field of type defaultAddressID in the Person table, you could just have a boolean field in the Person table, for example isDefaultAddress. Its value is true if it is the default address for this person, otherwise false.

NTN