Sequential Primary Key generation across multiple tables

[EdaxFlamma]

Hey everyone,



My design may be inherently flawed but I have a manufacturing process I am trying to document via an Access Database (2000). A simplified example of the issue I am facing: I have different raw component lots that show up on incoming invoices (ItemInID), all of which need to pass certain quality control tests before they can be used in the production process. There are 3 different tests but not all tests always apply to each type of item. Some require 1 out of the 3, others 2, some all three.

I could just make a table that contained all the tests (tblFullQCLogID) and link it to the lot number (ItemInID). However, that would leave a bunch of blank records for each test that was not used. In actuality there are more than 10 tests so the potential for empty values is rather large.

Instead, my thought was to have different tables for each test type, (tblMoistureTestID, tblpHTestID, tblSizeID), but somehow have all the primary keys (MoistureTestID, phTestID, SizeID) sequentially numbered across the three tables. In other words, while the primary key for the moisture test (tblMoistureTestID) would have numbers 1,4,6,8,9... the primary keys for the other two tables would have the remaining numbers so that each individual test is unique. The end goal is being able to correlate them with the lot number (ItemInID) in a junction table to later pull the unique tests required for each item eliminating the need to store a mess of irrelevant blank values for those items that don't need a pH test for instance.

Unfortunately I am at a loss as to how to achieve this and feel as though I may be trying to reinvent the wheel -either that or I am improperly trying to normalize my data.

If you have any suggestions I would be very grateful. Also if I can do anything to make my explanation more clear I would be happy to do so.

Thanks a bunch,
-J.P.

[rpeare]

I'd have a table for tests:

tblTests
TestID  TestName ---> other test specific information
1       Moisture
2       PH
3       Size
etc...

I'd have a table for materials:

tblMaterials
MaterialID  MaterialName ---> other material specific information
1          Material A
2          Material B
3          Material C
etc..

then I'd have a table that tells me which tests need to be performed on which materials

tblMaterialTests
MT_ID  MaterialID  TestID
1      1          3
2      2          1
3      2          3
4      3          1
4      3          2
4      3          3

so the table tblMaterialTests tells you which tests you need to perform on each material

Then I'd have a table for the receipt of the item

tblMaterialReceipt
ReceiptID  MaterialID  ReceiptDate ----> other receipt related fields 
1          1           1/1/2013
2          1           2/1/2013
etc.

then I'd have a table that stores the tests performed for each

tblReceiptTests
MRT_ID  ReceiptID  TestID   TestResult ---> other test result fields
1       1          3       Pass
2       2          3       Fail

So at any point you can look at the tests you SHOULD perform vs the tests that HAVE been performed and enter the results.

I don't have any information on the nature of your tests, for instance if it's a simple pass/fail, or whether you have to record the results of your test. For instance size may have three dimensions (length, width, height) where a ph test may only have one result, that being the actual value.

[rpeare]

One other word, your PH test is likely going to have different values for different materials those tolerances would have to be in the tblMaterialTests table because they are material specific

[EdaxFlamma]

I am in the process of trying this now. Thank you for your help! So far the only thing that is giving me pause is the final table. I was planning on calculating whether or not the materials passed in a future query.

And you are correct that some of the tests do involve a series of data points, a point I should have mentioned before.

In my previous example:

tblMoistureTest
MoistureTestID (PK)
MoistureDate
MoisturePanNum
MoisturePanMass
MoisturePan&SampleMass
MoistureDryPan&SampleMass

tblpHTest
pHTestID (PK)
pHDate
phValue

In another query, this data would be used to calculate the % moisture of the component and that value would be used later to determine pass or fail.

I haven't digested this to a point where I understand how to incorporate something akin to a Lot Number for each material (unless that is just the material ID) but I will try a few permutations of this and get back to you!

Thank you again for your help,
-J.P.


Edit: Would the image below be a possible solution for my various tests having different numbers of values stored depending on the test?

[EdaxFlamma]

Hello all,

Having already posted the previous image I didn't want to change it again. In my previous attempt I wasn't sure whether or not the tblReceiptTests was still necessary with the addition of the test parameter table.

That having been said I tried adding this into a part of my current relationship set up (see attachment below). Again I am not 100% sure if tblReceiptTests is necessary or if it is acting to maintain referential integrity throughout the tables. Assuming the latter was the case, I have kept it my current layout, although the relationships may not be correct given the changes made.

Am I on the right track or have I gone off the deep end? It has been a few years since I've had to build from scratch...



Thanks again,
-J.P.

[Dal Jeanis]

Something looks hinky about the relationships.

I see that tests and parameters are primary items - these are like code tables.

The relationships between tblInvoiceIN, tblInvoiceItemInLot, and tblInvoiceItemsIn look odd. What does tblInvoiceItemsIn represent? Shouldn't it be directly related to tblInvoiceIn? The Invoice has many Items, the item has many lots?

Without straightening that out, if needed, I can't tell whether the tables to the right of that look correct or not.

Can you tell me, in English, what entity each table represents?

[EdaxFlamma]

tblInvoiceIn is for incoming invoices.

tblInvoiceItemsIn is a record of each item found on an invoice as each invoice can have an unknown number of unique items. My intention was to have each ItemInID functioning as a lot number as future quality tests will be performed on the batch of items as a whole. So each unique ItemInID on an invoice will be able to be tested independently.

tblInvoiceItemLot was going to be the table linking all the items for a given invoice. However after you said that, I think you're right; the inclusion of InvoiceID in tblInvoiceItemsIn should be sufficient for that linking.

tlbSuppliers is our list of suppliers and relevant info.

tblTests is used to identify each test.

tblMaterialTest allows an arbitrary number of different tests to be performed on a given ItemInID.

tblTestParameterID is used to designate which parameters will be attributed to the values collected during each test and allow for multiple values to be stored for each test ID. Those parameters are found in tblParameters.

My thought is that it would allow each item to have any number of tests done on it, with any number of parameters per test.

Given that tblReceiptTest was originally designed to store whether or not the item on a given invoice passed or failed, I'm not sure that it is needed now.

Thank you for your help!
-J.P.

[Dal Jeanis]

That makes sense. Sounds good.

I'd also suggest linking tblTestParameterID to tblMaterialTests instead of directly to tblTests.

When you get those changes made, post up again and we'll look it over.

[EdaxFlamma]

I think we may have gotten it. Each item can have its own set of tests, and each test can have its own set of parameters. I have labeled it #1 in the picture below.

If we have solved it, I have one last question on this and I hopefully won't pester you guys any more.

During production we mix the items found on the invoices together to make an intermediate product. After mixing we have to do another set of tests. Would the relationships in number 2 be an adequate solution to this "new" requirement?

tblCommercialBatchTest will be used to characterize the mixtures of the raw components (ItemIDs).
tblComBatchTest is used in the same manner as tblItemTest.

[Dal Jeanis]

I'm not sure. If a commercial batch is a mix of two or more invoice items, shouldn't it have links to the invoice items? If so, there should be a tblComBatch to describe the batch, and a tblComBatchItems to link the batch to the items in it.

[rpeare]

is your tblCommercialBatch going to contain items as well? I don't see a raw item number in any of those tables.

I think you need another table that lists raw and combined products.

When you receive an item you are going to receive something from your table, the test should be related to the item, NOT the PK of the invoice detail.

with your current setup you would have to establish which tests you were going to perform for every item received, every time you receive it.

So if you received, say Sulpher 30 times a month you would have to define which tests you're going to perform 30 times that month.

Where I suspect what you really want is a standard set of tests that you perform on any sulpher receipt.

so, Your table tblInvoiceItemsIn should have a FK to your raw items not the item name and the test should be related to the item table not the invoice table.
the raw/combined products should then be linked to your tblItemTests and your tblItemTests should contain the FK to your items table on the PK of the inventory detail.

[EdaxFlamma]

Oh most definitely! I just didn't include it, sorry about that. I have fleshed it out a bit more for you below.

[EdaxFlamma]

@rpeare: I missed your message before I posted sorry about that. Your assumption was 100% correct. I would much rather not have to enter everything each time. Did I get the gist of your recommendations in this most recent attempt? EDIT: Scratch that I know I didn't. Reprocessing.

[rpeare]

I was thinking more along the lines of what I have below. If you can give another summary of what you expect each table in your current relationship setup to be doing and what each PK is (in my example all PK's are autonumbers) I could comment a bit better.

[EdaxFlamma]

I think I've befuddled myself a bit, so I'm going to back peddle. Please excuse the text wall.

Absolutes and requirements:
An invoice can only come from one supplier.
An invoice can contain many different raw item lots. One raw item lot corresponds to one row of an invoice. For example: Grade A Choice Cut sulfur 1000 lbs.
Each raw item lot can have only one name. In this case Grade A Choice Cut Sulfur.
Each raw item lot can be subjected to any number of different tests.
Each test may have any number of different parameters.
The result of each test parameter must be recorded.
Each parameter has only one tolerance min and one tolerance max. Shouldn't this be dealt with in a query though?
Each construct can be a composed of any number of raw item lots.
The amount of a raw item lot used to form a construct must be recorded.
Each construct can have only one name.
Each construct can be subjected to any number of different tests. Many of which are the same as the raw item lot tests.
Each test may have any number of different parameters.
The result of each test parameter must be recorded.

Assume all PKs are autonumbers.

In rpear's relationship view:
tbleSuppliers is functioning as a list of supplier information.
tblInvoiceIn is functioning to record information about the invoice
tblInvoiceItemsIn is functioning as an item lot number and being referenced to show what items are on an invoice. Also known as an invoice detail.
tblItems is functioning to store the names of the items found on the invoice.
tblTestID is functioning to store the name of each test performed.
tblItemTestParameter is functioning to set the tolerance of the test being performed.
tblTestResults is functioning to store the results of each test and is currently relating them back to the InvoiceItemIn or lot number.
tblConstruct is the one I'm not so sure about. I believe each construct is being treated as its own item so that it can follow the same testing pathway? It would seem however that it would then get related back to tblInvoiceItemID which it does not directly relate to.

Supposed functions in my previous relationship view:
tblSuppliers is functioning as a list of supplier information.
tblInvoiceIn is functioning to record information about the invoice
tblInvoiceItemsIn is functioning as an item lot number and being referenced to show what items are on an invoice. Also known as an invoice detail.
tblItemName is functioning to store the names of the items found on the invoice.
tblItemType is functioning to store the name and numerical placeholder of the item type.
tblTestID is functioning to store the name of each test performed along with its numerical placeholder.
tblTestParameterID is functioning to store the results of each parameter in a test.
tblParameters is functioning to store the name of each parameter and its numerical placeholder.
tblItemTests is functioning to link the individual items with their respective test, and therefore corresponding parameters.
tblComBatchItem is functioning to link all the item lots or InvoiceItemsIn used in the creation of a commercial batch or construct.
tblComBatch is functioning to store information about the commercial batch or construct.
tblComBatchTest is functioning to link the commercial batch or construct to its tests and their results.

Am I correct in my assumptions? I don't want to go crazy and draft something if I've misunderstood.

Thank you both again for all of your help. I really appreciate it.