mySQL - foreign key to multiple tables?

Question

I am fumbling my way through phpMyAdmin and mySQL. I’m creating something where a customer can register multiple products. The data coming in is:

Category > Type > Size > Color

So for example:

Cookware > Oven > 5 qt > Blue
Bakeware > Casserole > 3qt > Blue
Accessories > Textiles > N/A > Blue
Etc.

I have set up one table with categories, and 4 tables to cover each product type.

Categories
ID Category
1 Cookware
2 Bakeware
3 Accessories
4 Serveware

Cookware Table
ID Type
1 Oven
2 Skillet
3 Roaster

Bakeware Table
ID Type
1 Casserole
2 Pie Dish
3 Baker

Etc.

Then, in the registration table, I set up a foreign key to link the category to the category table. So it would look something like this:

ID   CustID  Category  Type Size Color
1    20      2         1    11   34
1    20      1         1    9    34

(sorry the formatting is so terrible! not sure how to fix) But, I’m stuck on how to link the product type to the correct product type table since it is dependent on which category they picked. Hopefully this makes sense. Maybe I don't even need to link them and can still request the data somehow through a query?

score 3 · Accepted Answer · answered Jun 19 '14 at 18:55

3

Merge the last 2 tables ?

    ID Type        Category
    1  Oven        1
    2  Skillet     1
    3  Roaster     1
    4  Casserole   2
    5  Pie Dish    2
    6  Baker       2

answered Jun 19 '14 at 18:55

Xyaren

955
10
19

I thought about that but it would require reprogramming the form and making that piece more complex. Perhaps that will be easier than dealing with it on the database side though. – surfbird0713 Jun 19 '14 at 18:58
You can reference just one table. Dirty solution: dont use foreign keys and handle within the application... Off-Topic: Use Code-Tag for formatting :) – Xyaren Jun 19 '14 at 19:01
Thanks for the code-tag formatting tip! Sorry, what do you mean by reference just one table? Are you still suggesting I merge all the types together, or in the query to get the data, I can reference the appropriate type table based on what category was selected? – surfbird0713 Jun 19 '14 at 19:04
I decided to merge the tables to keep things easy. – surfbird0713 Jun 20 '14 at 14:22

mySQL - foreign key to multiple tables?

1 Answers1