Database Design SQL

Question

My question is: Notice that I am putting MvrId in many tables. What is your opinion of the following desing below. How can I improve it? If you point me to a book I hope is to a page of a book. You can just execute this script on a database called MedicalVariance if you want to see the whole thing.

 USE MedicalVariance; 
    --This is a quick install script 
    --I guess you could even execute this from the front end but that would be overkill 
    --Because of the audience that will install this software are DBA's 

IF EXISTS
(
--The query below will will evaluate to true if it finds a foreign key constraint.
    SELECT 1 From INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_TYPE LIKE 'FOREIGN KEY'
)
BEGIN 
    DECLARE @TableName      NVARCHAR(100)
    DECLARE @ConstraintName NVARCHAR(100)
    DECLARE @DynamicSQLEXEC NVARCHAR(300)
    --DECLARE AND FEED THE CURSOR DATA
    Declare ConstraintCursor CURSOR FAST_FORWARD FOR
    -- Dont worry I wont drop your precious FOREIGN KEYS since the catalog must be MedicalVariance
    SELECT TABLE_NAME,CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_TYPE LIKE 'FOREIGN KEY'
          AND 
          CONSTRAINT_CATALOG LIKE 'MedicalVariance'

    --OPEN THE CURSOR 
    OPEN ConstraintCursor 
    FETCH NEXT FROM ConstraintCursor
    INTO @TableName, @ConstraintName

    --NOW IMPLEMENT THE LOGIC TO DROP ALL CONSTRAINTS
    WHILE @@FETCH_STATUS =0
    BEGIN

        --DYNAMIC SQL IS A PAIN IF YOU THINK YOU GOT BETTER SYNTAX GO FOR IT
        SET @DynamicSQLEXEC ='ALTER TABLE '-- the space is important
        SET @DynamicSQLEXEC =@DynamicSQLEXEC + @TableName + ' '
        SET @DynamicSQLEXEC =@DynamicSQLEXEC + 'DROP CONSTRAINT '
        SET @DynamicSQLEXEC =@DynamicSQLEXEC + @ConstraintName
        PRINT @DynamicSQLEXEC -- make sure this is correct sql syntax
        EXEC(@DynamicSQLEXEC)
        -----------------------Dynamic SQL ENDS-------------------------------------
        FETCH NEXT FROM ConstraintCursor
        INTO @TableName, @ConstraintName
    END;
    CLOSE ConstraintCursor
    DEALLOCATE ConstraintCursor

END;
GO


IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMeds'
    )
            BEGIN

                DROP TABLE MvrMeds
            END;
            GO
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'Mvr'
    )
            BEGIN 
                DROP TABLE dbo.Mvr
            END;
            GO
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsAdminRoute'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsAdminRoute
            END;
            GO
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsPrescribingErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsPrescribingErrors
            END;
            GO
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsTranscribingErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsTranscribingErrors
            END;
            GO
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsProductIssuesErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsProductIssuesErrors
            END;
            GO  
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsProcumentErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsProcumentErrors
            END;
            GO  
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsDispensingErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsDispensingErrors
            END;
            GO  
IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsAdministrationErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsAdministrationErrors
            END;
            GO  

    IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrMedsDocumentationErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrMedsDocumentationErrors
            END;
            GO  

    IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrEmployees'
    )
            BEGIN 
                DROP TABLE dbo.MvrEmployees
            END;
            GO          

    IF EXISTS
    (
            SELECT 1 FROM Information_Schema.Tables
            WHERE  Table_Name = 'MvrCommunicationErrors'
    )
            BEGIN 
                DROP TABLE dbo.MvrCommunicationErrors
            END;
            GO  
-- The way I am putting MvrId in almost every table 
-- Do you recommend it or bless it as good desing?


 CREATE TABLE Mvr
 (
    MvrId INT NOT NULL PRIMARY KEY 
 )
  CREATE TABLE MvrMedsAdminRoute
 (
    MvrMedsAdminRouteId INT NOT NULL PRIMARY KEY,
    MvrId INT
 )
 CREATE TABLE MvrMeds
 ( 
    MvrMedsId INT NOT NULL PRIMARY KEY,
    MvrId INT ,
    MvrMedsAdminRouteId  INT ,
    CONSTRAINT MvrMeds_Mvr_FK FOREIGN KEY(MvrId) REFERENCES dbo.Mvr(MvrID),
    CONSTRAINT MvrMeds_MvrMedsAdminRoute_FK FOREIGN KEY (MvrMedsAdminRouteId) REFERENCES dbo.MvrMedsAdminRoute(MvrMedsAdminRouteId)
 )

 CREATE TABLE MvrMedsPrescribingErrors
 (
    MvrPrescribingErrorId INT NOT NULL PRIMARY KEY,
    MvrMedsId        INT ,
    MvrId INT
    CONSTRAINT MvrMedsPrescribingErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )

 CREATE TABLE MvrMedsTranscribingErrors
 (
    MvrTranscribingErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT ,
    MvrId INT
    CONSTRAINT MvrMedsTranscribingErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )
 CREATE TABLE MvrMedsProductIssuesErrors
 (
    MvrTranscribingErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT ,
    MvrId INT
    CONSTRAINT MvrMedsProductIssuesErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )

 CREATE TABLE MvrMedsProcumentErrors 
 (
    MvrProcumentErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT,
    MvrId INT
    CONSTRAINT MvrMedsOrderingProcumentErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )
 CREATE TABLE MvrMedsDispensingErrors
 (
    MvrDispensingErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT,
    MvrId INT
    CONSTRAINT MvrMedsDispensingErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )

 CREATE TABLE MvrMedsAdministrationErrors 
 (
    MvrAdministrationErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT,
    MvrId INT
    CONSTRAINT MvrMedsAdministrationErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )

 CREATE TABLE MvrMedsDocumentationErrors
 (
    MvrDocumentationErrorsId INT NOT NULL PRIMARY KEY,
    MvrMedsId INT, 
    MvrId INT
    CONSTRAINT MvrMedsDocumentationErrors_MvrMeds_FK FOREIGN KEY (MvrMedsId) REFERENCES dbo.MvrMeds(MvrMedsId)
 )


----EMPLOYEES 
--ONLY EMPLOYEES CAN BE PART OF MVR?
CREATE TABLE MvrEmployees 
(
    MvrEmployeesId INT PRIMARY KEY,
    MvrId INT, 
    CONSTRAINT MvrEmployees_Mvr_FK FOREIGN KEY (MvrId) REFERENCES dbo.Mvr(MvrId)
)

CREATE TABLE  MvrCommunicationErrors
(
    MvrCommunicationErrorsId INT NOT NULL PRIMARY KEY,
    MvrEmployeesId INT, 
    MvrId INT,
    CONSTRAINT MvrCommunicationErrors_MvrEmployees_FK FOREIGN KEY (MvrEmployeesId) REFERENCES dbo.MvrEmployees(MvrEmployeesId)
)

Answer 1

If the MvrId in all tables come from the primary key MvrId in table Mvr you should have a foreign key constraint on all MvrId columns. At least if you want to have control over what MvrId's is used in the other tables.

You have not specified why you added MvrId to all those table and depending on how you want the values to be used it can be necessary or it can be a bad idea.

It is necessary if it adds some information about the entity where it is stored it is unnecessary if that information could be retrieved using a relationship to another table.

Ex: MvrMedsPrescribingErrors is a child table to MvrMeds with FK MvrMedsId. If you can use MvrMedsId in MvrMedsPrescribingErrors to find the associated MvrId in MvrMeds then I would not not store MvrId in MvrMedsPrescribingErrors.

But if on the other hand you can store MvrId's in MvrMedsPrescribingErrors that is not the same as the MvrId on the related MvrMeds row then it is absolutely necessary to have MvrId in MvrMedsPrescribingErrors