1
I have the following database modeling so far:
I’m wondering how best to store the products in the order table.
One string with Id of all products for each order in the table? I think very bad. What other solution would be better?
Then I want to be able to fetch an order and its related products.
3
You need a table of order items. This is called associative entity. This table would be linked to the request and an entry in it only exists because there is a request. This is normalization basic.
In this table you would have a primary key composed by the order number plus the sequence of the entered item. I could just put the product code, but in a real application it’s never as simple (that’s why I talk that artificial examples never teach you how to do something, making a paper car is very different from making a car to ride the streets). In general you will have several data relating to this order item.
Not all cases you need to search the items individually, so it may not be as advantageous.
On the other hand if it’s just the same code, then I don’t see anything so bad as making a list of ids in the table itself. It’s not so common to do this in the relational model, but I don’t think it’s so bad when it makes sense. For an artificial example that is probably an exercise I would do relationship because the goal should be this. In real case it would not even be appropriate as I said in the previous paragraphs, but if it were, there are some cases that may be better.
The relational model was created at a time when purity was required, and existing mechanisms were limited to deal with variable data. You need to think about the need and purity of the model.
1
When we are working with a relational bank we seek to establish a relationship between tables when it makes sense. As is your case.
In your case, you need to establish a relationship between orders (Orders) and products (products).
An order is made for several products and this makes perfect sense. So your reasoning is correct, but the way to execute it is not.
Starting from your problem, we can establish a few things to get at a final solution suggestion:
To establish this relationship between order and product, it can occur using the already existing properties of relational banks. For this, we have what we call "foreign key". A foreign key makes a link from one table to another, keeping the unique identifier (key) from the other table.
In most cases, we can establish the relationship between 2 tables by placing the foreign key in one of them. The choice will depend on the relationship. For example, if a person may have more than one address, and the address only belongs to the person, we can place a foreign key at the address with the person’s key. Something like that:
pessoa
id | nome
100 | Dherik
102 | Luan
endereco
id | rua | pessoa_id
400 | Rua Chile | 100
401 | Rua Argentina | 102
402 | Rua Peru | 100
However, in your case, it does not seem to make sense for the product to have the order key in its table, as the product may belong to more than one order and an order may be associated with several products.
To solve this, you can create an intermediate table to associate order and product: the table pedido_produto. So you would have this result (I’ll omit some columns for simplification):
produto
id | name
5 | Garrafa
6 | Mouse
7 | Teclado
pedido
id | idClient
2 | 33
9 | 236
pedido_produto
id_pedido | id_produto
2 | 5
9 | 7
9 | 5
9 | 6
In this way, the table pedido_produto can establish which applications are linked to which products.
Browser other questions tagged sql database modeling foreign-key
You are not signed in. Login or sign up in order to post.
As the relationship between order and product is
N - N(a product can be in multiple orders, just as an order can have multiple products), you can create a associative table, where each record contains the product id and the order id, allowing you to perform queries such as "all products for X order" or "all orders that have X product".– Vinicius Gabriel
Did the answer solve your question? Do you think you can accept it? See [tour] if you don’t know how you do it. This would help a lot to indicate that the solution was useful for you. You can also vote on any question or answer you find useful on the entire site (when you have 15 points).
– Maniero