General Design - involving FK fields

  • I'll apologize on the front end for the "green-ness" of the question, but I'd really like to design my database correctly. OK, new C# payroll application. Standard types of tables you may imagine for such an app - i.e., Companies, CostCenters, PayrollPeriods, FiscalPeriods, PayCodes, Earnings records, etc - you get the idea. I've created an auto-incrementing identity column as the PK for all these tables and created the FKs using the PK of the foreign key table. I would refer to any number of these tables as Master-file type tables - i.e., FiscalPeriods, PayCodes, EmployeeMaster, etc. These tables all have "CompaniesID" as a foreign-key because I may obviously have different sets of FiscalPeriods, PayCodes, etc for each different company. Then I have a (miscellaneous) earnings record called EarningsMiscWrk that contains a lot of these Master-file type table foreign-keys:

    CREATE TABLE [dbo].[EarningsMiscWrk](

    [EarningsMiscWrkID] [int] IDENTITY(1,1) NOT NULL,

    [PayCodesID] [int] NOT NULL,

    [FiscalPeriodsID] [int] NOT NULL,

    [PayrollPeriodsID] [int] NOT NULL,

    [PayrollWeeksID] [int] NOT NULL,

    [Date] [datetime] NOT NULL,

    [EmployeeMasterID] [int] NOT NULL,

    [LineSeq] [int] NOT NULL,

    [RecType] [nvarchar](5) NOT NULL,

    [PositionCodesID] [int] NOT NULL,

    [PayRate] [money] NULL,

    [Hours] [decimal](18, 3) NULL,

    [Earnings] [money] NULL,

    CONSTRAINT [PK_EarningsMisc] PRIMARY KEY CLUSTERED

    (

    Heres the FiscalPeriods table:

    CREATE TABLE [dbo].[FiscalPeriods](

    [FiscalPeriodsID] [int] IDENTITY(1,1) NOT NULL,

    [CompaniesID] [int] NOT NULL,

    [Year] [int] NOT NULL,

    [Period] [int] NOT NULL,

    [StartDate] [datetime] NOT NULL,

    [EndDate] [datetime] NOT NULL,

    CONSTRAINT [PK_FiscalPeriods] PRIMARY KEY CLUSTERED

    and the EmployeeMaster table:

    CREATE TABLE [dbo].[EmployeeMaster](

    [EmployeeMasterID] [int] IDENTITY(1,1) NOT NULL,

    [CompaniesID] [int] NOT NULL,

    [ClockNumber] [int] NOT NULL,

    [EmployeeName] [nvarchar](50) NULL,

    [EmployeeSsn] [nvarchar](20) NULL,

    [StartDate] [datetime] NULL,

    [HolidayRate] [money] NULL,

    [StatusFlag] [nvarchar](1) NOT NULL,

    [PartTimeFlag] [nvarchar](1) NULL,

    CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED

    Here's the problem (or the question): the CompaniesID on the EarningsMiscWrk record is determined by selection of PayCode, but the CompaniesID on the other FK tables (i.e., FiscalPeriods, EmployeeMaster, etc) is not determined by that same selection. Since the FK is nothing more than the ID column of one of these "Master-type" tables, there's nothing to prevent a user from selecting a Company 2 employee for a Company 1 earnings record. I obviously want to ensure that all FK selections are (only) valid for the same company. I don't know whether to address this by changing the design or applying some type of constraint.

    Thanks so much for the help.

  • you can create composite primary keys such as

    EmployeeMaster - CompanyID, EmployeeMasterID

    PayCode - CompanyID, PayCodeID

    EarningsMiscWrk - CompanyID, EmployeeMasterID, PayCodeID

    Foriegn keys can be created on this table on

    CompanyID, PayCodeId refer to CompanyID, PayCodeId in PayCode table.

    CompanyID, EmployeeMasterID refer to CompanyID, EmployeeMasterID in EmployeeMaster table

  • Add an additional unique constraint to the other tables with (tablenameID, CompanyID), then add CompanyId to table EarningsMiscWrk, and then create the FK references to be (tablenameID, CompanyID), instead of just tablenameID.

    Then all references can only be for the particular CompanyID in the EarningsMiscWrk row.

  • Thanks Avamin and Michael Valentine Jones for the quick reply...both of your answers are immensely helpful...

    Just a few follow-on comments.

    I initially had CompaniesID in the EarningsMiscWrk record, but then I received the following comment from the ASP.NET forum on a Dynamic Data issue:

    "...While this may work, I'm not sure that this is necessarily the right answer for you. If I understand your schema correctly, what I find unusual about it is that it stores the Company_ID redundantly: the earning record has it, and then the earning record's paycode has it as well, opening the door for them to be different.

    Instead, would it be conceivable to remove CompanyID from the Earning Record, and have it be implied by selected PayCode? Of course, that would mean that you only get one drop down for Company/PayCode, while you may prefer to first select the company and then select the valid pay codes within it?"

    That's why I took it out; so it's not bad practice to reinstate CompaniesID back into the EarningsMiscWrk record...?

    Also, is it preferable to create the unique constraint on the FK tables via composite primary key (if so, do I maintain the auto-incr Identity field as is..?), or in some other fashion (i.e., index)...?

  • greg (8/19/2008)


    Thanks Avamin and Michael Valentine Jones for the quick reply...both of your answers are immensely helpful...

    Just a few follow-on comments.

    I initially had CompaniesID in the EarningsMiscWrk record, but then I received the following comment from the ASP.NET forum on a Dynamic Data issue:

    "...While this may work, I'm not sure that this is necessarily the right answer for you. If I understand your schema correctly, what I find unusual about it is that it stores the Company_ID redundantly: the earning record has it, and then the earning record's paycode has it as well, opening the door for them to be different.

    Instead, would it be conceivable to remove CompanyID from the Earning Record, and have it be implied by selected PayCode? Of course, that would mean that you only get one drop down for Company/PayCode, while you may prefer to first select the company and then select the valid pay codes within it?"

    That's why I took it out; so it's not bad practice to reinstate CompaniesID back into the EarningsMiscWrk record...?

    Also, is it preferable to create the unique constraint on the FK tables via composite primary key (if so, do I maintain the auto-incr Identity field as is..?), or in some other fashion (i.e., index)...?

    It is not really redundant, since it is being used to identify the fact that it is for only one particular company and to prevent references to lookup items that are not for that company.

    You should leave your primary keys the way they are, and just add the additional constraints with the ID and CompanyID. The constraints are required because a FK must reference a PK, unique constraint, or unique index. The FK reference is what will ensure references to rows with only the correct CompanyID.

    I have used this design in a number of situations where it is necessary that FK reference only a particular set of rows.

  • Michael,

    Perfect....I really appreciate the reply. I was glad to read your answer, because I had already started re-doing all my tables with unique indices and revised (composite) FKs. I also re-added the CompaniesID to the EarningsMiscWrk table. It seems to work great...!

    I'm hoping that a unique index is an ok form of the constraint (as it was really easy to add visually with Management Studio)...

    Thanks again for all your help...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply