I have three tables (MySQL):
families where I define the products' familiesproducts where I define the productsfamilies_products where I relate families and products------------------- -------------------- ------------------------
| familyID | code | | productID | code | | familyID | productID |
|----------|------| |-----------|------| |----------|-----------|
| 1 | p | | 1 | p3 | | 1 | 1 |
| 2 | a | | 2 | a5 | | 1 | 3 |
| 3 | e | | 3 | p1 | | 1 | 6 |
------------------- | 4 | e7 | | 2 | 2 |
| 5 | a2 | | 2 | 5 |
| 6 | p4 | | 3 | 4 |
-------------------- ------------------------
I have two questions:
families_products table putting the familyID relation directly into the table products?familyID how can I retrieve the products->code? I wrote this query but a query structure like this one would work if I drop the families_products table putting the familyID relation directly into the table products as said before, not in the case of a third relational table.'SELECT productID, code, img
FROM products AS a
INNER JOIN families_products AS b
ON b.productID=a.productID
WHERE b.familyID=' . $families[$key]["familyID"]
Your data structure is fine.
If you have at most one familyID per product, then you should put the family in the products table. You should use the junction table (your structure) if a product can be part of multiple families.
As for your query, it is fine. I would use better table aliases:
SELECT p.productID, p.code, ??.img
FROM products p INNER JOIN
families_products fp
ON f.productID = fp.productID
WHERE fp.familyID = ' . $families[$key]["familyID"]
If your product only belongs to one product family you can skip the "familiy_product" table and add the familiy_id directly to the product. Only in case a product can be assigned to multipl families you need the "join" table.
With only two tables the SQL is quite easy: select p.code from family f, product p where f.code ='xx' and p.family_id = f.id