January 5, 2009 at 9:26 pm
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.
+---------------+ +---------------+
| Demographic | | Address |
+---------------+ +---------------+
| PK, DemoID | | PK, AddrID |
| FK, AddrID |------| Address |
| etc. |1 1| etc. |
+---------------+ +---------------+
2) Allows for multiple addresses, but loses one-to-one relationship between one demographic record and one address record.
+---------------+ +---------------+ +---------------+
| Demographic | | DemoAddr_Assn | | Address |
+---------------+ +---------------+ +---------------+
| PK, DemoID |-------| DemoID |--------| PK, AddrID |
| FK, AddrID |N N| AddrID |N N| Address |
| etc. | +---------------+ | etc. |
+---------------+ +---------------+
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.
Code:
+---------------+ +---------------+ +---------------+
| Demographic | | DemoAddr_Assn | | Address |
+---------------+ +---------------+ +---------------+
| PK, DemoID |-------| DemoID |--------| PK, AddrID |
| Address |N N| AddrID |N N| Address |
| City | +---------------+ | etc. |
| State | +---------------+
| Zip |
+---------------+
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
January 5, 2009 at 9:58 pm
4) SOP design for this problem with one AddrID in the demographic record indicating the one good one and then allow the many to many relationship to allow multiple addresses for demographic records, plus association attributes to distinguis different kinds of "secondary" addresses:
+---------------+ +---------------+ +---------------+
| Demographic | | DemoAddr_Assn | | Address |
+---------------+ +---------------+ +---------------+
| PK, DemoID |-------| DemoID |--------| PK, AddrID |
| AddID |N N| AddrID |N N| Address |
| etc. | | AssnType | | etc. |
+-----------+---+ +---------------+ +---+-----------+
| |
1 +----------------------------------------+ 1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 10:15 pm
Hi
Nice design RbarryYoung. A slight variation from your design.
AssnType in DemoAddr_Assn can also keep that a particular address is the good one. So we can avoid AddID column in Demographic table.
RbarryYoungs design avoids a join with DemoAddr_Assn table when the good address details need to retrieved.
"Keep Trying"
January 5, 2009 at 10:33 pm
Chirag (1/5/2009)
HiNice design RbarryYoung. A slight variation from your design.
AssnType in DemoAddr_Assn can also keep that a particular address is the good one. So we can avoid AddID column in Demographic table.
Yes that is an option. However, if you do it that way, then you cannot use Foreign Keys to enforce the 1-to-1 relationship there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 10:56 pm
True. One to One relationship exists only for the good "one" address.
"Keep Trying"
January 7, 2009 at 7:10 am
Bryan - Hi
If I've understood your design problem correctly, I'd allow a 1:M relationship between the Demographic table and the Address table, but add a Flag to the Address table and use this to indicate the 'default' address. Use code to ensure consistency, i.e. only 1 address can have the flag set at any one time. It's a compromise between using the strengths of the DB and a pragmatic solution.
HTH
January 7, 2009 at 7:24 am
john.morley (1/7/2009)
If I've understood your design problem correctly, I'd allow a 1:M relationship between the Demographic table and the Address table, but add a Flag to the Address table and use this to indicate the 'default' address. Use code to ensure consistency, i.e. only 1 address can have the flag set at any one time. It's a compromise between using the strengths of the DB and a pragmatic solution.
This is the other most commonly used design (along with the one that I posted) for this kind of problem, which I think of as the "Parent with One Primary and Many Secondary Children" relationship. Almost always, one of these two designs is used in actual practice.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 7, 2009 at 9:05 am
john.morley (1/7/2009)
Bryan - HiIf I've understood your design problem correctly, I'd allow a 1:M relationship between the Demographic table and the Address table, but add a Flag to the Address table and use this to indicate the 'default' address. Use code to ensure consistency, i.e. only 1 address can have the flag set at any one time. It's a compromise between using the strengths of the DB and a pragmatic solution.
HTH
This is the design used by my company where we track multiple CompanyAddresses -- only one row can have the Primary_fg = 1.
One way to enforce this requirement, if you use Stored Procedures to perform Updates/Inserts, would be to have the following code in your those SPs (assumes all values are passed in as parameters and one parameter is @Primary_fg bit):
IF (@Primary_fg = 1)
UPDATE
dbo.Address
SET
Primary_fg = 0
WHERE
Primary_fg = 1
This code would be called before the actual Update/Insert to make sure that no other addresses are set as Primary if the specificed row is to be.
Code could be adapted for Triggers, if triggers are your thing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply