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?

Answer: 
You are trying to implement the [class table inheritance pattern](https://stackoverflow.com/a/3579462/1446005) (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)-...

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

[![enter image description here](https://i.stack.imgur.com/cVvVq.png)](https://i.stack.imgur.com/cVvVq.png)

---

I think you should instead go with this:

[![enter image description here](https://i.stack.imgur.com/PT6Ig.png)](https://i.stack.imgur.com/PT6Ig.png)

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.