Search code examples
sql-serverxmldatabase-designentity-attribute-value

Design database based on EAV or XML for objects with variable features in SQL Server?

I want to make a database that can store any king of objects and for each classes of objects different features.

Giving some of the questions i asked on different forums the solution is http://en.wikipedia.org/wiki/Entity-attribute-value_model or http://en.wikipedia.org/wiki/Xml with some kind of validation before storage.

Can you please give me an alternative to the ones above or some advantages or examples that would help decide which of the two methods is the best one in my case?

Thanks

UPDATE 1 : Is your db read or write intensive? will be both -> auction engine Will you ever conceivably move off SQL Server and onto another platform? I won't move it, I will use a WCF Service to expose functionality to mobile devices. How do you plan to surface your data to the application? Entity Framework for DAL and WCF Service Layer for Bussiness Will people connect to your data through means other than those you control? No

Solution

  • I will use my answer from another question:

    EAV:

    1. Storage. If your value will be used often for different products, e.g. clothes where attribute "size" and values of sizes will be repeated often, your attribute/values tables will be smaller. Meanwhile, if values will be rather unique that repeatable (e.g. values for attribute "page count" for books), you will get a big enough table with values, where every value will be linked to one product.
    2. Speed. This scheme is not weakest part of project, because here data will be changed rarely. And remember that you always can denormalize database scheme to prepare DW-like solution. You can use caching if database part will be slow too.
    3. Elasticity This is the strongest part of solution. You can easily add/remove attributes and values and ever to move values from one attribute to another!

    XML storage is more like NoSQL: you will abdicate database functionality and you wisely prepare your solution to:

    1. Do not lose data integrity.
    2. Do not rewrite all database functionality in application (it is senseless)