Relation in MySQL to other table or use type and tableId

Question

I want to design a software and I have a question about my relations.

Consider I have a table named 'orderItem'. I want to save relation to my products, but I have several categories of product, each in a distinct table (e.g.: Tours, Tickets, Hotels).

My question is: how to implement this?

Using 'productId' and 'type' to find which table used or for each table create new field and create foreign keys and just save current product relation in one of them and let others null?

Thank you?

Why do you have a different table for each product ? By product, you mean a SKU, or a specific category ? If it is SKU, then you would be having 1000s of tables. If category, you will still have 100s of tables. If this is not the case, then we cant make much out of your question language ? — Madhur Bhaiya, Aug 10 '18 at 10:56
Because my products are completely different and limited to maximum 5 product. — Aminkt, Aug 10 '18 at 11:00
Consider a travel agency web site. my products is Tours, Tickets, Hotels and ... — Aminkt, Aug 10 '18 at 11:01
@amin I have tried to clarify your question, feel free to disagree with my edits. — RandomSeed, Aug 10 '18 at 14:32

score 1 · Answer 1 · answered Aug 10 '18 at 14:44

1

You are trying to implement the class table inheritance pattern (go to the third paragraph in the linked answer). Your model needs an abstract "Product" table, which will be involved in the (n, n) relation to the Order table.

UML representation:

[Order] <>--- [Product]
                  ^
                  |
            +-----+-----+
            |     |     |
         [Tour]   |    ...
              [Ticket]

ERD:


[Order]-(1,n)-[Order_has_Product]-(n,1)-[Product]--(1,1)-[Tour]
                                                 \-(1,1)-[Ticket]
                                                 \-(1,1)-...

answered Aug 10 '18 at 14:44

RandomSeed

29,301
6
52
87

To the OP, be sure to read the comments to the question for the potential pitfalls of this modeling pattern. To @RandomSeed, thanks I did not know that method! – Nic3500 Aug 10 '18 at 16:42
Thank you. your answer is that i want. – Aminkt Aug 10 '18 at 16:58

score 0 · Answer 2 · answered Aug 10 '18 at 11:32

So what you propose is something like this? With a fixed number of products?

I think you should instead go with this:

My reasoning behind it is:

Adding new products will be more difficult later.
I know you have a fixed number of products for now, but things change.
I feel what you gain in "simplicity" now, will cost you later.
I put "simplicity" because your queries will be a bit funky!
The OrderItem table for me is 1 line in an invoice. So you have your Order (== invoice), and OrderItem (one line of the Order).
My model also allows you to track how many of each product you have in an OrderItem. So if you have a family going on a tour, you do not have to create 4 OrderItem, one for each person. You just put Quantity == 4.
In order to keep the quantity of each product in your model, you would have to put 5 extra fields in the OrderItem table, to store the quantity of each product type. It is not clean, and not a normalized model. Also you could have ex. Tours_idTours == null and Quantity_Tours_idTours == 0 which would be pointless. It could cause integrity issues.
In my model, the Products table can be huge, depending on the number of fields you need to create. For common fields, that is not really a problem (ex. ProductName). For fields that are specific for 1 type only, you could prefix them. Ex. Tour_Guide. Guide does not apply to Hotels, so prefix it.
Maintenance wise it is easier to add a column and add entire tables.

What should i do if my product details are different? My product table may contain some fields that not necessary in all case but waste memory. — Aminkt, Aug 10 '18 at 11:41
As I stated in my second to last point, you can have as many fields in each row as you need. An empty field does not use any memory. — Nic3500, Aug 10 '18 at 11:44
Thank you so much. I want to know complicated solution if its ok — Aminkt, Aug 10 '18 at 11:48
My mean is that i prefer to have no un necessary col in my product table. I thin it is good to use on product table but i want have new tables for my each product to save details in them. so my object oriented application can have more better Models. — Aminkt, Aug 10 '18 at 11:50
I will discuss this with my data modeling guy, but I think both solutions have merit, it is a matter of compromise. Others might suggest other solutions. — Nic3500, Aug 10 '18 at 11:51

Relation in MySQL to other table or use type and tableId

2 Answers2