Home Forums SQL Server 2008 T-SQL (SS2K8) Best way to effienctly store hugh amounts of data RE: Best way to effienctly store hugh amounts of data

  • aaron.reese (10/3/2012)


    out of interest then; if I was selling books (ISBN, Author, Pages), wine (bottle size, ABV,Vinyard,grape,acidity) and shirts (material,collar type, sleeve length), how would you recommend storing the attributes for those products so that they were both easy to insert and easy to extract.

    1) As I have said before, if you do EAV "properly" (which is VERY difficult/tedious), it really can be an efficient and "proper" design for this type of varied data collections. What you currently have is neither efficient nor proper on a variety of fronts.

    2) Having a table per object type sold is unworkable - how many different types of objects does Amazon sell??

    3) Sparse Columns come to mind as a potential solution for what you are trying to do, especially if you "genericize" them and let the application logic know what column is what attribute for each type of object. Again, that gets "difficult" for the app, but will SIGNIFICANTLY reduce the total columns you would need and also reduce the number of times over the life of the app that you will need to add columns to the table (unless you just start out with some very high number such as 1000).

    For example, have 5 Attribute columns: Attribute1, Attribute2, Attribute3, Attribute4, Attribute5. For Books, Attribute1 would be ISBN, Attribute2 would be Author, etc. But for Wine Attribute1 would be bottle size and Attribute2 would be ABV, etc.

    I note that Sparse columns do carry some downsides of primarily being xml-ish in nature and have quite a few caveats, limitations and provisos. Also, if you did go with a generic set you might have few-enough of them to not really benefit from their overhead.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service