Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following 3 tables:

╔═════════════════╗
║   Companies     ║
╠═════════════════╣
║ id              ║
║ name            ║
║ type            ║
║ etc...          ║
╚═════════════════╝
╔══════════════╗
║   Contacts   ║
╠══════════════╣
║ id           ║
║ company_id   ║
║ first_name   ║
║ last_name    ║
║ etc...       ║
╚══════════════╝
╔═══════════════╗
║   Addresses   ║
╠═══════════════╣
║ id            ║
║ foreign_key   ║
║ address_1     ║
║ address_2     ║
║ city          ║
║ state         ║
║ zip           ║
║ etc...        ║
╚═══════════════╝

My problem is that both Companies and Contacts can have multiple addresses associated with them. Their ID numbers might be overlapping. What would I store in the foreign_key column in the addresses table?

share|improve this question
1  
Normally there is no foreign_key column in a table. Instead, you can add two separate columns with the names company_id and contact_id and store the respective ids in those. –  dezso Dec 10 '12 at 21:06
    
@dezso - I know. The foreign_key column name was just an example to illustrate a point. Regarding your solution: if I then have another table that uses the addresses table, do I then add another column to the addresses table? When does this madness end? –  MegaHit Dec 11 '12 at 17:46
2  
Life is risky... –  dezso Dec 11 '12 at 19:01

1 Answer 1

You have couple options. I'd describe one I like the most, but you can find others searching for 'resolving polymorphic association in database'. Add a common table for companies and contacts (I'd prefer to rename contacts to people or persons), say party. Thus, companies and contacts will have FK to party. Then you add link table party_address(address_id,party_id) with FKs to addresses and party respectively.

UPDATE For instance (I know it's oversimplified, normally you may have different address_type, address change history, etc, but I guess it illustrates idea).

Note: enum is used because mysql still doesn't have check constraints. Unique constraint on (party_id, party_type) added so child tables can have a foreign key references to it; thus, optional relationship implemented and enforced on database level- Party can be either Person or Organization; no way it can be Person and Organization at the same time.

  CREATE TABLE PARTY (party_id int not null auto_increment primary key,
party_type enum('person','organization') not null,
CONSTRAINT UQ_PARTY UNIQUE(party_id,party_type));

CREATE TABLE PERSON (party_id int not null primary key, 
party_type enum('person') NOT NULL DEFAULT 'person',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type) 
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ORGANIZATION (party_id int not null primary key, 
party_type enum('organization') NOT NULL DEFAULT 'organization',
....
CONSTRAINT FK_PERSON_PARTY FOREIGN KEY(party_id,party_type) 
 REFERENCES PARTY(party_id,party_type));

CREATE TABLE ADDRESS(address_id INT NOT NULL auto_increment PRIMARY KEY,
.... );

CREATE TABLE PARTY_ADDRESS (party_id INT NOT NULL, address_id INT NOT NULL,
CONSTRAINT PK_PARTY_ADDRESS PRIMARY KEY(party_id,address_id),
CONSTRAINT FK_PARTY_ADDRESS_PARTY FOREIGN KEY (party_id) REFERENCES PARTY(party_id),
CONSTRAINT FK_PARTY_ADDRESS_ADDRESS FOREIGN KEY (address_id) REFERENCES ADDRESS(address_id));
share|improve this answer
2  
You have the makings of a good answer here. Would you be willing to script/diagram it out a bit more (using the example structure) for the OP since they seem to not have a conceptual understanding of relational data? –  swasheck Dec 11 '12 at 19:01
1  
Example added, I hope it illustrates the idea of common parent approach. –  a1ex07 Dec 11 '12 at 19:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.