DB Design 2 FK's to same Primary Key Table

[spangeman]

Database design question for you all

I want to save on data storage by having an address table which stores the address of many different tables in the database. This works fine in the following example:-


Table - School
SchoolID
cola
colb
AddrID (FK to Address.AddrID)


Table - Building
BuildingID
cola
colb
AddrID (FK to Address.AddrID)


table - Address
AddrID (FK to Address.AddrID)
Addr1
Addr2
Addr3
Addr4



But if I try and have more then one address type in one of the tables SQL server gives me an error.
May cause cycles or multiple cascade paths.


Table - Person
PersonID
cola
colb
MyAddrID (FK to Address.AddrID)
MyOtherAddrID (FK to Address.AddrID)


Why is this?
And do I need a different design method.

Regards
Spangeman

 

[gmmastros]

I would suggest that you add another table to handle the case where you want multiple addresses for a person. This will allow you to have more than 2 addresses, also.

Person
------
PersonId
ColA
ColB

Address
-------
AddressId
Addr1
Addr2
Addr3
Addr4

New Table
PersonAddress
-------------
PersonId
AddressId
AddressDescription

The data in this table may look like....

PersonId  AddressId  AddressDescription
1         1          Home
1         2          Mail
1         3          Work
2         4          Home
2         3          Work

Looking at the sample data, you'll notice that person1 and person2 both use Address3 for work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Two relationships between two tables (Person, Address) form enclosed graph. If you have ON UPDATE|DELETE CASCADE enforced there, server won't let you do that.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[fredericofonseca]

A new design is in order.

Also inn many situations it is better not to have the CASCADE active. (I don't use it for example. If I need to update/delete anything I will do that with code)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com