Table Structure Advice

this is a tough day...

i have two sets of related information...

contract numbers and job numbers.

typically a job number will relate to either a
contract or a parent job number.  i'll get to an
atypical situation in a bit - which complicates the
issue even more.

think along the lines of...

contract 1
-job 1
-job 2
--job 10
--job 11
---job 20
-job 4

job 20 roll up under job 11, job 10,11 roll up under
job 2, etc.

my current line of thinking tells me to lay out my
table t_job_number similar to the following:

t_job_number
job_number_id
fkey_contract_id
fkey_job_number_id
etc...

i would then just leave one of the two blank after
filling in the other with the appropriate value in the
appropriate column.

i would then use my queries and php to manipulate the
data.

however, i'm feeling a bit uneasy that this might not
be the optimal solution.

i definitely appreciate any input as to whether this
is the best there is or if i'm out to lunch on my
table design.

to complicate matters further, not only can multiple
job numbers be use per contract (customer orders
multiple items to create their "setup"), but multiple
contracts can be associated with a job number.  for
example, 10 widgets are "built to stock" as part of
job 1 and assigned to the special "build to stock"
contract id.

at some future point, 6 of job 1's widgets get
assigned to contract 1 and 4 of job 1's widgets may
get assigned to contract 5.  iow, i have the same job
number associated with two different contracts - 1 and
5.

this business rule because i only have one job number
entry and then link each serial number to its
appropriate job number.

off the top of my head, i'm thinking i might be able
to "split" a job number (create a second job number
with the same number) if this special case occurs, but
that sounds very ugly with a high likelihood of traps
down the road.

i guess i could store the actual job number in the
serial number table...  or denormalize (eliminate the
job number table and just store the job number for
each individual serial number in the serial number
table).

i'm not very happy with anything i've been able to
develop based on the current business rules.

as the learning curve ramps up...

again, any advice is appreciated.

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com