locked
Dynamic Properties RRS feed

  • Question

  • I am working on a small ERP application. Currently in the warehouse module, we maintain the quantity of a given stock item found in a given warehouse in the database using three tables as shown in this diagram.
    http://img195.imageshack.us/img195/1294/currentdesign.jpg

    We need to add feature which allows the addition of properties dynamically to a given stock item. For example, a given stock item could have a color, size, producer, etc.

    I thought of handling this by creating a table that defines the set of properties that every stock item have like this.
    http://img37.imageshack.us/img37/4273/newdesign.jpg

    The problem here is that this design doesn't allow me to perform queries based on the custom properties. I mean queries like (what is the quantity of stock item x with color "red" and size "45" in warehouse 5).

    I am trying to search for a database design that allows for such queries but could find any suitable design till now.

    any suggestions?
    Sunday, May 24, 2009 11:06 AM

All replies

  • I think the model you have is a common model for dynamic properties. You can use a sub query to achieve what you want, for example
    Select ...
    From StockItem si
    Where (Exixts(Select null from StockItemProperties sips where si.Id = sips.StockItemId and PropertyName = 'Color' and PropertyValue='Red')
               and Exists(Select null from StockItemProperties sips where si.Id = sips.StockItemId and PropertyName = 'Size' and PropertyValue='45'))...

    Another model is to use XML as an extended column for StockItem instead of using an extra table (StockItemProperties) and use XQuery in the WHERE clause.
    http://msdn.microsoft.com/en-us/library/ms345117.aspx
    Tuesday, May 26, 2009 8:00 PM