-1

I have the following tables

tb_orders: 
      > id - PK
      > name
      ...
      > product_id - FK

tb_product: 
      > id - PK
      > components
      > color 
      ...

In one order, I can have more than one product, but the foreign key column has only one reference to tb_product

I can use two approach for this:

  1. create an associative table, where would exists a row for each product ordered
tb_orders: 
      > id - PK
      > name
      ...
      > order - FK

tb_order_products: 
      > id - PK 
      > product_id - FK
      > order_id - FK

tb_product: 
      > id - PK
      > components
      > color 
      ...
  1. I can create a column products_id, in a list/array format, containing all the ordered products ids in one row: '[1, 5, 7]'. Before insert, I can check manually if the product exist in db.

I think M:M is good in the majority scenarios, but I don't see problem make this... I never see this to be honest, but seems simpler than a M:M relationship. By the way, check if the row exist in db before insert doesn't see a big problem, I don't have many products.

So, what you think about using the second approach?

vitxr
  • 30
  • 1
  • 7

1 Answers1

0

Both methods are good... but ultimately it depends. If you want a single order, including a product that is selected multiple times, an m:m associative table would be a better solution. Eg product is "T-shirt" but sizes are different.

katakrowa
  • 162
  • 6
  • oo, don't think about it. I don't need this now, so it's good use m:m because maybe in future I need scaling – vitxr Jan 10 '23 at 17:23
  • In that case, first option is good enough. – katakrowa Jan 10 '23 at 17:28
  • The 2nd method is at odds with relational concepts, so it is not good at all for this purpose. – Shadow Jan 10 '23 at 17:30
  • yep. I think is enough, but in maintenance pespective the second seems better too – vitxr Jan 10 '23 at 17:32
  • @vitxr if you want to use the 2nd option then I suggest discarding mysql or any other relational database. What's the point of using a relational database if you go against the relational concepts even if there is sound relational solution out these? See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad fir more detailed discussion – Shadow Jan 10 '23 at 18:40
  • I can't use another db. And this is a specific case, where avoid relationships keep the tables simpler, I think. And mysql have engines that doesn't supports foreign keys by the way. Actually only 'InnoDB' engine support it. But probably get a no relational db is better – vitxr Jan 10 '23 at 19:24
  • btw²: thanks for the links. I'll read it – vitxr Jan 10 '23 at 19:25