Section 7.13. Generating Master Records Online

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.