Storage of an object with parameters with values ​​in the database and their selection

Question

We are developing a site for publishing ads similar to Craigslist. There we have an advertisement, a category and its parameters. Options are tied to categories. And the parameters matter. And these values ​​depend on other values. For example, when I select the “BMW” option, BMW models such as “X6, X5, M3,” etc. should appear.

We tried to solve it in this way.

We have tables

Params(id, category_id, parent_id, name) ParamValues(id, param_id, is_string, int_value, string_value) Ads(id,category_id) and Ads_paramvalues_join(ad_id, param_value_id). 

We added string_value and int_value, because when the user performs a search, he can select an int range or a constant value from the list.

The problem is that the join table(Ads_paramvalues_join) will become very large, because it will contain a mapping from each parameter value. This seems to be causing performance issues.

Here we use Hibernate 3, Spring 3 MVC.

We would like to know if there are any other best practices and patterns to solve such a problem in such cases.

Answer 1

You must first separate the problem when starting the relational database, as I expect you to do this:

Basically, you have a separate object of real life with their parameters: Car: Engine, color, seats, doors, etc. Bike: frame size, wheel size, color, lighting, etc.

They are simply different, on the basis of normalization it would be strange to group them all into one table. A car is just not a bike.

Now you have a problem that you see them as a kind of common object: Advertising.

That where the problems arise is actually: you create the database in the database. And this is not a well-known good practice.

To make sense, some options:

• Just give the object your own time sheet, so there is a car table, a bicycle table, etc.

• Add all the properties of your objects to one table (with a zero value). So: object_type (bike / car), engine, color, seats, doors, frame, wheel size, lighting, etc. Etc.

• Think of some many-to-many constructs, such as tags that are just like you are now.

• Think of a different way to store your data, RDBMS are correct many times, but for this type of flexible storage, such as key storage databases, it may be more appropriate.