Abstracting properties

• Roy Wilson

  Grasshopper

  Points: 19

  More actions

  January 15, 2004 at 8:49 pm

  #161089

  Maybe I'm just a bit dumb and forgot how I did this properly. I'm trying to write a table structure which can store various properties for object types. In this case, it applies to asset management.

  For example, asset A is of type "computer", which has properties: Make, Model, and Purchase Date.

  So I have some tables:

  Asset - assetID (PK), assetType (int), assetName

  AssetPropType - propID (PK), typeID (int), propName (varchar)

  AssetProp - assetPID (PK), assetID (varchar), assetProp (int), assetValue (text)

  Basically, assets can have one type. Types have multiple properties. The AssetProp table houses the assets properties for the given type.

  I'm trying to write a query (stored procedure) which will join AssetProp and AssetPropType that will show all the properties of the type and values if the asset has any in the AssetProp table.

  CREATE PROCEDURE [GetProps]
  (
  @AssetID varchar(20) = null,
  @AssetType int = null
  )
  AS
  BEGIN

  SELECT
  a.propID, a.propName, a.propType, a.propExtra, b.assetValue
  FROM
  assetproptype a LEFT OUTER JOIN assetprop b ON a.propID = b.assetProp
  WHERE
  a.typeID = @AssetType AND
  b.assetID = @AssetID
  ORDER BY a.propOrder

  END
  GO

  I'm pretty sure there is a better way to do this, either via stored procedure or joined call.

  Help?

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  January 16, 2004 at 11:11 am

  #490498

  You wrote you're trying to write a table structure, right? 

  CREATE TABLE AssetTypes(

  TypeID int IDENTITY PRIMARY KEY,

  Descr varchar(20))

  CREATE UNIQUE INDEX ux_AssetTypes_Descr ON AssetTypes(Descr)

  CREATE TABLE Assets(

  AssetID varchar(20) PRIMARY KEY NONCLUSTERED,

  TypeID int REFERENCES AssetTypes,

  AssetName varchar(400))

  CREATE UNIQUE INDEX ux_Assets_IDs ON Assets(AssetID,TypeID)

  CREATE CLUSTERED INDEX x_TypID ON Assets(TypeID)

  CREATE TABLE Properties(

  PropId int IDENTITY PRIMARY KEY,

  PropOrder int,

  Descr varchar(400))

  CREATE UNIQUE INDEX ux_Properties_Ord ON Properties(PropOrder)

  CREATE UNIQUE INDEX ux_Properties_Descr ON Properties(Descr)

  CREATE TABLE TypeProps(

  TypeId int REFERENCES AssetTypes,

  PropID int REFERENCES Properties,

  PRIMARY KEY(TypeId,PropID))

  CREATE TABLE AssetProps(

  AssetID varchar(20),

  TypeID int,

  PropID int,

  Value varchar(400),

  PRIMARY KEY (AssetID,TypeID,PropID),

  FOREIGN KEY (AssetID,TypeID) REFERENCES Assets(AssetID,TypeID),

  FOREIGN KEY (TypeID,PropID) REFERENCES TypeProps(TypeID,PropID))

  CREATE PROC GetProps @AssetID varchar(20) AS

  SET NOCOUNT ON

  SELECT p.Descr, x.Value

  FROM Assets a JOIN TypeProps t ON t.TypeID = a.TypeID

   JOIN properties p ON p.PropID = t.PropID

   LEFT JOIN AssetProps x ON x.AssetID = a.AssetID AND t.PropID = x.PropID

  WHERE a.AssetId = @AssetID

  ORDER BY p.PropOrder

  
  
  --Jonathan