Foreign key question

Question

Foreign key question

Miranda Johnson

Mr or Mrs. 500

Points: 523
More actions
October 1, 2015 at 10:05 am

#321636

In the database in question there are the following tables
Job_Request, Job_Requisitions, Jobs, Locations
The Job_Request table has a column for RequisitionNumber that will only have a value once the request has gone through the approval process.
The Job_Requisitions table becomes populated when the approved request is assigned to a job and a location. There are 3 columns in the table with RequisitionNumber the primary key, JobID being a foreign key on the Jobs table and LocationID being a foreign key on the Locations table.
How would I set up a Foreign Key between the Job_Request (RequisitionNumber) and the Job_Requisitions(RequisitionNumber) knowing that the first record created will be in Job_Request which will have a null value in the RequisitionNumber column until after the request has been approved and assigned?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

I think you have it designed backwards. A JobRequest row should have a PrimaryKey of JobRequestKey. A JobRequisitions row should have a foreign key column called JobRequestKey that is not nullable and is filled on insert.

Use "Key" instead of "ID" for Primary and Foreign Keys since ID is generally a human-readable and human filled column. I can already hear everyone complaining but as someone who has to deal with the failures because managers push through this ID crap as keys I will do what it takes to prevent problems for myself.

Miranda Johnson Mr or Mrs. 500 Points: 523 More actions · Answer 2

So if I understand you correctly instead of having 3 columns in the Job_Requisitions table, RequisitionNumber, JobID, and LocationID , you would have a 4th column being the JobRequestID which would be a foreign key off the Job_Request tables Primary Key.

I will keep my naming convention but thanks for the thought on calling them key instead of ID

MMartin1 One Orange Chip Points: 27799 More actions · Answer 3

Miranda Johnson (10/1/2015)
So if I understand you correctly instead of having 3 columns in the Job_Requisitions table, RequisitionNumber, JobID, and LocationID , you would have a 4th column being the JobRequestID which would be a foreign key off the Job_Request tables Primary Key.
I will keep my naming convention but thanks for the thought on calling them key instead of ID

Typically, you see the ID nomenclature in source systems. When it comes to data warehousing we create our own identifier for that row (along with the ID) suffixed "Key". As in surrogate key. You are good to keep it the way you have it as long as ID truly represents a true identifier of that record of course.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27799 More actions · Answer 4

How would I set up a Foreign Key between the Job_Request (RequisitionNumber) and the Job_Requisitions(RequisitionNumber) knowing that the first record created will be in Job_Request which will have a null value in the RequisitionNumber column until after the request has been approved and assigned?

You can have the JobRequest table only reference the JobRequisitions table. The JobRequisitions table can itself play host to the location and job ID's. So in the relational schema this would imply two joins. It keeps things clean.

It is perfectly acceptable to have a NULL until the actual value is known. Keep in mind I am not talking about warehousing here, just your data in the operational system that your application(s) write to.

----------------------------------------------------