Solved

Relate Multiple Table IDs to Single Column ID

Posted on 2010-09-03
3
517 Views
Last Modified: 2012-05-10
Ok, I have a rather large relational database I'm building and have run into a problem relating tables to each other.  I need to relate an ID in one table to several other tables.  And this occurs several times.  I'm going to give a fictitious example to try and reflect what I'm doing.

I have a large number of assets which are all very unique with their own properties.  For example, Apples, Oranges, Bananas, and Grapes.  Tied to each of these are Images and Reports.  So I have a table for each asset, which each has an ID number which is a GUID.  The Images table houses all the images, the Reports table houses all the reports.

Now what I'm looking for is this.  What is the best practice for linking the Images and Reports back to the individual asset?

Right now what I've setup is an AssetID in both the Images and Reports table which points to the ID number of the asset owner.  The problem is I have no idea which asset table this ID comes from, it could be from Apples, Oranges, Bananas or Grapes.  With Images this isn't that big of a deal as I only lookup images coming from the Asset-to-the-Image.  For reports this is a problem though.  For reports I have to go from Reports-to-Assets.  The report will vary depending on whether it's for an Apple, Orange, Banana, or Grape.  

I need to be able to do queries on the reports table and then generate reports based on the asset they come from.  Obviously I can setup a stored procedure to loop through the asset tables and find the correct asset, etc.  But is there a better way to do this?

My understanding is that a GUID is always unique, so using one ID column to reference multiple table IDs shouldn't be a problem as far as unique IDs are concerned.  

I could setup a ReportToAsset table that contains ReportID, AssetID, AssetType (the name of the asset table, Apple, Orange, etc.).  To me though this seems just as precarious as what I have setup now... unless there is a way to setup a hard relationship between the asset tables and the ReportsToAssets table.  Is this possible?

So, database experts, what's the best way to solve this?  I'm looking for a solution that can directly relate the record to the proper table.  The solution could be in many forms, an altered table structure, or with a fancy SQL query that figures out the correct relationship.

If I were to delete a report I cannot currently setup a relationship to automatically cascade and delete the asset associated with that report.  I would have to do it manually with code.  What is the standard practice in this sort of information structure?
0
Comment
Question by:aj2010
  • 2
3 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 33598279
You can set them up as
Asset Table
AssetID, AssetDescription

Report Table
AssetID, other report fileds

Image Table
AssetID, AssetImage

From such a structure, you can use joins to obtain any information you want. Also set up tour referencial integrities. Deleting a report should not affect the asset, however deleting an asset should cause the cascade deletion of the reports and images for that asset
0
 

Author Comment

by:aj2010
ID: 33601944
I do not follow your suggestion.  Again, I have multiple assets.  As an example four tables, Apples, Oranges, Bananas, and Grapes.

So I have a Apples.ID, Oranges.ID, Bananas.ID, and Grapes.ID - all uniqueidentifier/GUID

In your suggestion how does Images.AssetID and Reports.AssetID reference the asset table it goes with?  How can I setup a constraint that will cascade and delete a report/image if a record in one of the asset tables is deleted?
0
 

Accepted Solution

by:
aj2010 earned 0 total points
ID: 33711895
I guess nobody had any suggestions.  I solved this problem myself.  What I did was create an Asset table, and then a child table for each type of asset, for example, Asset_Apples, Asset_Oranges, Asset_Bananas, Asset_Grapes, etc.

The Asset table has an Id = GUID.  Each of the child assets likewise have Id = GUID.  The child tables relate to the Asset table as foreign key.  In code my class objects derive from asset, for example, Apple :: Asset.  Inside the Reports and Images table I reference the Asset.Id.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 Make faster Temp Table 17 113
SSRS  - Dropdown with Null 3 30
Help Required 2 39
SQL Server Shrink hurting performance? 4 22
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question