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 |
|
rahul8346
Starting Member
21 Posts |
 Posted - 2006-05-16 : 01:35:27
|
| Hi,I am preparing the Help Ticket for each an every Asset in my Application. 1) I wish to relate the HelpTicket with the S/W Asset but there are multiple S/w license present with a S/W Asset..i.e, they have1-Many relationship 2) Each pair of S/W License and S/W Product is considered an Assetso i need to store some composite id for that to Uniquely Identify it.Consider my table,Table--------------Software ---Similarly all Type of Assets Like H/W...--------------Productid [PK] ------------------------HelpTicket_RelatedAsset------------------------ticketid [FK]assetCategoryID [FK] ----Type Of Asset Like S/WTypeid [FK] ----SubType of Asset Like for S/W it can beSystem program,Application programProductid [FK] ----Particular AssetId as SoftwareProdIDOptions1------------1) I can create an extra field in HelpTicket_RelatedAsset as Licenseid , which will be empty for other Assets 2) I create an unique assetid which is not there and will be require modify the existing code for H/W and Peripherals (In the Sense create an assetID whenever an Asset is added to the Application)Is There Any other Way out.........Any Sugggestion would be HelpFullTxs for reading.... |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-05-16 : 06:15:53
|
Hi rahul8346,I can never think through these types of problems (or in fact any types of problem) without some examples. The more varied (from very simple to very complex) the better.From what you've described, the design below would be my first try (but I'm just guessing). Then I'd try to fit all my varied examples into that structure and see how it feels. If they don't fit or it doesn't feel right, I'll try something else until it does - getting advice along the way, as required.Try fitting some of your real-life examples into the structure below (or one of your own making), then if you need advice, post it all in the same format as below and we'll be better able to help you.Hope that helps  Note that I tend to prefer surrogate keys with simple attributes (like Id and Name). Obviously if you prefer something else, then change it to what you like.declare @AssetCategory table (Id int identity(1, 1), Name varchar(50))insert @AssetCategory select 'Hardware'union all select 'Software'declare @AssetSubCategory table (Id int identity(1, 1), AssetCategoryId int, Name varchar(50))insert @AssetSubCategory select 2, 'System Program'union all select 2, 'Application program'union all select 1, '?'declare @Product table (Id int identity(1, 1), Name varchar(50))insert @Product select 'My Product 1 ?'union all select 'My Product 2 ?'declare @License table (Id int identity(1, 1), Name varchar(50))insert @License select '?'union all select '??'declare @Asset table (Id int identity(1, 1), AssetSubCategoryId int, ProductId int, LicenseId int)insert @Asset select 1, 1, 1union all select 2, 1, 2union all select 3, 1, nulldeclare @HelpTicket table (Id int identity(1, 1), AssetId int)insert @HelpTicket select 1union all select 2union all select 3select *from @HelpTicket t inner join @Asset a on a.Id = t.AssetId inner join @AssetSubCategory s on s.Id = a.AssetSubCategoryId inner join @AssetCategory c on c.Id = s.AssetCategoryId inner join @Product p on p.Id = a.ProductId left outer join @License l on l.Id = a.LicenseId Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
|
|
|
|
|
Database Problem