Geek Answers Handbook

Should I use an EAV database design model or multiple tables

I started a new application, and now I am considering two ways and do not know which one is a good way.
I am creating something like an eCommerce site. I have categories and subcategories .
The problem is that there are different types of products on the site, each of which has different properties . And the site should be filtered by these product properties.
This is my initial database design:

Products{ProductId, Name, ProductCategoryId} ProductCategories{ProductCategoryId, Name, ParentId} CategoryProperties{CategoryPropertyId, ProductCategoryId, Name} ProductPropertyValues{ProductId, CategoryPropertyId, Value}

Now, after some analysis, I see that this project is really EAV , and I read that people usually do not recommend this design.
Everyone seems to need dynamic SQL queries.

This is one way, and I'm looking at it right now.

The other way that I see is probably called LOT WORK WAY, but if it's better, I want to go there. To make a table

 Product{ProductId, CategoryId, Name, ManufacturerId}

and do table inheritance in the database, which means creating tables like

 Cpus{ProductId ....} HardDisks{ProductId ....} MotherBoards{ProductId ....} erc. for each product (1 to 1 relation).

I understand that it will be a very large database and a very large application domain, but it is better, simpler and works better than the EAV design option.

+4
source share
3 answers

EAV rarely wins. In your case, I see the attractiveness of EAV, given that different categories will have different attributes, and otherwise it will be difficult. However, suppose someone wants to find "all hard drives with more than 3 plates using a SATA interface that rotates at 10,000 rpm"? Your request to EAV will be painful. If you want to support such a request, there is no EAV.

There are other approaches. You can consider an XML field with extended data or, if you are on PostgreSQL 9.2, a JSON field (XML is easier to search, though). This will give you a significantly wider range of possible searches without EAV headaches. The trade-off will be that applying the circuit will be more difficult.

+5
source

This question seems to discuss the issue in more detail.

In addition to the performance, extensibility, and complexity discussed there, also consider:

  • SQL databases, such as SQL Server, have full-text search capabilities; therefore, if you have one field describing the product, a full-text search will index it and be able to provide advanced semantic searches.

  • take a look at systems without sql, which are rage now; scalability should be good with them, and they provide support for unstructured data, such as the one you have. Hadoop and Casandra are good starting points.

+4
source

You can work very well with the EAV model. We do something similar with a logistics application. It is built on .net, though. In addition to tables, your application code must correctly process objects. See if you can add a common table for each object. It works for us.

0
source

More articles:


All Articles