SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Db design with Dynamic Properties for Objects
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle123
Starting Member

United Kingdom
2 Posts

Posted - 08/31/2012 :  06:10:45  Show Profile  Reply with Quote
Hello all,

Looking for a best approach really, I'm in the process of designing an asset tracking database where assets may have any number of child assets which in turn may have any number of child assets and so on.

So I think my Asset Table will look something like:

CREATE TABLE dbo.tb_Assets
(
    AssetID		int PRIMARY KEY,
    SKU_Ref		int FOREIGN KEY REFERENCES tb_SKU(skuID),
    Description	varchar(255),
    AssetParent	int FOREIGN KEY REFERENCES tb_Assets(AssetID)
)


With a couple more fields for value etc. There will then need to be some properties that can be attached to certain assets but not all, I want these properties to be user definable and for users to add any number of them and attach them to assets. I was therefore thinking of a Properties table with Property and Description fields with a many-to-many join to the Assets table (some properties would be shared between assets).

However this opens up a few issues, ideally the properties should have different types for example some would be numbers, others would be text.

Any input into the design would be very useful :)

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/31/2012 :  07:09:35  Show Profile  Reply with Quote
The thought process you are going through is in line with what I would be thinking too. Couple of things that may or may not be useful:

1. SQL 2008 and later has a new data type called hierarchyId which is very useful for representing hierarchies. http://technet.microsoft.com/en-us/library/bb677290.aspx

2. For representing different types of Properties, you could have more than one table, one for each kind. There is the SQL_VARIANT data type, but I am not a great fan of it, and have not used it much. http://msdn.microsoft.com/en-us/library/ms173829.aspx

3. If Properties are shared between several assets, you may want to use a link table so the same property does not get inserted into the properties table(s) multiple times.

Edited by - sunitabeck on 08/31/2012 07:10:26
Go to Top of Page

Kyle123
Starting Member

United Kingdom
2 Posts

Posted - 08/31/2012 :  07:33:09  Show Profile  Reply with Quote
Thanks for replying:

1. I wasn't aware of this and we've just upgraded to 2008, so I'll have a look, could you recommend any on-line examples of this being used? If not, no problem, I'll have a trawl through google :-)
2. Hmmm I hadn't considered multiple tables for different property data types, that's an interesting idea I'll have a look at the Variant type too.
3. Yes, I was going to add a unique restriction across the property description and its key, then a joining table to facilitate the m2m join with the asset.

I'm also going to need to create a history table of all movements and changes to assets for tracking through locations, status etc. Have you got any pointers as to where's best to do this, in the client code or database side with triggers etc? Even just a general approach would be handy.

Thanks again

Edited by - Kyle123 on 08/31/2012 07:41:56
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/31/2012 :  09:30:17  Show Profile  Reply with Quote
The MSDN tutorial on hierarchyid is pretty good: http://technet.microsoft.com/en-us/library/bb677213.aspx

Another good resource, even though you will need to buy his book for it, is Itzik Ben-Gan's book: http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030 He has a section "Materialized Path with the HIERARCHYID Data Type" which I liked very much.

For tracking changes, if you are on Enterprise edition, you may want to investigate Change Data Capture: http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx If not, probably triggers are the best option.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000