Enforce inclusion of Employee data in Beneficiary table RRS feed

  • Question

  • I apologize for the vague title of this topic but I'm not sure how exactly to phrase my problem. Here is my DDL:

    CREATE TABLE Employer (
    EmployerID int not null IDENTITY(1,1) PRIMARY KEY,
    EmployerName varchar(100),
    EIN varchar(9),
    Size char(1) )
    CREATE TABLE Employee (
    EmployeeID int not null IDENTITY(1,1) PRIMARY KEY,
    EmployerID int not null REFERENCES Employer(EmployerID),
    LastName varchar(50),
    FirstName varchar(50),
    SSN varchar(9) )
    CREATE TABLE Beneficiary (
    BeneficiaryID int not null IDENTITY(1,1) PRIMARY KEY,
    EmployeeID int not null REFERENCES Employee(EmployeeID),
    LastName varchar(50),
    FirstName varchar(50),
    SSN varchar(9),
    Gender char(1),
    DateOfBirth date,
    EmployeeRelationship char(2),
    CoverageStatus varchar(10),
    CoverageStartDate date,
    CoverageEndDate date)

    My concern is with the Beneficiary table. For my business needs for each employee in the Employee table, there must also be the same employee in the Beneficiary table, with all of their relevant demographics and coverage information. The employee is considered a beneficiary because they are benefiting from the plan coverage. If the employee has dependents, a spouse or a domestic partner who are also covered those individuals must also be included in the Beneficiary table.

    I am wondering if it is possible to somehow enforce this using DDL, or if I need to check this at the time of data insertion instead. Your help and insight is greatly appreciated. Let me know if I need to provide more information. Thanks.

    Monday, April 11, 2011 5:00 PM


All replies

  • How exactly you're entering information into the Beneficiary table? You may have a job that will run and check if you have Employees without Beneficiary, but I'm not sure there is a mechanism in SQL Server to enforce adding a record (except that you can have a trigger on the Employee table that will done it automatically).
    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog
    Monday, April 11, 2011 5:39 PM
  • Right now the information is being inserted manually from an existing staging table (created by multiple incoming employer files combined into one). Eventually I think I will roll all of this into an SSIS package. Perhaps I should add the employee check on the SSIS package instead.

    I have not learned much about triggers yet. Is there a good resource online (aside from BOL) to start with for learning more about triggers? This entire task is daunting to me, it is related to Medicare Secondary Payer reporting, which is all file/record based and I am trying to think using a set-based mind to eliminate as Celko would call them, kludges.

    Monday, April 11, 2011 6:03 PM
  • Well, just try to google on 'SQL Server triggers tutorial'. I can suggest to check this blog post to make sure you started with the correct practices:

    Best Practice: Coding SQL Server triggers for multi-row operations

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog
    • Marked as answer by WeiLin Qiao Wednesday, April 20, 2011 1:37 AM
    Monday, April 11, 2011 6:58 PM