mysql多个外键关联,MySQL多个外键

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:

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.

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

解决方案

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