0

I have following abstracted model in my application.

Entity "Product"
Id | Name | Attribute 1 | Attribute 2 | ... | Attribute N = 50

Attributes are fixed by a business model and can only have specific, predefined characteristics, i.e.:

Attribute 1
1 | Blue
2 | Red
3 | Yellow

Attribute 2 
1 | S
2 | M
3 | L

Referencing these attributes by a one-to-many or many-to-many would make sense, if I would only have a hand full of so defined attributes... However, we have a lot of these attributes, lets say N=50 predefined, specific attributes.

That would mean a query must perform N=50 joins to receive the entire product from database. That does not seem like a well-performing, well-scaling approach, especially when increasing the amount of products (several tens of thousands).

In a NoSQL database like MongoDB I would design it probably like as including the references directly in the same document:

{
    "Id":"1"
    "name":"somename"
    "attributes": {
        "attribute1":{"1":"red"}
        "attribute2":{"1":"S"}
        ...
        "attribute50":{"1":"something"}
    }
}

But for SQL and RDBMS im kind of stuck or can't find a proper solution.

Philip
  • 112
  • 14
  • 1
    50 columns? I do not see a problem... – Akina May 27 '22 at 11:19
  • @Akina Thanks for commenting. That would lead to 50 joins per query, when asking for the entire product. Isn't that too many joins to be handled efficiently and quickly? – Philip May 27 '22 at 11:21
  • *That would lead to 50 joins per query* ?? Do you mean that these 50 columns are not values but foreign keys? If so then think about EAV pattern or single JSON column. Or maybe you need in Faceted Search? – Akina May 27 '22 at 11:24
  • Yes, it would be foreign keys, like fk=1 for Attribute 1 = Blue. Another approach would be, instead of putting in the foreign key, putting in the actual value? In this case a string. Or should I defined a custom data type in database? – Philip May 27 '22 at 11:31
  • If the lists are relatively stable, then you could use enums instead of lookup tables. – Shadow May 27 '22 at 11:55
  • Alternatively, look into one of the dynamic properties design patterns as Akina has already suggested, see https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m – Shadow May 27 '22 at 11:58

0 Answers0