Is database structure the right choice for mySQL?

Question

We are currently planning a database structure for a rather sophisticated e-commerce web application, with flexibility as the main cornerstone.

Our application has a large amount of data (products), and we faced a small headache trying to maintain high performance without compromising normalization rules in the database or leaving our concept of flexible flexibility when integrating product parameters (also commonly known as attributes or product parameters )

Based on various links and available sources, we have developed lists of advantages and disadvantages of all the main and well-known database templates to solve this problem. By comparing them, we came to two final alternatives:

• EAV (object attribute value model):

  Pros: The database is used for sorting.

  Cons: All related queries will include multiple joins between multiple tables to complete data collection.

• SLOB (serialized LOB, also known as Facade?):

  Pros: Very flexible. Storage of the number of required connections compared to the EAV design pattern. It is easy to update / add / remove data from each product, but it is difficult to maintain data integrity without additional tables.

  Cons: all sorting will be performed by the application instead of the database. Will greater performance be used (memory?) When large amounts of data are processed by a large number of users.

Our main questions:

• What structure / structure would you use, or perhaps another solution?
• Are there any better databases besides mySQL currently available to achieve what we want?

Thanks a lot!

Link: How to create a product table for many types of products, where each product has many parameters

Answer 1

Why limit yourself to one model? It is very possible that you will be better off with two different models, where each of them will meet a specific goal very well.

Assuming, as is often the case, that they should not be absolutely and instantly synchronized, you can easily get much better overall performance. What are the strict requirements you have when synchronizing? Milliseconds to a minute?

Udi Dahan has some good information about the division of responsibilities for team requests (CQRS) that are relevant. See also a couple of other articles . InfoQ also has a corresponding video by Greg Young from QCon08.

EDIT: Here's another video (Udi Dahan) that discusses, among other things, the benefits of several models.

Answer 2

MySQL works very well even for very large datasets. I use it at SaaS financial services company, and it always worked well. I also use SQL Server and Oracle for very large applications, and MySQL does not work best or worst. However, I focus on the business layer, and you can get more detailed opinions from people close to the database.

When choosing a template, keep in mind that it is much easier to scale the application level than the data level (it is easy and cheap to add application servers). Performing many unions for common operations can cause a performance bottleneck.

I would suggest you a prototype of both approaches so that you can get to know each of them better and compare their performance in your particular environment.

In addition, you might want to look at alternatives to SQL that are trying to create a template similar to the one you draw. A friend in a very large, well-known Internet company begins to use Project Voldemort . He prefers it with similar efforts, mainly due to a very active community.

Answer 3

from your solution, it seems you do not want to use the relational model, so it might be better not to use the relational database, take a look at these alternatives: http://nosql-database.org/ btw SQLServer has nice SLOB functions in the form of xml fields ( query can be indexed through XQuery)