Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    1-to-N binary recursive relationship

    In my DB I'll have a table, "contractors," with data on each contractor that could be involved in a production system. However, each contractor can also work as a subcontractor to another contractor. On one job, contractor A might hire contractor B as a sub, and on another job, contractor B might hire contractor A as a sub. However, on any given job, a lead contractor may hire many subs, and a sub can have only one lead contractor. So, the relationship between a contractor (lead) and a contractor (sub) is 1-to-N with the lead contractor being mandatory and the sub being optional. (A sub must have a lead but a lead doesn't have to have a sub).

    How can I create a linking table (or "associative entity") -- "contractors_subs" -- to relate contractors to contractors without violating 1NF?

    BTW, I'm not looking for SQL code, but rather for an understanding of the situation.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your understanding of the situation is splendid enough already

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    ack

    You've nailed me with a zen retort. A splendid one.

    So, then, am I stuck with building a simple linking table with three columns: 2 repeating columns using an alias for one of them, contractorID and contractorID2.... and maybe a surrogate primary key?

    Is that solution possible to physically implement? (i.e., will it work?)

    Is there another (better) mechanism that one would recommend?

    Sorry all these questions, but my splendor is highly constrained.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CREATE TABLE job_contractors
    ( jobid INTEGER NOT NULL 
    , contractorid INTEGER NOT NULL 
    , PRIMARY KEY ( jobid , contractorid )
    , FOREIGN KEY ( jobid ) REFERENCES jobs ( id )
    , FOREIGN KEY ( contractorid ) REFERENCES contractors ( id )
    , supervisorid INTEGER NULL
    , FOREIGN KEY ( jobid , supervisorid ) 
        REFERENCES job_contractors ( jobid , contractorid ) 
    );
    holler if you don't understand how this works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104

    I'll try to piece it together

    OK, admittedly, I haven't yet gotten into the SQL. I'm soooo close to being finished with my logical design. Then I'll dive into putting it into physical form.

    Meanwhile, I see that you're creating a table, job_contractors, with a composite primary key, jobid and contractorid. Is this assuming I have a table named jobs and one called contractors (with primary keys, jobid and contractorid, respectively)?

    Can you help me understand what's going on with the below code?

    Quote Originally Posted by r937
    Code:
    , FOREIGN KEY ( jobid ) REFERENCES jobs ( id )
    , FOREIGN KEY ( contractorid ) REFERENCES contractors ( id )
    , supervisorid INTEGER NULL
    , FOREIGN KEY ( jobid , supervisorid ) 
        REFERENCES job_contractors ( jobid , contractorid )

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    Meanwhile, I see that you're creating a table, job_contractors, with a composite primary key, jobid and contractorid. Is this assuming I have a table named jobs and one called contractors (with primary keys, jobid and contractorid, respectively)?
    yes

    what that code does is set up a foreign key for jobid that references the jobs table

    then it sets up a foreign key for contractorid that references the contractors table

    supervisorid is the contractorid of the contractor who is the supervisor for the contractor on this job

    it's NULLable because the contractor may not have a supervisor

    then there is the foreign key that relates the supervisor/job combination back to the primary key of the same table, establishing the the relationship of the supervisor on this job

    i think the actual SQL says the same thing but is a lot less verbose

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    104
    OK, that seems straightforward. (So, I see that in sql one creates the column then defines its references.) Could I get a couple other clarifications just to make sure I have this right?

    Re. the jobs table, is that just a one-column reference table (or maybe a two column table with a non-key column for "jobname" or something like that)?

    Re. the supervisorID column: the user would, thus, input the value of the supervisorID into a record based on business knowledge. Is that right?

    (And, since supervisorID is nullable... its "participation" is optional in the logical design, right?)
    Quote Originally Posted by r937
    supervisorid is the contractorid of the contractor who is the supervisor for the contractor on this job

    it's NULLable because the contractor may not have a supervisor
    I'm just now settling into the fact that a table can have a column act as a foreign key to another column in the same table. That is not, then, a transitive relationship, correct? (I'm still getting accustomed to the normal forms, but as I get it, one of the corollaries of the 3rd NF is that transitive relationships violate it.)

    I'm also seeing from a toddler's perspective how denormalization is an optimization between "referential integrity" and a query simplification. I need to step into the world of sql and get a feel for this end of things to gain the full view... err..

    Quote Originally Posted by r937
    then there is the foreign key that relates the supervisor/job combination back to the primary key of the same table, establishing the the relationship of the supervisor on this job
    Thanks for the tips... and indulging the small strides. I'll get on this sql and cut through the english.

    Quote Originally Posted by r937
    i think the actual SQL says the same thing but is a lot less verbose


  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have understood everything correctly except the transitive thingie

    3rd normal form deals with the relationship of non-key attributes to the entire key

    this table is clearly in 3NF because every column that is not part of the PK is uniquely and unambiguously determined when you specify a value for the PK

    the only non-key attribute is the supervisor id, and what 3NF says is that if you can give me the id of the contractor and the id of the job, i can tell you precisely whether there is a supervisor for that contractor on that job, and if so, who it is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2009
    Posts
    104
    Many many thanks for this guidance. Now I finish the conceptual design and get into the code. Best!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •