A
A
Andrey Bilyk2014-04-21 21:54:07
SQL
Andrey Bilyk, 2014-04-21 21:54:07

How to write an ERD correctly?

Good afternoon. Tell me how best to structure the product and its decorators.
Decorators here mean, for example:
1) Wrapping paper (FK on the corresponding reference book)
2) Packing tape (just as a service, available or not)
3) Product engraving - applying text (several texts) to the product
and others.
On the one hand, all decorators are listed in the directory, have their own key and their cost:
Decorators

id	name	price
1	Бумага	10
2	Лента	5
3	Текст	2

This suggests a summary plate between a product and several of its
ProductDecorators
id product_id decorator_id decorator_price
And this structure would work great if it weren't for the different types of decorators.
So, for paper, you need to specify a foreign key to the directory, which is optional for all other decorators.
For texts, additional parameters must be specified (the text itself, position on the product) + there can be several texts for one product..
What is the best way to proceed in this case?
You can, for example, move decorators like "Wrapping paper", "Text" into separate entities with FK on the product, and leave decorators of the same type and "linear" in ProductDecorators.
However, in this case, we get a dependency - some of the decorators are separated into one table (and must be processed at the code level), some obey the general logic ... which is not good at all.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
robofox, 2014-06-30
@robofox

Create tables with decorator options. For example:

decor_text_params:
id   product_id   text_value   text_position   text_param1   text_param2

decor_paper_params:
id product_id paper_type paper_size paper_param1 paper_param2

They will also be linked to the product. Yes, processing at the code level will be added.
If you want to do without binding to the code, you can have decorator parameter tables.
decor_params_definition:
id   decor_id   param_name  param_type  ...

Заполнение:
1   1   "Цвет бумаги"  "color"
2   1   "Размер бумаги"   "int"

In another table, store parameter values ​​as strings, convert them to the required types at the code level.
This structure is useful if the parameters will be constantly added or changed, and they are only needed for display purposes. If you have to work with parameters in code, it is better to hard-bind them.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question