MySQL database table setup

Question

I want to know if i'm setting up this database schema the most effective way. I am trying to output snowboarding terminology and what it means for each product on my website. The tables that I have set up for this:

products

+----+--------+--------+-------------+
| id | brand  | name   | product_type|
+----+--------+--------+-------------+
|8000| burton | diode  | binding     |
+----+--------+--------+-------------+

terminology_products

+----+--------+------------+
| id | att_id | product_id |
+----+--------+------------+
| 1  | 51     |  8000      |
| 2  | 52     |  8000      |
+----+--------+------------+

terminology

+--------+-----------+--------+---------------------+
| att_id |    type   | key    | value               |
+--------+-----------+--------+---------------------+
|   52   | baseplate | EST    | details about EST   |
|   53   | baseplate | Hinge  | details about Hinge |
+--------+-----------+--------+---------------------+

Then I query

SELECT products.ProductName, products.Brand, terminology.type, terminology.key, terminology.value
FROM products
JOIN terminology_products
ON terminology_products.product_id = products.ID
JOIN terminology
ON terminology_products.att_id = terminology.att_id
WHERE products.id = 8000

And get the results that I am looking for, but I feel like this could be an over complicated way of doing it. Is there a simpler way? Thank you for any insight or help.

Answer 1

Yes, it is a correct way. It's common solution.

Your "terminology_products" table contains foreign keys, and it allows you to make "many to many" relation between tables.