In the system, it is necessary to implement 2 types of goods, a линейка товаров (название линейки, а к ней модификации товаров из этой линейки относятся) and just обычный товар . I don’t want to do 2 separate tables for this, but simply to add the parent_id field, this is somehow not right, as when adding a product, and choosing a product line (parent), you can get ordinary products whose parent_id also 0. As for the other can this be done?

UPD: Let me give you an example: Suppose there is an HP ProBook 430 G2 product, it is a product line and it has an HP ProBook 430 G2 (P5T34ES) modification HP ProBook 430 G2 (P5T34ES) , it turns out that the HP ProBook 430 G2 parent_id wakes up parent_id is 0, and the HP ProBook 430 G2 (P5T34ES) modification HP ProBook 430 G2 (P5T34ES) , parent_id wakes equal to the id of the product line. Now imagine that there are ordinary products (which have no modification), they also wake up parent_id = 0. And now, when adding a product, when we select the product line (parent), then the list will not only HP ProBook 430 G2 , but and ordinary goods without modification

  • это как то не правильно Why not? - Anatol
  • Added an example of why this is not correct - Alex_01
  • You decide that for what we choose - product lines or specific products? In general, it seems to me, there are two options. 1) Do not be lazy and make your table for the rulers. 2) Distinguish rulers from goods by a special field, for example data_type = 'TOVAR' / 'LINEIKA'. And of course you can always make a line consisting of only one product. - Sergey
  • data_type is probably the most necessary. If it is not clear from the example, look here market.yandex.ru/product/10952997?hid=91013 and here market.yandex.ru/product/13334303 . The first link is a line of products, and the second is a modification, that is, an ordinary product that belongs to the first. In fact, these are 2 products, only one has a modification - Alex_01
  • I am sure that each concept in them has its own model and its own table for storing its state. - Sergey

1 answer 1

If this is the only problem, then the parent_id field must accept NULL values. Then the product line parent_id = 0 , for ordinary products parent_id = NULL .

  • If the foraign key is set, then parent_id can be zero only if there is an entry with id = 0. Those. as well as for all values ​​other than null - Sergey
  • one
    @ Alex_01 Using 0 for such things is not really correct. For 0 record must exist and it will be some kind of "superline". The field of the record type "ruler / not ruler" (and perhaps in the future some more types will emerge) looks better - Mike
  • one
    @Anatol I think I understood the task. But there can be no records with parent_id = 0 in the system if there is no record with id = 0. And what does it mean to write with id = 0, except as a kind of software stub in order to put entries with parent_id = 0 I can’t imagine - Mike
  • one
    @Anatol Absence in the FOREIGN KEY table potentially threatens to make entries in the system with pointers to nowhere. Control for the presence of generated records, before removing the parent will have to do hands. Working with systems without automatic referential integrity usually costs 3-4 times more expensive in terms of development and maintenance time - Mike
  • 2
    @ Alex_01 note the recommendations and comments of Mike. From the point of view of canonical design, he is right. I would, for example, nevertheless smash it up according to different tables and add more categories. But you know better, tk. the subject area is all we do not know. - Anatol