MySQL multiple foreign keys

问题:

I'm new to setting up relational databases.

I'm trying to create two tables in MySQL, a USER table and a COMPANY table. Both should be able to have multiple phone numbers associated with them, so I created a PHONE table and wanted to have a one-to-many relationship from both COMPANY to PHONE, and from USER to PHONE.

There only seem to two options in what I am trying to do:

1. Keep two foreign keys in PHONE, one referencing COMPANY, and one referencing USER. They would both default to NULL and each and when creating a new row in PHONE, I would only fill the one that I need.

2. Have two different tables, USER_PHONE and COMPANY_PHONE.

Neither option seems optimal to me. Option 1 seems hacky and prone to redundancy issues. Option two seems pretty repetitive and unnecessary. I'm inclined to think that option 2 is the "official" way of doing things (starting to wonder if this is why I hear negative things about MySQL).

Anyone? Thanks,

-Matt

回答1:

I do like to suggest the following design(much like yours):

First of all, we will have three tables

USER -- UserId, other fields
COMPANY -- CompanyId, other fields
PHONE -- PhoneId, PhoneNumber

Then have two tables for storing the relation

COMPANY_PHONE -- CompanyId, PhoneId
USER_PHONE -- UserId, PhoneId

回答2:

Having two different tables for phone numbers raises some problems.

• You have to look in more than one place for a phone number.
• If a person and a company share a phone number, and the phone number changes, you'll probably forget to update it in all the tables.
• It violates the Principle of Orthogonal Design, which loosely means "Don't have tables whose meanings overlap".

Instead, use a supertype/subtype schema. (Linked answer includes example SQL to implement the schema.) This kind of schema recognizes that users and companies aren't exactly alike, but they're not utterly different.