0

Consider I have the following MySQL tables:

products id | created_at | updated_at | name | description | barcode

stores id | created_at | updated_at | name | description | address

product_attributes id | product_id | attribute_name | attribute_value

store_attributes id | store_id | attribute_name | attribute_value

Note that the product_attributes and store_attributes tables are actually the same. The application has the option to link attributes to products and stores in the form of a simple key-value form. The product_attributes and store_attributes tables are used for this.

When a product is loaded, its attributes must be loaded too, and the same applies to stores.

There could also be just one table, say attributes, and then both the product attributes and the store attributes could be put in that attributes table together. Like as follows:

attributes id | entity_id | attribute_name | attribute_value

I chose to keep the attribute data in separate tables as I thought it would have better performance when selecting data. However, I have no evidence that this assumption is correct. When MySQL has to find all attributes for a given product_id, it has to look only in the product_attributes table, and it would never be scanning rows that don't belong to a product at all. Is my design with separate tables (product_attributes and store_attributes) a better choice in terms of selection speed/performance?

Consider the design completely separate from any future adjustments or expansions. It's actually a very simplified example.

1
  • That choice will ultimately be the least of your problems and performance issues. EAV is the main hassle, especially when you search by attribute.
    – Rick James
    Dec 21, 2022 at 23:03

1 Answer 1

0

A table in a relational database isn't just a bucket of bytes into which any old value can be dumped. It is intended as the implementation of an entity type, which is a description of a class of things.

If your users consider store_attributes & product_attributes to be synonyms for the same class of things - they have the same provenance, life cycle, usage etc. - go ahead and combine them. If not keep them separate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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