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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--- operationsengineer1@yahoo.com wrote:

> 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...

okay, maybe i can use a link table to link
contract_ids and job_number_ids in a many to many
fashion. maybe i can then link the serial_number_ids
to the link_table id (uniquely identifying each serial
number to each set of job numbers and contracts)...

i have to stew on this for a bit...

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster