none
Horizontal table partitioning by key RRS feed

  • Question

  • Hi All,

    I'm developing a hosted web application.  In the database design each table is "partitioned" horizontally using a customer key.  Each customer can only see their data based on their SiteID.  That way i'm only managing 1 database/schema across all customers.

    I've been playing around with Linq to Sql and am having issues setting up an association.  I'm getting the following error when attempting to remove an association by setting the Child side reference to null.

    An attempt was made to remove a relationship between a DirectoryItem and a CaseItem. However, one of the relationship's foreign keys (CaseItem.SiteID, CaseItem.StaffVolID) cannot be set to null.

    I have 2 tables, Directory and Case.

    Directory Schema:

    SiteID (PK)
    DirID (PK)
    Name,
    Address,
    etc...

    Case Schema:
    SiteID (PK)
    DirID (PK)
    CaseID (PK)
    StaffVolID (FK) Null

    I have an association between Directory and Case using (Directory.SiteID, Directory.DirID and Case.SiteID, Case.StaffVolID)

    In the Case table, I get the error above when setting the CaseItem.StaffVolAssociation = null.  I assume I'm getting the error because the SiteID is part of the primary key and cannot be set to NULL. 

    My Question:

    To bypass the error, do I need to change my schema in the Case table as follows:

    SiteID (PK)
    DirID (PK)
    CaseID (PK)
    StaffVolSiteID (FK) Null
    StaffVolID (FK) Null

    I'd like to use the SiteID in the primary key since this will never change and *always* be the same as StafVolSiteID.  But it seems as if Linq to Sql needs it this way since StaffVolSiteID can be nullable.

    If that's the case, I assume the SiteID would have to be duplicated to support other associations for all other composite foreign keys that include SiteID int them.  Possibly resulting in a table similar to below:

    Bloated Case table schema:

    SiteID (PK)
    DirID (PK)
    CaseID (PK)
    StaffVolSiteID (FK) Null
    StaffVolID (FK) Null
    CategorySiteID (FK) Null  (each customer can define their own list of categorys)
    CategoryID (FK) Null
    TestSiteID (FK) Null
    TestID (FK) Null

    You can see where this can really bloat out the table, even though SiteID, StaffVolSiteID, CategorySiteID and TestSiteID would never have different SiteID's (other than possibly being null).  Is this recommended database design to take full advantage of Linq to Sql?  Any comments would be must appreciated!



    Monday, August 3, 2009 6:35 PM

All replies

  • Instead of setting the entire association to null (which will attempt to set both parts of the relationship, the SiteID and the StaffVolID, to null), can you just set StaffVolID to null?
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Sunday, August 9, 2009 4:05 PM
    Answerer
  • When I retrieve the case object from the database (prior to update), if I include the association in a LoadWith query option and try setting StaffVolID to null (versus the association) I get the following error :

    Operation is not valid due to the current state of the object.

    That said, If I do not include the association in a LoadWith option during retrieval prior to update, LinqToSql will allow me to assign null to the StaffVolID.  I guess I just need to be sure not to inlcude associations during an update when I first query the database for a fresh copy of the object?  Is that recommended?

    One other point relating to queries:

    I'm also dealing with the issue that if one column in an association is NOT NULL (e.g. SiteID) and others are nullable (e.g. StaffVolID), LinqToSql with generate TSQL with an INNER JOIN between the case and directory tables, versus an OUTER JOIN when using the association in a LoadWith option.  I wish it did it the other way, since StaffVolID is nullable.  This is forcing me to do a manual outer join using Linq versus just including the association in a LoadWith option.

    Any additional comments much appreciated.  Thanks for you help!

    Monday, August 10, 2009 3:28 PM
  • It appears that after LINQ to SQL loads a relationship (e.g. Case.Directory), that you can't set the foreign key properties anymore; you can only set the association. However, that won't work for you either because part of the association is the primary key itself.

    Is there a way you can refactor this database? I'm not sure what it means for a Case's StaffVolID to actually be null. If the Case is not associated with a Directory via StaffVolID -> DirID then what purpose does the SiteID FK serve?
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Tuesday, August 11, 2009 12:58 AM
    Answerer
  • The record numbering in Directory (Directory.DirID) starts at 1 and goes to N for each Site (customer) and requires a composite primary key of (Directory.SiteID, Directory.DirID) on Directory for uniqueness.  The primary key for Case table is (Case.SiteID, Case.DirID, Case.CaseID).  Where CaseID starts at 1 and goes to N for each person in Directory.  Directory also holds people which are on staff (or a volunteer) hence StaffVolID.  They are the people that service the case which requires the foreign key of (Directory.SiteID, Directory.DirID) ->>(Case.SiteID, Case.StaffVolID). I'm not sure how I would refactor the database other than actually adding a StaffVolSiteID and have it allow nulls since Case.SiteID is part of the primary key and cannot allow nulls.

    For now, per your suggestion, i've decided to not load the relationships when i get a fresh copy of the case record prior to assigning values and update.  As you suggested, this allows me to update the value of a field in a composite foreign key versus updating the entire relationship....which gets me past the original error I posted.  It in my opinion it also improves performance since I'm also not required to do a database lookup using the following pattern:

    entity.Relationship = context.EntitySet.Single(record => record.ID = SomeID);

    I do wish L2S would change the way it generates tsql for associations where 1 field of a composite foreign key is NULLABLE.  Per my earlier post, it always does an INNER JOIN, where in my opinion it should do an OUTER JOIN. 

    Tuesday, August 11, 2009 1:48 AM
  • I looked into our bug database and could not see the bug that you've mentioned (INNER vs OUTER JOIN when a component of a composite foreign key which involves a primary key is nullable) filed. Can you head over to https://connect.microsoft.com/VisualStudio/feedback/CreateFeedbackForm.aspx?FeedbackFormConfigurationID=1160&FeedbackType=1 to file a bug for this?
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Wednesday, August 12, 2009 5:32 PM
    Answerer