Efficient relationships?

Hey guys, I have a quick design problem that I need assistance with. I've been tasked (along with others) to redesign the central database that drives our organization. We basically have everything under control except for one aspect. To give you a brief overview, I have included a short summary of the issue below:

The issue revolves around the relationship between a demographic and address table. The demographic table contains such data as DemoID, FirstName, LastName, MaidenName, DOB, SSN, etc. The address table contains such data as AddrID, Address, City, State, Zip, etc. One requirement was to constrain a one-to-one relationship between the address and the demographic. Basically, each demographic record (person) may have one and only one physical address.

This works great by just including the AddrID in the corresponding demographic record. However, an issue arrives when we need to relate additional addresses to that demographic record. I know it sounds kind of confusing, but I’ll try to elaborate. Basically, each demographic record has ONE well-known, good address. But some of the applications that are developed around the database need additional addresses for information such as business addresses, and a few others. We would still like to capture these addresses in the same address table but we’re not sure how to enforce a one-to-one relationship between the demographic record and the ONE well-known, good address, while at the same time allowing the demographic records to have multiple addresses for other application specific information.

A few ideas that we came up with (all of which do not work) are:

1) Enforces a one-to-one relationship by including the AddrID foreign key, but can't differentiate between the ONE-known good address and other addresses.


2) Allows for multiple addresses, but loses one-to-one relationship between one demographic record and one address record.

3) A controversial design that will include the ONE-known good address in the demographic record and then allow the many to many relationship to allow multiple addresses for demographic records.


As you can see, there are problems with these designs, but I'm not sure which one of these (or none at all) would be the best design for the above requirements. Open to any and all suggestions. I know the length of this question is long, but any help would be tremendously appreciated. Thanks in advance.

blparker

The solution depends a lot on reporting perfromance and where you want to keep your business logic.

BTW According to what you posted, there's no difference between Option 2 and option 3 since you still have the FK AddrID in the demographic table.

Option 2 is the easiest to code and maintain since ALL addresses end up in the same table you do not need to do anything special.

There are 2+ versions of Option 2 .

The first (2A) is exactly what you have with both a FK in the Demographic table and an Association table.

The second option (2B) is to remove the FK from the Demographic table and Add a Primary_Flag column to the Association table. So you have this:

Demographic
PK, DemoID
etc

DemoAddr_Assn
DemoID
AddrID
PrimaryFlag - Set to True for the required address and false otherwise.

Of course as soon as you implement option 2B you'll want to know what the other address really are. So the typical design is: (2B+)

DemoAddr_Assn
DemoID
AddrID
AddrTypeID

AddrType
AddrTypeID
AddrType - (Primary, Mailing, Billing, Shipping, etc)


The key Advantage of option 2A is retrieval performance. This requires a bit more processing when you enter an address since you need to check if the FK field in the Demographic table is populated and then what happens if the FK address is actually the wrong one?

The key advantage of the 2B options is maitenenace. It is very easy to add or re-task addresses. However you do need to add code to make sure that at least one record exists in the association table.

If you decide on either of the 2B options you will want to create a stored query (view) that gives you the Primary address.

Hope this helps.