SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


General Design - involving FK fields


General Design - involving FK fields

Author
Message
greg-799851
greg-799851
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
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.
avm
avm
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 289
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
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 11793
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.
greg-799851
greg-799851
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
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)...?
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 11793
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.
greg-799851
greg-799851
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search