You are in a typical n,m relation.
In this case, your problem of included products in sales is the same problem of students enrolled in disciplines:
Inthiscase,theaboveschemaisintheEntity-RelationshipModel,orMER.Thismodelisveryusefulforrapidmodelingofthedata,andalsoforadeepunderstandingofthebusinessinvolved.However,noteverythingisperfect.SQLisnotdirectlycompatiblewithMER,sinceSQLfollowstheRelationalModel,MR,basedonrelationalalgebra.
TotransformfromMERtoMR,itisveryimportanttoknowtherelationship'ssake.Inyourcase,theinterestingpartisinthemany-to-manyrelationship.
MoreaboutturningMERintoMR on this answer
In a many-to-many relationship, you have m entities of one type relating to n entities of the other type. In this case, you create an intermediate table with foreign keys for the two entity tables. Example:
I sold a drill and two saws.
My entity table produto :
produto
| prodid | prodnome |
+--------+-----------+
| 1 | serrote |
| 2 | furadeira |
My entity table pedido :
pedido
| pedid |
+-------+
| 1984 |
My table for the relationship:
item
| itemid | prodid | pedid | quant | valor |
+----------+--------+-------+-------+--------+
| 1783 | 1 | 1984 | 2.0 | 10.00 |
| 2123 | 2 | 1984 | 1.0 | 150.00 |
To find out what the products (and their quantities) are in the 1984 order:
SELECT
prod.prodnome,
i.quant
FROM
pedido ped
inner join item i on (ped.pedid = i.pedid)
inner join produto prod on (i.prodid = prod.prodid)
In my case, I'm modeling according to the needs I've already had in the company where I work. There are cases where you want to sell the same item two or more times in the same order (for example, as a bonus item, exchange, and actual sale), so I set your primary key to itemid . In most cases, however, a n,m relation usually does not have the entities' repetition.
