Going One to One relationship or not? (1 Viewer)

[Mike Krailo]

I am helping out with a client that has designed one of his tables with the name LeadsJobsForm and it is true that there is a One to One relationship between Leads and Jobs but they are different in that the timing of when they occur in the process is distinctly different. A Lead is simply a customer who has not decided what type of options they wish to select yet and this process can go back and forth for days if a customer is taking their time deciding. The moment they make a decision to sign off on a proposal of options that they have selected, that's when the actual Job starts to make the custom window treatments. If they don't sign off on anything then the Lead goes dead and nothing else happens.

My question is: Should there be a separate table for Leads because of the fact that all of the attributes of Job do not describe a Lead? 2NF violation? I am thinking having two separate tables would enhance the reporting capabilities when analyzing Leads and Jobs but I also understand it is rare to have a One to One relationship.

 

[plog]

Need more information. Can you post the structures of the tables you propose for using a 1-1?

 

[Mike Krailo]

Well here is part of the LeadsJobsTable and I have some notes on what I am proposing to do. The CusID would then go into the LeadsTable. The BidAmt and TotalSale fields are not calculated values in this database, they come from the sales personnel that work up and finalize a proposal for the customer to sign off on prior to starting the Job.

 

[The_Doc_Man]

IF there is going to be a time delay between one entry and another, make it a one-many (since "Many" includes both 0 and 1 as well as higher numbers.) Having a one-one relationship COULD lead to issues if you needed to enforce relational integrity. It is possible that you would be unable to enter data into EITHER table with a one-one and RI in place.

 

[CJ_London]

I think it depends on the parent/child relationship

can one lead result in many (or no) jobs, or only one?
can a job exist without a parent (a lead)?

 

[Mike Krailo]

CJ, the same customer can walk into the store on many different occasions for different projects that they need to accomplish. A Lead is like a new project that hasn't had all the details hashed out yet. So a lead could end up never turning into a job, or many new projects can be proposed that the customer signs off on and then an actual Job is initiated. So every job must first be a Lead.

Now on to the Jobs:
There seems to be a one to one sort of a relationship between Leads and Jobs and I think that's why he just lumped them all into one table.

I recently developed code to calculate a commission structure for his two Tiers of commission rates:
The current schema caused a big issue for me since I assumed that when we initiated a new Lead, I could also assign a commission structure at the same time. This was for calculating two different sliding scales of commission rates that depend on the type of items that are sold. The owner of the business wanted to split the items into two categories to make this easier to calculate so in most cases, there are either Tier 1 or Tier 2 type commissions for a given job but on occasion, there are BOTH. That means, two separate jobs would have to be created. That was a design trade off made a while back. But now the problem is, the sales personnel don't know which category to use yet since they are just in the "make choices about the project" stage. Once the customer makes a decision, then know exactly which category the job will be in.

So to sum this up. One Lead can have 0, 1 or 2 Jobs. So I guess this is really a one to many after all. This really drove me crazy as I tied the code that set which category the commission went into, in two different buttons that create a Job (Tier 1 Job and Tier 2 Job). Now I see that there should be a separate table for the Leads and this just might solve all the problems that I was having.

 

[CJ_London]

sounds like you have your answer, good luck with your project

 

[Mike Krailo]

Sometimes, writing it all down reveals the answer. So thank you for your support. I'm clicking on your like button now.

 

[theDBguy]

Hi Mike. Whatever you do, I suggest you make sure to document it. If you didn't originally design this database, then I am guessing you have no idea why they decided leads and jobs are in the same table. Later on, maybe after you may have moved on, the next developer might think to ask why are they not together in one table and think about joining them again. Cheers!

 

[Mike Krailo]

Good point DBGuy, I am not making any changes yet, just making a proposal to do so with the client. And about the joining back together, that is another possibility that I could not figure out how to do the commission calculations that way but I'm sure there is a way to do it, it's just above my head.

 

[Pat Hartman]

When you create a 1-1 relationship, the "master" table uses an Autonumber as its primary key and the "child" uses an integer. You ALWAYS need to use Left Joins between these two tables because sometimes you will have "child" and other times you will not.

If jobs can exist without leads, then you cannot split the tables. You just need some indicator that gets set (it could be a start date) to identify when a lead becomes a job.

The way Access identifies 1-1 vs 1-many is how the two tables are related.
1-1 = PK to PK
1-m = PK to FK (the FK might be a part of a multi-column PK but it will never be a single field PK)

 

[Mike Krailo]

No no, that's not the situation Pat. A Lead might not have a Job, but all Jobs must and will have a Lead. Sometimes a Lead will have two Jobs. So I think there is a One (Lead) to Many (Jobs) relationship in this case. I didn't realize that at first but pretty sure that's what it is. To be honest, I have never had to create a One to One type yet so thanks for the info.

 

[Pat Hartman]

As I said earlier, 1-1 is extremely rare in the wild So 1-m it is.

 

[The_Doc_Man]

Sometimes, writing it all down reveals the answer.

Mike, you have recognized a CRUCIAL fact. Organize your thoughts before you try to do too much with Access. The more that you organize ahead of time, the less you will have to do an "Oops" retrofit later. So congrats for recognizing the value of that idea! And I'm not trying to be snarky. I want to reinforce correct behavior!