Re: Table Structure Advice - Mailing list pgsql-novice

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