Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kyle123
Starting Member
2 Posts |
 Posted - 2012-08-31 : 06:10:45
|
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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-31 : 07:09:35
|
| 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.aspx3. 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. |
 |
|
|
Kyle123
Starting Member
2 Posts |
Posted - 2012-08-31 : 07:33:09
|
| 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 |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-31 : 09:30:17
|
| The MSDN tutorial on hierarchyid is pretty good: http://technet.microsoft.com/en-us/library/bb677213.aspxAnother 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. |
|
|
|
|
|
|
|
|
Db design with Dynamic Properties for Objects