Redundant Foreign Keys?

[kd2017]

I've got a data normalization question, about possibly redundant foreign keys. I have a table [Jobs] that has many [JobValues]. [Jobs] also has many [Orders], and [Quotes]. A "JobValue" not only belongs to an "Job" but it also belongs to a "Order" and/or a "Quote". My [JobValues] table has a foreign key for "JobID", [Orders] and [Quotes] are directly referenced to [JobValues].



My Question: Should my [Orders] and [Quotes] also have a "JobID" field OR should my queries on [Orders] and [Quotes] reference its associated [JobValues] to look up its "JobID"? Or is this all just a matter of preference?

Notes: [Jobs] -> [JobValues] is kind of the core of this database. I'm fairly certain I at least need [JobValues] and [Jobs] to be directly related. The examples in this post are quite slimmed down from the actual DB.


Do like this?


Or like this and use query magic to find a Quote's Job through it's JobValues relationships?

[June7]

JobID definitely does not belong in Orders and Quotes.

[aytee111]

This was a question I had too, can you provide some backup for your response, some links or references where I could go?

[orange]

I agree with June, but I'd like to see a brief plain, English description of "a day in our business".
You are telling us about tables and possible relationships you think might apply, but we know nothing of your business.

Perhaps you could also tell us about Quotes and Orders. I'm not sure what JobValues represents.
I have seen this set up referenced in posts.

[June7]

JobID is already associated with OrderID and QuoteID in JobValues. Why would these associations be repeated in Quotes and Orders?

It appears a unique JobValue is created by combination of JobID, OrderID, QuoteID.

However, this does allow a quote to associate with more than one job and order with more than one job. If this is not desired then something is very wrong with the structure.

Really need a better understanding of data relationships and business flow.

[kd2017]

JobID is already associated with OrderID and QuoteID in JobValues. Why would these associations be repeated in Quotes and Orders?

It appears a unique JobValue is created by combination of JobID, OrderID, QuoteID.

However, this does allow a quote to associate with more than one job and order with more than one job. If this is not desired then something is very wrong with the structure.

Really need a better understanding of data relationships and business flow.

You've given me something to think about. Yes, that is not desired. The dollar value of a quote or order would equal the sum of its associated JobValues. A quote or order should belong to only one job, its JobValues should all be from the same job. I'm having trouble thinking how to enforce this rule with table structure alone. Surely I could rig something up pragmatically but it like to follow best practices here. My thinking is to use a table macro on the JobValues table such that when a QuoteID or OrderID foreign key is changed that it would query this quote/order to see if it's already associated with another value, and if so make sure that value is the same JobID before saving. I don't know if that's easier said than done, I haven't messed with table macros yet.

So for business flow: A single job will have many quotes, these quotes are evaluated by the customer and eventually accepted and awarded as orders/work orders/change orders/contracts/etc or rejected all together, sometimes the customer will negotiate the price. The JobValues are the details of the quote or order. Eventually we invoice off of the "orders". The dollar value of the quotes (and "orders") are composed of several values ie material, labor, overhead, profit, etc. These JobValue items actually need to be quite specific to the job, so they won't actually be that generic but you get the idea.

[kd2017]

I agree with June, but I'd like to see a brief plain, English description of "a day in our business".
You are telling us about tables and possible relationships you think might apply, but we know nothing of your business.

Perhaps you could also tell us about Quotes and Orders. I'm not sure what JobValues represents.
I have seen this set up referenced in posts.

This table structure is similar to what I need, thank you! However, it will take some adaption.

Here is the problem I'm seeing with this table: I would need to invoice against StatementLines, not entire Statements. Typically a job lasts for months and we invoice monthly, we might be able to only bill for 30% of StatementLine X in July and then 20% in August, etc. Now I know I can accomplish this with a many-to-many relationship like this StatementLines -> Invoice_StatementLines -> Invoices. However, we still run into the issue from before that now an invoice could end up having line items from multiple jobs which should never happen.

[orange]

I was trying to just show a picture of what someone else showed as a logical model. In order to understand the relevance of the picture or to adapt it, you really need some detailed facts of your set up. What exactly is a job? Can a job have parts (tasks, item...)? Do you only invoice by Job? etc. etc. With those facts, others may have more appropriate models/suggestions.

Here is a link to a free, generic model for Customers and Invoices that may help put some things into context. You may find others that deal with "partial invoices". There may still be others that deal with monthly invoicing on larger jobs/projects.

In any event, I recommend you take some sample data and some "business scenarios" and using pencil and paper-- test your model. Get others involved to ask questions of the model. Reconcile every anomaly -- and retest until you are sure that the model supports your needs.

You and your co-workers have a much better understanding of your needs than any reader.

At that point you have a blue print for your database design.
Good luck.

[kd2017]

I was trying to just show a picture of what someone else showed as a logical model. In order to understand the relevance of the picture or to adapt it, you really need some detailed facts of your set up. What exactly is a job? Can a job have parts (tasks, item...)? Do you only invoice by Job? etc. etc. With those facts, others may have more appropriate models/suggestions.

A job is a construction project. Yes we only invoice by job. Quotes, orders, and invoices are the focus of this database. Here is a workflow description from a previous reply
"So for business flow: A single job will have many quotes, these quotes are evaluated by the customer and eventually accepted and awarded as orders/work orders/change orders/contracts/etc or rejected all together, sometimes the customer will negotiate the price. The JobValues are the details of the quote or order. Eventually we invoice off of the "orders". The dollar value of the quotes (and "orders") are composed of several values ie material, labor, overhead, profit, etc. These JobValue items actually need to be quite specific to the job, so they won't actually be that generic but you get the idea."

I think I've figured out how to use a data macro to ensure an invoice can't invoice for items from different jobs. How about:
(using the latest table structure from post 7)
1. Give [Invoices] a JobID field
2. Create a query to return two fields for every StatementLineItems: StatementLineItemID and its associated JobID
3. Create a "before change" data macro on the [InvoiceItems] table.
--a. LookupRecord in qry from step 2 where qry.StatementLineItemID = InvoiceItems.StatementLineItemID
--b. SetLocalVar = JobID from record found above
--c. Repeat a and b to find JobID from associated invoices table
--d. if the JobIDs from the two lookup records are not equal then RaiseError

Anyone see any problems with this procedure? Or perhaps I would be better off with evaluating my table structure further to eliminate the need for data macros?

EDIT
------------
The following macro appears to work as described. Again, please if anyone sees any problems with this approach let me know!


Am I asking for trouble in speed/efficiency by running these queries every time I create an invoice? A single invoice could typically have up 20 or more items to bill for each month...
Here is the query:
SELECT StatementLineItems.StatementLineItemID, Statements.JobID
FROM Statements INNER JOIN StatementLineItems ON Statements.StatementID = StatementLineItems.StatementID;

[orange]

I recommend you create a model; test it with some sample data and scenarios; ask more specific questions of the evolving model that relate to more detailed entities (as orders/work orders/change orders/contracts/etc or rejected all together). Depending on your needs --and I would recommend building a model that separates orders, work orders, change orders and contracts since I believe each of these may be a "generic contract"-- You may decide that these can be grouped in some way, but do it from a point of knowledge --don't assume they are the same or different and carry on(do the analysis and decide).
For your own benefit you may find that creating a 2 or 3 line description of each of the entities --that is what differentiates a work order from a change order etc -will greatly assist your analysis and better understand certain processes. I would also recommend you review the model and testing scenarios with colleagues. Don't be too quick to make a physical database. Better to have a tested, reviewed, jointly accepted model as a blueprint and build from that.