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!

your understanding of the situation is splendid enough already

nice job

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.

holler if you don't understand how this works

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?

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

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

Thanks for the tips... and indulging the small strides. I'll get on this sql and cut through the english.

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

Many many thanks for this guidance. Now I finish the conceptual design and get into the code. Best!