none
How best to handle "loose relationships"?

    Question

  • BACKGROUND:

    I have a table that has a "loose relationship" to many different tables.  This table has been around for a while so it’s not eligible to be redesigned at this point.  The table structure is similar to the following:

     

    CREATE TABLE [dbo].[Task](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [ObjectId] [int] NULL,

    [ObjectRecord] [int] NULL

    ) ON [PRIMARY]

     

    1.  ObjectId is to identify the object/table this record is related to

    2.  ObjectRecord is to identify the row this record is related to.

     

    The other fields in the example can be ignored however the idea behind this table is to reuse it for many other related objects.  An example would be a Tasks table in a CRM application.  You might have companies, contacts and opportunities that can all have tasks related to them.  The ObjectId is used to associate the task to another object (company, contact, opportunity, etc…) and object record is used to specify the row in the object.

     

    Many times we need to display the related objects “description” when presenting to the user.  We would do something like the following

    Select Task.Subject, Task.DueDate, Task.CompleteDate, Task.AssignTo, ‘Company – ‘ + Company.CompanyName [Description] From Task Inner Join Company on Task.ObjectId=100 And Company.id=Task.ObjectRecord

     

    FYI: The value of 100 is used internally to define the Company table. 

     

    PROBLEM:

    Take this one step further.  We want to display all tasks for a user in the system and display the related to “description” information with it.  Seeing that a task can be related to many objects what is the best way to solve this.  I do know the number of related tables currently being used though it can increase in the future. 

     

    Option 1)

    Select

    Coalesce(C.[Description],CN.[DESCRIPTION],NULL) FROM Task Left Join (Select 'Company: ' + CompanyName [Description],Company.Id From Company) C on C.Id=Task.ObjectRecord and Task.ObjectId=100 Left Join (Select 'Contact: ' + LastName [DESCRIPTION], Id From Contact) CN on CN.Id=Task.ObjectRecord and Task.ObjectId=200 ... and so forth

     

     Option 2) Use Union ALL query instead of using the Left Joins

     

    Option 3) Use a SQL view to union all the descriptions in the related tables.  ...and so forth.  This would allow for a very simple join.  Select Description From Task Inner join [MYView] on [MyView].ObjectRecord=Task.ObjectRecord and [MyView].ObjectID=Task.ObjectID. The problem I see with combining the tables through unions is the size of data and lack of indexes.  You can’t use indexed views that include Unions

    Select ‘Company: ‘ + CompanyName [Decription], Id ObjectRecord from Company,100 as ObjectId UNION ALL Select ‘Contact: ‘ + LastName, Id, ObjectRecord 200 as ObjectId From Contact

    Option 4) Create a table and populate the related table descriptions then join tothis table.  This table would have to be synchronized when updates occur.

     

    Which would be the best option and is there another approach I’m not seeing.  Keep in mind that changing the design of the table is out of the question.

     

    Thanks!

     

    • Edited by Winters_32 Monday, March 14, 2011 7:32 PM fix formatting
    Monday, March 14, 2011 7:17 PM

Answers

  • You're not going to like my reponse.

    What you have got there is an anti-pattern that has a name. It is called EAV, for Entity Attribute Value.

    Such a setup is as flexible as you describe it to be. That is, flexible to put data in there. To get data out of it is an entire different matter altogether. The downside is, that is does not allow any the DRI that the database provides. It doesn't scale (if the database grows, performance of your queries will degrade fast), and you can expect problems with data type conversion.

    And of course, as you have noticed, it takes a lot more work to write queries.

    Now other people that have suffered from this model have resorted to hardcoding the magic values all over the place (like your 100 for Company), which might be a temporary fix until the database outgrows that again. So if you are sure you will never get to that point, then you might be safe. Otherwise...


    Gert-Jan
    • Proposed as answer by Naomi NModerator Monday, March 14, 2011 11:05 PM
    • Marked as answer by KJian_ Monday, March 21, 2011 8:24 AM
    Monday, March 14, 2011 9:53 PM

All replies

  • You're not going to like my reponse.

    What you have got there is an anti-pattern that has a name. It is called EAV, for Entity Attribute Value.

    Such a setup is as flexible as you describe it to be. That is, flexible to put data in there. To get data out of it is an entire different matter altogether. The downside is, that is does not allow any the DRI that the database provides. It doesn't scale (if the database grows, performance of your queries will degrade fast), and you can expect problems with data type conversion.

    And of course, as you have noticed, it takes a lot more work to write queries.

    Now other people that have suffered from this model have resorted to hardcoding the magic values all over the place (like your 100 for Company), which might be a temporary fix until the database outgrows that again. So if you are sure you will never get to that point, then you might be safe. Otherwise...


    Gert-Jan
    • Proposed as answer by Naomi NModerator Monday, March 14, 2011 11:05 PM
    • Marked as answer by KJian_ Monday, March 21, 2011 8:24 AM
    Monday, March 14, 2011 9:53 PM
  • The amount of data we are talking about is in the 10s of thousands no more and typically it's a lot less.  I will probably go with option 1 after doing some profiling.

    Thanks for your response!

     

    Thursday, March 17, 2011 5:28 PM