三个表之间的关系：正确的查询和设计建议

Question

I have three tables (MySQL):

1. families where I define the products' families
2. products where I define the products
3. families_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:

1. Is this design convenient or is it better drop the families_products table putting the familyID relation directly into the table products?
2. With a design like this one, if I have the 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"]

Answer 1

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"]