0

I haven’t created such structures before and decided to ask the experts. Is this a good structure? I am concerned about the following questions:

  1. I created the "product_size" table separately, because some products do not have dimensions.
  2. The "product_prefix" table is a boolean value for displaying icons on the product card.
  3. Table "product_gallery", is it correct or worth transferring these values ​​to the table "products"?
  4. Table "product_characteristics", I haven’t come up with anything else how to store an array with json data of the form [{"key": "value"}]

enter image description here

Improve this question
2
  • no, unlike images i doubt thta width and height changes for a produkt. so it belongs to the proiduct. you must diffrentiate between attributes of the proiduct that exits only ones, or like images where you can have many product prefix looks also wrong. every joins costs time and space, as long as you can't normalize it keep the attribute with the entity
    – nbk
    Apr 14, 2021 at 22:16
  • Can't judge a schema without seeing the SELECTs.
    – Rick James
    Apr 15, 2021 at 0:51

2 Answers 2

Reset to default
0

After examining the type of database relationships (one-to-one, one-to-many, many-to-many) I made such a database structure. If something can be corrected here, correct me.enter image description here

Improve this answer
4
  • I suggest there is "over-normalization" in this case order_status. It is unclear whether a product can be in multiple categories. There is no need for an id on any many-to-many table; simply have the PRIMARY KEY be the pair.
    – Rick James
    Apr 19, 2021 at 17:46
  • @RickJames I wouldn't say it's over-normalization, order_status should be there as a lookup. You are right about id though
    – Charlieface
    Apr 25, 2021 at 10:57
  • Wouldn't an "order" want just one "size" of the product? (I don't see how that is achieved with this schema.)
    – Rick James
    Apr 25, 2021 at 15:29
  • @RickJames you are right here it is probably better to take out the "product_size" to the parent and set the default value to "NULL". Just some products will not contain size.
    – Лукас
    Apr 25, 2021 at 17:06
0

I think your second attempt looks better. For the tables that are optional columns to the product table (product_gallery, product_sizes) I would not add another id column but just use product_id. A good rule of thumb is to move the wider columns to the vertically partitioned secondary table - that way you can store more rows in the same amount of disk space. So you have a description column in your products table and maybe that should be moved to one of the secondary tables (or its own). As someone commented previously a lot depends on which queries you will be running and how oftern they run.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.