yea or nay - Allowing nulls on a FK column

Question

yea or nay - Allowing nulls on a FK column

jabo73

Old Hand

Points: 376
More actions
May 30, 2007 at 3:55 pm

#176868

This problem actually pertains to an Access database but overall its primarily a design question so it shouldn't make much of a difference. Suppose you have an Employees table that has a ProjectID column and this column is a FK to the Projects table. Now every employee may not be working on a project so for some employees the value for this field could be null. However for those employees that are working on a project you want to ensure that there is a related parent record in the projects table.
I know that if I specify "allow nulls" for the Employees.ProjectID column, I can still define "enforce referential integrity" on the relationship between Projects and Employees. That way when a non-null value is used it will ensure a related record in the Projects table. My question is - is that a good design? I have read in numerous db design articles to avoid null values whenever possible and so I want to ensure that this is a good design. Thanks for your input!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

My personal opinion would be to use a linking table to hold the employeeID and projectID. This way if an employee doesn't have a project, then there simply wouldn't be a record in that table with their ID.

Just my two cents...

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

Agreed. This has additional benefit if the relationship ends up being a many-2-many...ie if over time an employee can work on more than 1 project for this then you just add in a start + end date/time, to track a history of same.

Current design only allows tracking of 1 current project.

jabo73 Old Hand Points: 376 More actions · Answer 3

Thanks guys! That does help clear it up. I was not in favor of using that approach (i.e. allow nulls) but my co-worker was very much for it. So I told him I would post the question, in a non-partisan format - which I think I did Hopefully he will see the light. Thanks again.