CODEWITHSUNDEEP
mysqldatabasedatabase-design
Manual5355
Manual5355

Reputation: 982

Foreign Key to One of Many Tables (MySQL)

I am designing a website for a small business (landscaping, tree removal, etc) owned by a family member. It's kind of a pet project.

The website will allow users to create an account and then request services, and will create the necessary invoices, but it will also allow my family member to enter invoices for orders not received through the website.

I am working on the database (MySQL), and this is currently what I have. Anything containing "User" is referring to online customers, and anything with Cust refers to non-online customers.

Basically what I am trying to do here is this:

An Invoice has a customer, business, or user (online-customer). An invoice also one or more InvoiceLines, and each InvoiceLine has a Service or Product.

I put users (online-customers) in separate table because it makes sense for a user to have a passwordhash and password salt (and possibly other things), but a customer added solely for records keeping shouldn't have a password and things related to the website.

   ServicesAndProducts 
--------------------------
| ID INT (PK)            | 
| Name VARCHAR(50)       |  
| BasePrice DECIMAL(7,2) |
| ...                    |
--------------------------

   CustServicesProvided               UserServicesProvided
---------------------------       ---------------------------
| ID INT (PK)             |       | ID INT (PK)             |
| ServiceID INT (FK)      |       | ServiceID INT (FK)      |
| CustInvoiceID INT (FK)  |       | UserInvoiceID INT (FK)  |
| ...                     |       | ...                     |
---------------------------       ---------------------------

       CustInvoices                     UserInvoices
--------------------------        --------------------------
| ID INT (PK)            |        | ID INT (PK)            |
| CustomerID INT (FK)    |        | UserId INT (FK)        |
| ....                   |        | ....                   |
--------------------------        --------------------------

        Customers                           Users
--------------------------        ---------------------------
| ID INT (PK)            |        | ID INT (PK)             |
| Name VARCHAR(50))      |        | Name VARCHAR(50)        |
| BasePrice DECIMAL(7,2) |        | PasswordHash VARCHAR(50)|
| ...                    |        | PasswordSalt VARCHAR(50)|
--------------------------        | ...                     |
                                  ---------------------------

I also have another similar BusinessServicesProvided, BusinessInvoices, and Business because I want the foreign keys, and Businesses would have different fields than the other two.

My question is there a way that I can combine the Invoice Tables together, and combine the Services Provided Tables together, while still maintaining the three kinds of the foreign keys between the Invoice Table and Customers and Users (and Businesses)?

I thought of possibly adding a InvoiceType table, and then having a TypeID (FK) in The Invoice Table to indicate, but I don't know how to maintain a foreign key between the Entity (Customer, Business, or User) and the Invoice. e.g:

   ServicesAndProducts 
--------------------------
| ID INT (PK)            | 
| Name VARCHAR(50)       |  
| BasePrice DECIMAL(7,2) |
| ...                    |
--------------------------

      ServicesProvided    
---------------------------
| ID INT (PK)             |
| ServiceID INT (FK)      |
| CustInvoiceID INT (FK)  |
| ...                     |
---------------------------

       Invoices                            InvoiceType
--------------------------        ---------------------------
| ID INT (PK)            |        | ID INT (PK)             |
| ForeignID INT (FK)     |        | Name VARCHAR(50)        |
| Invoice Type INT (FK)  |        |                         |
| ....                   |        |                         |
--------------------------        ---------------------------

        Customers                           Users
--------------------------        ---------------------------
| ID INT (PK)            |        | ID INT (PK)             |
| Name VARCHAR(50))      |        | Name VARCHAR(50)        |
| BasePrice DECIMAL(7,2) |        | PasswordHash VARCHAR(50)|
| ...                    |        | PasswordSalt VARCHAR(50)|
--------------------------        | ...                     |
                                  ---------------------------

I also considered having a trigger to create a new customer when a user is created, but I would also like for a customer to be able to view the Invoices/Services they have requested in the past.

This is my first time designing a database so any ideas are appreciated.

Upvotes: 1

Views: 269

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50970

No, you cannot point a foreign key at primary keys in different tables, telling the database to find a matching record in one of the tables (furthermore, presumably the range of primary key values in the different tables could overlap, and then you might match two or more of the primary key tables).

Your mistake is to create a whole new table to account for the differences between, for example, customers and users. Instead, create a single table with the common columns (probably called customer) and then separate tables with foreign keys back to the main table. Each table holds the columns for one of the sub-types of customers (on-line or flesh-and-blood).

Same for the different kinds of invoices. One table holds common invoice information (most especially the primary key) and one or more auxiliary tables holds the information particular to a kind of invoice.

This design is similar to class inheritance in object oriented programming.

Upvotes: 1

Related Questions