3

I use SQL Server and Entity Framework as ORM.

Currently I have a table Product which contains all products of any kind. The different kinds of products possess different attributes.

For example:

  • All products of kind TV have attributes title, resolution and contrast
  • Where as all products of kind Car have attributes like model and horsepower

Based on this scenario I created a table called Attribute which contains all the attributes of a product.

Now to fetch a product from database I always have to join all the attributes.

To insert a product I have to insert all the attributes one by one as single rows.

The application is not just a shop or anything like it. It should be possible to add/remove an attribute to/from a kind of product on the fly without changing the db.

But my questions to you is still:

  • Is this a bad design?
  • Is there another way of doing it?
  • Will my solution slow down significant? (f.e. an insert takes several seconds assumed the product has hundreds of attributes...)

Update

The problem is that my application is really complex. There are a lot of huge algorithms. The software is used for statistical purposes.

One problem for example is the following one: In an algorithm-table I'm storing which attributes are used for filters. Say an administrator wants to filter all cars that have less than 100 horsepowers. The filters are dynamical, what means that I have a filter table which stores the filter type (lessThan) and the attribute (horsepowers). How can I keep this flexibility with the suggested approaches (with "hardcoded" columns)?

Improve this question
6
  • 5
    Yes, this is a really bad design - it's known as Entity Attribute Value and it's considered an anti-pattern - try to avoid it! See Five Simple Database Design Errors You Should Avoid (point #3) for a discussion of why EAV is really really bad, and see Bad CaRMa for a true horror story of an EAV system design that ruined a thriving company. Commented Nov 14, 2013 at 9:19
  • Thanks for your comment, but sometimes we have to use antipatterns when there seems to be no other solution... Or am I wrong? Is there another way? Commented Nov 14, 2013 at 9:33
  • 1
    There are lots of alternatives: (1) define a BaseProduct table with the common attributes, and then Cars and TV tables that reference the base products table, and add the additional attributes; or if that's still too rigid: (2) put the base attributes on the table, and store any additional custom attributes into an XML column - those are just the two alternatives that I've personally used with great success - I'm sure there are plenty more! Commented Nov 14, 2013 at 9:42
  • Thank you marc_s. Really appreciate your input! The thing is that I use complex sql calculations for all kinds of products. There are a lot of joins which I would have to make "dynamic" (one time it would have to join to "Cars" one time it would have to join to "TV"). Or I would have to write each piece of SQL several times for each kind of product... Can you please set me thinking :)? Commented Nov 14, 2013 at 9:52
  • Well - you're saying you're using Entity Framework - right? EF can use parent-child tables as a single entity, e.g. you could have a Car entity in EF that has some of its values in BaseProduct and the car-specific values in Car. Same for TV. That way, if you can move your calculations from SQL to EF, you could simply use a nice object model and don't worry about JOINs and everything ... Commented Nov 14, 2013 at 10:01

1 Answer 1

Reset to default
1

There is a thing about EF that I don't think everybody is aware of when designing the relations.

When you query something, EF (at least <= 4) wants to create a single SELECT for that query.

What that implies is that if you have entity A, that have a one-to-many relationship to entity B (say Item to Attributes) then EF joins the two together such there will be a returned row for all dependent Bs for each A. If A have many properties, multiple dependencies or even worse if B has many sub-dependencies, then the returned table will be quite massive, since all A-properties will be copied for each row of dependent B. Over time, when your entity models grow in complexity, this can turn into a real performance problem.

EF only includes the Bs if you explicitly tell to it to eager load the dependencies "include"s. If the includes are omitted, your stuff will initially load faster, but once you access your attributes, they will be lazy-loaded by EF. This is known as the SELECT N+1 problem (each A will require N times B-lazy queries, which can be a huge overhead).

While this is not a straight answer to your question, it is something to consider when designing your tables.

Also note, that EF supports several alternatives for base-classing. One strategy is to have a common table, that automatically joined together with the sub-entities. The alternative, which typically performs better, but is harder to upgrade, is to have one table with a super-set of all properties of all sub-classes.

More (over) generalized database design considerations:

  • The devil is in the details. You can make a whole career out of making good database design choices. There is no silver bullet database patterns.
  • EF comes with a lot of limitations. This is the price for the convenience. If the model suits EF well, then EF is quite good, but do consider more flexible alternatives like NHibernate. Sometimes even plain old data tables with views and stored procedures are to be preferred.
  • EF is not efficient if your model has a lot of small dependents (like a ton of attributes to an item table). It will result in either a monster query and return table or the select n+1 problem. You can write some tricky multi-part LINQ queries to somewhat compensate, but it is tricky.
  • SQL's strength is in integrity and reporting which works best for rather rigid data models.
  • Depending on the details, your model looks like a great candidate for a NoSql backend, like RavenDb and MongoDb. NoSql is much better for dynamic datamodels and scale really well.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you holstebroe! Please read my concerns about your suggestions in my last comment above. Thanks
If you have attributes that are not just custom metadata, but are used in actual business logic, then it makes sense to promote the attribute to something less dynamic. E.g. if "price" is an attribute shared by all entities, then you could dedicate an attribute for the base class. All in all, I don't think there is a clear-cut answer to how to represent attributes. It depends on an analysis of how likely it is that they will change, count (tens or tens of millions), if they can be shared.
Note that EF supports both stored procedures and views. Though they both come with a maintenance overhead, they allow you a few more degrees of freedom in your design.
Thank you very much. I also think that there is no clear-cut answer. Most of the attributes aren't shared and it must be possible to reference them for particular calculations. So you think that my antipattern approach is an acceptable compromise in the end?

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.