Team LiB   Previous Section   Next Section

7.13 Generating Master Records Online

7.13.1 Problem

Your users are accustomed to entering non-normalized data into a table through a spreadsheet interface. However, you want to have the data internally represented using a master/detail structure, replacing character-string values that the users enter with IDs that reference those same values in the master tables.

For example, your users are accustomed to working with spreadsheets like ThesisOld from the previous recipe, but you want the data stored in normalized tables like the ThesisData and ProfessorMaster table.

7.13.2 Solution

Allow the users to enter their data into the non-normalized table as they are currently doing. Write a trigger that takes all newly inserted data into that table and distributes it between a separate set of master/detail tables. To make the solution more robust, you can create a VIEW that takes data from the master/detail tables and returns it in the non-normalized format. You can also write a stored procedure to support updates.

The steps to implement this solution are described as follows. The code shown in this recipe is based on the same ThesisOld and ProfessorMaster tables that were used in the previous recipe.

7.13.2.1 Step 1: Setting up the tables

This recipe is based on the three tables shown in the previous recipe. Their structures are as follows: 

CREATE TABLE ThesisData( 
   StudentId INTEGER,
   Member1 INTEGER,
   Member2 INTEGER,
   Member3 INTEGER,
   Grade CHAR(2)
)

CREATE TABLE ThesisOld (
   StudentId INTEGER,
   Member1 CHAR(255),
   Member2 CHAR(255),
   Member3 CHAR(255),
   Grade CHAR(2)
)

CREATE TABLE ProfessorMaster(
   ProfessorId INTEGER IDENTITY,
   ProfessorName CHAR(255) UNIQUE
)

The ThesisOld table will be used as the non-normalized table into which new records are inserted. The ProfessorMaster table will represent the master table in the master/detail relationship, while the ThesisData table will represent the detail table.

7.13.2.2 Step 2: Creating the INSERT substitute

For every record inserted into the ThesisOld table, there must be a corresponding insert into the ThesisData table. The ThesisData table contains the StudentId and the Ids of the three members of the commitee. Whenever a new professor name is inserted into the ThesisOld table, that name must be used to create a corresponding master record in the ProfessorMaster table. To do all this, create a trigger that takes inserted records from the ThesisOld table and distributes their data appropriately to both the master table and the detail table. Use the following code: 

CREATE TRIGGER ThesisOldIns
ON ThesisOld
FOR INSERT
AS BEGIN
   
   DECLARE @StudentId INTEGER
   DECLARE @Grade CHAR(2)
   DECLARE @Member1 INTEGER
   DECLARE @Member2 INTEGER
   DECLARE @Member3 INTEGER
   DECLARE @Name1 CHAR(255)
   DECLARE @Name2 CHAR(255)
   DECLARE @Name3 CHAR(255)

   DECLARE ThesisOld CURSOR
      FOR SELECT StudentId, Member1, Member2, Member3, Grade 
             FROM inserted
   OPEN ThesisOld
   
   FETCH NEXT FROM ThesisOld
      INTO @StudentId, @Name1, @Name2, @Name3, @Grade 

   WHILE (@@FETCH_STATUS=0) BEGIN
     
      SELECT @Member1=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name1
      IF @@ROWCOUNT=0 BEGIN
         INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name1)
         SELECT @Member1=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name1
      END
            
      SELECT @Member2=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name2
      IF @@ROWCOUNT=0 BEGIN
         INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name2)
         SELECT @Member2=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name2
      END

      SELECT @Member3=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name3
      IF @@ROWCOUNT=0 BEGIN
         INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name3)
         SELECT @Member3=ProfessorId FROM ProfessorMaster WHERE ProfessorName=@Name3
      END

      INSERT INTO ThesisData(StudentId,Member1,Member2,Member3,Grade) 
         VALUES(@StudentId,@Member1,@Member2,@Member3, @Grade)

      FETCH NEXT FROM ThesisOld
         INTO @StudentId, @Name1, @Name2, @Name3, @Grade 

   END
  
   CLOSE ThesisOld
   DEALLOCATE ThesisOld
END
7.13.2.3 Step 3: Supporting the SELECT statement

To support SELECT statements, create a view on the data and master tables that returns a combined view of the data that matches the data provided by the ThesisOld table. The view for our example would be: 

CREATE VIEW ThesisDataView AS
   SELECT d.StudentId, m1.ProfessorName Member1, 
      m2.ProfessorName Member2, m3.ProfessorName Member3, d.Grade 
   FROM ThesisData d JOIN ProfessorMaster m1 ON d.Member1=m1.ProfessorId
      JOIN ProfessorMaster m2 ON d.Member2=m2.ProfessorId
      JOIN ProfessorMaster m3 ON d.Member3=m3.ProfessorId
7.13.2.4 Step 4: Supporting the UPDATE statement

For UPDATE, the values can be updated in master tables directly (if the UPDATE is related to values that are stored in master tables). You might want to embed this into a trigger, so the mechanism is hidden from the user. For example, the following trigger would handle online updates to the ThesisOld table, which has three member columns. Changes to member names are propagated to the ThesisData table in the form of new Member Id numbers. 

CREATE TRIGGER ThesisOldUpd
ON ThesisOld
FOR Update
AS BEGIN
   
   DECLARE @StudentId integer
   DECLARE @ProfessorIdI integer
   DECLARE @Name1D CHAR(255)
   DECLARE @Name2D CHAR(255)
   DECLARE @Name3D CHAR(255)
   DECLARE @Name1I CHAR(255)
   DECLARE @Name2I CHAR(255)
   DECLARE @Name3I CHAR(255)
 
   DECLARE ThesisOld CURSOR
      FOR SELECT i.StudentId, i.Member1, i.Member2, i.Member3, d.Member1, 
             d.Member2, d.Member3
             FROM inserted i, deleted d
             WHERE i.StudentId=d.StudentId
   OPEN ThesisOld
   
   FETCH NEXT FROM ThesisOld
      INTO @StudentId, @Name1I, @Name2I, @Name3I, @Name1D, @Name2D, @Name3D

   WHILE (@@FETCH_STATUS=0) BEGIN
     
      IF @Name1D<>@Name1I BEGIN
         SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
            WHERE ProfessorName=@Name1I
         IF @@ROWCOUNT=0 BEGIN
            INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name1I)
            SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
               WHERE ProfessorName=@Name1I
         END
         UPDATE ThesisData SET Member1=@ProfessorIdI WHERE StudentId=@StudentId 
      END

      IF @Name2D<>@Name2I BEGIN
         SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
            WHERE ProfessorName=@Name2I
         IF @@ROWCOUNT=0 BEGIN
            INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name2I)
            SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
               WHERE ProfessorName=@Name2I
         END
         UPDATE ThesisData SET Member2=@ProfessorIdI WHERE StudentId=@StudentId 
      END

      IF @Name3D<>@Name3I BEGIN
         SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
            WHERE ProfessorName=@Name3I
         IF @@ROWCOUNT=0 BEGIN
            INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name3I)
            SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
               WHERE ProfessorName=@Name3I
         END
         UPDATE ThesisData SET Member3=@ProfessorIdI WHERE StudentId=@StudentId 
      END


      FETCH NEXT FROM ThesisOld
         INTO @StudentId, @Name1I, @Name2I, @Name3I, @Name1D, @Name2D, @Name3D
   END
  
   CLOSE ThesisOld
   DEALLOCATE ThesisOld
END
7.13.2.5 Step 5: Supporting the DELETE statement

For the DELETE statement, it is enough to delete just the data-table record. In that case, you leave the master record entries for possible further use: 

CREATE TRIGGER ThesisOldDel
ON ThesisOld
FOR Delete
AS BEGIN
   DELETE FROM ThesisData WHERE StudentId IN (SELECT StudentId FROM deleted)
END

Of course, if you are sure that you will not need them anymore, you can also delete them from the master records.

7.13.3 Discussion

The concept described earlier can be applied for online use to implement online distribution of base records to both master and data tables. Most systems are designed with the master/detail table concept in mind. However, most also require users to maintain consistency of the tables manually. If a user wants to add a new row into the system, with no master records for a value in the row, he has to add a master record to the master tables first. Only then the data record can be inserted into the system. That is a two step process.

We can combine these two steps into one, but at the expense of complexity. To do this, use the procedure described earlier to provide functionality of the INSERT, SELECT, UPDATE, and DELETE statements. With that, you provide support for data/master record distribution without the hassle of having the user maintaining master records manually.

7.13.3.1 The INSERT trigger

The INSERT trigger uses a cursor to go through all inserted rows. For each row a series of statements is executed to ensure that each member name is associated with a member ID. If a member name is new, then a new record is created in the ProfessorMaster table. That crucial part of the code is repeated for every column and looks like this: 

SELECT @Member1=ProfessorId FROM ProfessorMaster 
WHERE ProfessorName=@Name1
IF @@ROWCOUNT=0 BEGIN
   INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name1)
   SELECT @Member1=ProfessorId FROM ProfessorMaster 
      WHERE ProfessorName=@Name1
END

The first SELECT checks to see if the member's name is already in the master table. If it is not, the @@ROWCOUNT variable will return 0 and the INSERT statement will be invoked. That INSERT statement creates a new record in the ProfessorMaster table for the new member name. Another SELECT statement is then executed to retrieve the member Id number for the new record, and that Id number is stored in the @Member1 variable.

After all professor name columns are processed, the trigger inserts a row into the ThesisData table using the professor ID values. The following INSERT is used: 

INSERT INTO ThesisData(StudentId,Member1,Member2,Member3,Grade) 
   VALUES(@StudentId,@Member1,@Member2,@Member3, @Grade)

In this way, ThesisOld records containing three member names are converted into ThesisData records containing three member Id numbers.

7.13.3.2 The UPDATE trigger

The UPDATE trigger is similar to the INSERT trigger, but with a few extensions to handle the complexities of changing data. The important differences are in the code that handles each member column. That code looks like this: 

IF @Name1D<>@Name1I BEGIN
   SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
      WHERE ProfessorName=@Name1I
         IF @@ROWCOUNT=0 BEGIN
            INSERT INTO ProfessorMaster(ProfessorName) VALUES(@Name1I)
            SELECT @ProfessorIdI=ProfessorId FROM ProfessorMaster 
               WHERE ProfessorName=@Name1I
         END
         UPDATE ThesisData SET Member1=@ProfessorIdI WHERE StudentId=@StudentId 
      END

Here, the code first checks to see if the old and the new values of the column differ. If they are equal, the change in the row has taken place on a different column, and the rest of the processing for this column can be skipped. If the values are different, an UPDATE has been performed on the original table that changes this column, so we have to update our master and detail tables accordingly.

The first SELECT, together with the subsequent IF statement, retrieves a member Id value corresponding to the new value for the member name and places it into the @ProfessorIdI variable. If necessary, a new row is created in the ProfessorMember table. Finally, the UPDATE statement modifies the member Id reference in the ThesisData table so that it corresponds with the new name recorded in the ThesisOld table.

    Team LiB   Previous Section   Next Section