Split Table: Joining the resultant tables

• 

  amachanic wrote:

  ...
  Why is this a LEFT OUTER JOIN? That is not equivalent with the original query--or at least, it should be an option. I'm assuming that this happened because [id2] is now not a PK in [x2], and because the column does not reference [x1]? But on that note, why must the tables share a common column? Shouldn't a foreign key be created, if you're going to enfore that?

  
  The shared column is enforced for foreign keys (we create one automatically on the primary table). It is a left outer join because at the moment the table split support m:1 (m>=1) relations only. While at the time of the split an inner join would suffice, the shared columns in the primary table may be nullable. We could change the join type automatically based on the nullability of the shared columns, but would that be useful?
  
  Andras

  Andras B August 30, 2006 09:19

  - 0 +
• 
  OK, I didn't notice any FKs getting created on the primary table. But I would think you should give the user an option of where the FK gets created. What if I want it on the new table instead? For instance, here's a scenario where it would depend on which columns you actually wanted to move:
  

  CREATE TABLE CustomerNamesAndAddresses
  (
    CustomerName VARCHAR(50)
    CustomerAddressLine1 VARCHAR(50)
    ...
  )
  

  
  Perhaps you'd want to split that into a second table and move CustomerName over so that you could make it unique and enforce the domain via a PK... But what if you wanted to instead move the address columns, and enforce the domain in the original table instead? Then the FK should be on the new table, not on the primary table.

  amachanic August 30, 2006 09:32

  - 0 +
• 

  amachanic wrote:

  OK, I didn't notice any FKs getting created on the primary table. But I would think you should give the user an option of where the FK gets created. What if I want it on the new table instead? For instance, here's a scenario where it would depend on which columns you actually wanted to move:
  

  CREATE TABLE CustomerNamesAndAddresses
  (
    CustomerName VARCHAR(50)
    CustomerAddressLine1 VARCHAR(50)
    ...
  )
  

  
  Perhaps you'd want to split that into a second table and move CustomerName over so that you could make it unique and enforce the domain via a PK... But what if you wanted to instead move the address columns, and enforce the domain in the original table instead? Then the FK should be on the new table, not on the primary table.

  
  I agree. On the other hand, at the moment we support m:1 relations for the table split. If one wants 1:n, in many cases it can be still done by moving the complement set of columns (not always permitted though).
  
  There are many many ways to extend the table split, and I'm sort of afraid that it is already quite complex. Adding control where the foreign keys are created, what joins are used, whether a new key column should be added automatically, are all on the list of possibilities. Some are definitely post version 1 features.
  
  I would be more interested in scenarios where you could apply such table splits, and how the above problems would run into limitations. For example, while we handle indices and drop indices that make no longer sense, shouldn't non clustered indices be dropped completely? Indices are for performance tuning. They should be added way after a table is split Similarly triggers (unless the triggers are for logging or audit purposes).
  
  Andras

  Andras B August 30, 2006 11:06

  - 0 +

Add comment

Please sign in to leave a comment.