Idea: normalization helper

• 
  I second that motion. That would be a very cool feature!

  drsql August 30, 2006 23:37

  - 0 +
• 

  amachanic wrote:

  Almost certainly for post v.1, but here's an idea I just thought of that I'd love to see:
  
  Say the database has the following kind of table (unfortunately, all too common):
  

  CREATE TABLE Purchases
  (
      PurchaseId (PK),
      CustomerId,
      PurchaseDate,
      ProductId1,
      ProductId2,
      ProductId3
  )
  

  
  Of course, we all know the issues here. What happens when the customer wants to buy 4 products?
  
  Answer: Refactor the table (using SQL Refactor's Normalization Helper!) into a normalized version...
  

  CREATE TABLE Purchases
  (
      PurchaseId (PK),
      CustomerId,
      PurchaseDate
  )
  
  CREATE TABLE PurchasedProducts
  (
      PurchaseId (REFERENCES Purchases.PurchaseId),
      ProductId
  )
  

  
  Something like that... just an idea of a standard database refactor that would be quite useful -- especially if the software automatically helped re-write all of the stored procedures that dealt with the previous structure.

  
  Basically the table split could already be used for this. It would require some manual work (change the join type, and move a foreign key).
  
  But in the above case the table Purchases can be split using the "Split Table" refactoring into two tables, with the columns: (PurchaseId (PK), CustomerId, PurchaseDate), and (PurchaseId1 , ProductId + moved columns). Then the extra PurchaseId2, PurchaseId3 need to be removed manually from the script.
  
  The script will include all the relevant alters for views, stored procedures.
  To make the above more automatic is beyound the scope of the version 1 of the tool, but we will try to improve it so that it is more customizable.
  
  Regards,
  Andras

  Andras B August 31, 2006 06:13

  - 0 +

Add comment

Please sign in to leave a comment.