referential integrity and possible orphans

[Claire]

Is it possible to create a child with a blank parent field?

I am creating a timesheet database where times go into tblHours. Currently,
the Job number field in this table has a many to one relationship with the
Job number field in the table that lists the jobs(tblJobs).

However, sometimes people will do work that does not have a job number (ie
vacation, or sales). In a perfect world I would have two options:
Enter hours with no job number.
Enter hours with a job number- only if it is listed in the Job Table
(tblJobs).

Is this possible? Can I enforce referential integrity but allow the job
number to be blank?

I do not want to have to create a patch (as in a vacation job number), as
there are really many more tables and hour options that it would have to
filter through, but I also do not want to apply times to a job that does not
exist.

Thanks.

 

[Klatuu]

No, an orphan is an orphan. You need some value to idenitify it as "No Job
Number"

 

[Tom van Stiphout]

On Tue, 9 Sep 2008 12:03:01 -0700, Claire

The way we solved this in our company is to have a "Kinetik IT
Internal" project, with tasks such as vacation, sick time, jury duty,
etc. If that project is selected, the "work" is automatically marked
as non-billable.

-Tom.