Table Relationship Design Question

  • I have been approached with a design idea that I am hoping to get other opinions on as I'm not at all sold on the idea and was wondering if anyone could provide any insight into pros or cons.

    So let's say there are Claims. Each claim can have multiple claimants, businesses, and documents association with it. But multiple claims can also use the same claimant, business, or document. So there is a many-to-many relationship. Typically, I feel that there would then be intermediate cross reference tables to link claims to claimants, claims to businesses, and claims to documents. The idea presented is to have a single xref table that can link claims to anything. This xref table would have an id in it that represents the right side table to link to from the claim. Below is a mock up of the idea.

    The proposition is that if a new table is ever added that links with a claim then you wouldn't have to add another xref table, just another object id. And if you have 10 tables a claim can link to then you don't have 10 xref tables. This is really a simple illustration of an xref table that could link many objects to many objects.

    My current thought is that once the whole database is flushed out then we might add a table maybe once every 3 or 4 years. And the joins from claim to claimant, etc. are going to have to happen regardless if there is one table or many. I don't know about performance difference. I welcome any input of the design or my thoughts on the design.

    CREATE TABLE Claim (claimpkint, claimdatedatetime, claimstatus varchar(10))

    CREATE TABLE Claimant (claimantpkint,namefirstVARCHAR(40),lastfirstVARCHAR(40))

    CREATE TABLE Business (businesspk int,businessname VARCHAR(40))

    CREATE TABLE Document (documentpkint, documentnameVARCHAR(50))

    CREATE TABLE ClaimAssociation (claimpkint, objectidint, objectpkINT)

    /* Object ids

    Claim - 10

    Claimant = 20

    Business = 30

    Document = 40

    */

    INSERT INTO dbo.Claim VALUES (1, '1/1/2011', 'Open')

    INSERT INTO dbo.Claim VALUES (2, '2/1/2011', 'Closed')

    INSERT INTO dbo.Claimant VALUES (1, 'Jon', 'Smith')

    INSERT INTO dbo.Claimant VALUES (2, 'Jane', 'Doe')

    INSERT INTO dbo.Claimant VALUES (3, 'Howard', 'Jones')

    INSERT INTO dbo.Claimant VALUES (4, 'Ann', 'Smith')

    INSERT INTO dbo.Business VALUES ( 1, 'Water')

    INSERT INTO dbo.Business VALUES ( 2, 'Air')

    INSERT INTO dbo.Business VALUES ( 3, 'Land')

    INSERT INTO dbo.Document VALUES ( 1, 'FirstPhoto.jpg')

    INSERT INTO dbo.Document VALUES ( 2, 'SecondPhoto.jpg')

    INSERT INTO dbo.Document VALUES ( 3, 'ThirdPhoto.jpg')

    INSERT INTO dbo.Document VALUES ( 4, 'FourthPhoto.jpg')

    INSERT INTO dbo.ClaimAssociation VALUES (1, 20, 1) -- Claim 1 links to Claimant 1

    INSERT INTO dbo.ClaimAssociation VALUES (2, 20, 3) -- Claim 2 links to Claimant 3

    INSERT INTO dbo.ClaimAssociation VALUES (2, 20, 4) -- Claim 2 links to Claimant 4

    INSERT INTO dbo.ClaimAssociation VALUES (1, 30, 1) -- Claim 1 links to Business 1

    INSERT INTO dbo.ClaimAssociation VALUES (1, 30, 3) -- Claim 1 links to Business 3

    INSERT INTO dbo.ClaimAssociation VALUES (2, 30, 2) -- Claim 2 links to Business 2

    INSERT INTO dbo.ClaimAssociation VALUES (2, 30, 3) -- Claim 2 links to Business 3

    INSERT INTO dbo.ClaimAssociation VALUES (1, 40, 1) -- Claim 1 links to Document 1

    INSERT INTO dbo.ClaimAssociation VALUES (2, 40, 1) -- Claim 2 links to Document 1

    INSERT INTO dbo.ClaimAssociation VALUES (2, 40, 2) -- Claim 2 links to Document 2

    INSERT INTO dbo.ClaimAssociation VALUES (2, 40, 3) -- Claim 2 links to Document 3

    INSERT INTO dbo.ClaimAssociation VALUES (2, 40, 4) -- Claim 2 links to Document 4

  • My immediate reaction is don't do it.

    I would have a separate xref table for each of the many:many relationships that you need for the following reasons:

    a) It more clearly reflects reality

    b) You cannot include referential integrity constraints with the "one xref" approach

    c) In time you will almost certainly find the columns required on the xrefs differ for various good reasons.

    If you only add new tables every few years it buys you nothing and loses you lots.

    Mike John

  • Thank you Mike for those thoughts. They all make complete sense to me.

  • I would only imagine you would want to do this if you could quantify your seperate cross references against each other.

    e.g. if a document had a monetery value and so did a business and you wanted to sum or average these.

    This is similar to using an interface in .NET

    if this isn't the case then this is not helpful.

    One issue you might have is that you would not be able to use key relationships here very well.

    It could get confusing as you link table would have one column that is a foreign key to many other columns.

  • This is an object-oriented concept. The concept isn't a bad idea in itself, it just doesn't apply to a relational database.

    If you're using an object-oriented framework such as .NET for the application, the application may indeed be structured with a Claim object that has a member ICollection<IClaimItem> in which IClaimItem is implemented by Business, Claimant and document. The application data layer would be responsible for retrieving the data from the relational tables and populating the Claim object and its collection appropriately. My definition of "data layer" includes both application code and stored procedures. A proc can get you halfway there and then the app data layer code finishes the transformation.

    But don't use that app structure unless it really fits the app. It's just as easy in the app to have separate collections for different claim-related object types.

    Semantics are important. Use the right ones: relational for the database, object-oriented for a .NET app. It is always easier to "hang" your app logic on a semantically correct structure.

  • Stephanie. That is my take on it. I completely see that we will have a object-oriented application layer with collections similar to what you describe. But the database will be more performant and better designed as a relational setup at the database and then through the different layers you end up with objects in the application.

    Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply