Table Design Help

[CBenfer]

From some discussion in another thread (http://www.access-programmers.co.uk/forums/showthread.php?t=266376) it was suggested that I post a question here regarding table design. Attached is a copy of my DB. I'll mention right now that this is half-completed (if that).

The short explanation of what I'm doing:
I need to create an SPC program. The goal is to have an operator on the floor enter their factory order and other info. The system will then look up the SPC limits for that given set of variables. The SPC limits will be maintained by engineering.

The operator will then enter X number of measurements (part of the spec table) each Y time (not part of the spec table yet, not sure if it is going to be). The system needs to take the operator input, check it against SPC rules (which can be activated or deactivated as needed by spec) and tell the operator if there is a failure. It also needs to display a chart with the spec limits, control limits, and any failures highlighted.

I think that pretty well sums it up. You can read the other thread for a description of the problem I'm having. I can provide more info if you have any questions.

 

[jdraw]

And SPC is?

 

[vbaInet]

And SPC is?

My two favourites from acronymfinder.com are:

Sweet Potato Crunch
Samurai Pizza Cats

 

[CBenfer]

Sorry - I'm used to dealing with manufacturing folks. SPC is Statistical Process Control.

For the purposes of this project, I need to store measurements and then look at past data for trends. If one of several conditions are met the data point is flagged as unusual. The traditional Western Electric rules are here (http://en.wikipedia.org/wiki/Western_Electric_rules) to give you a flavor. We generally have 8 rules that we can use that are similar to those. If the data point is flagged, some action needs to be taken by the operator (hence the requirement to notify them).

 

[CBenfer]

My two favourites from acronymfinder.com are:

Sweet Potato Crunch
Samurai Pizza Cats

Oh, and if anyone knows of a job that involves designing things involving Samurai Pizza Cats, please let me know so I can put in my application.

 

[GinaWhipp]

I'm thinking it's Statistical Process Control. In any event, I do see a problem with Record Source of the Forms as they appear not to have been updated to exclude fields removed from the Forms.

I also do not see any Relationships so not sure how these tables relate to each, perhaps you could provide that information? It will help in trying to link these Forms together.

Is the amount of Test's shown in the Table never going to change? One would normally put that in it's own Table and relate to a particular Part??? And those check boxes are found in two tables (never a good idea), so if if they are always 8 they should be in their own table.

Will wait for answers and go from there...

 

[GinaWhipp]

Hmm, I was typing while you were posting so while I got most of my answers, I would still like to know how these tables are realted to each other, I can't tell be the way the fields are named.

 

[CBenfer]

I probably should have done a better job of describing the tables. Here goes:

Data Storage Table:
ID - Autonumber, key field
Factory Order - Identifier of the job as it moves through the shop
Operation - The step the job is currently at (010, 020, 025, etc.)
Part Number - Which part we are making
Date Time Entered - Timestamp of when the measurements are done
Test 1-8 Fail - Yes/No. Stores any rule failures for display on charts or later reports
Zone - Stores the Zone that the data point is in. Ranges from -3 to 3, excluding zero. Planning to use for some of the rule calculations.
Increasing Decreasing - Similar to zone, but a binary variable.
I also just realized that there should be a "Measurement Name" field here - I may have deleted it by mistake. That would show which dimension or property is being measured at that operation, since there might be multiples.

Spec Table:
ID - Autonumber, key field
Part Number, Operation, Measurement Name - Combined they form a unique identifier which determines the remaining fields. Links directly with the Data Storage table.
Limits and Nominal - Fields entered by engineering to store appropriate limits against which the data will be checked.
Last Update - Date/time stamp of when the record was changed (for ISO purposes)
Number of Measurements - How many measurements make up each "Data Storage" entry. So, if this is 1, then each time someone enters data under this spec they would only put in one entry, then the SPC rules would be applied. If this is 5 it would mean that each time the operator enters data they should have 5 points. The system then applies the rules to the average and range of that data.
Test 1-8 On - Shows whether we're using a given rule for a given spec. So, if we decide that one or more of the rules are not valid for a given measurement we can turn off that rule to prevent false positives.

Measurement Storage Table:
Auto_ID - Key field
Measurement Value - The actual measurement entered by the operator
Parent ID - The ID of the Data Storage Entry field that contains the "header" information - timestamp, fails, etc. - for this particular entry.

I hope that basically answers your questions. Thanks for looking so far and let me know what I missed.

 

[plog]

Let me be more explicit than jdraw: There exist people in the world who don't have a frame of reference for your organization, your database, its purpose or its parts. I'm not really talking about whatever 'SPC' is or just us on this forum. My concern is you not completing the Description field for your tables and the people who come after you to maintain, edit, improve the database will have no idea what you were using the [Factory_Order] field of Data_Storage_Tbl is for. My first suggestion for your tables is to complete the Description field for every field.

If something's a foreign key to another table, note that in the Description. With the database you provided, I have no idea how your tables relate to another.

I see 2 other issues, both of which occur in Data_Storage_Tbl and Spec_Tbl.

Numerated Field Names: When you start putting numbers in field names, its a good sign you need another table. Instead of all those Test_X_Fail fields you need another table that will have a row for every test.

Numeric Data In Text Fields. Factory_Order, Operation & Part_Number all have sample data that are numbers. If all the values going into these fields are actually numeric, have the field that stores these values be numeric.

 

[GinaWhipp]

Hmm, almost... I can kind of see how the Data Storage Table and Measurement Storage Table ore related but I don't get the Spec Table. Is it related by Part Number? Did I understand that correctly?

 

[CBenfer]

I guess I should also say that the basic intent is that when the operator is ready to enter data they click a button. Based on some questions, a new Data Storage record is created. The form also pulls up the current specification limits from the Spec Table. The operator then enters appropriate data which goes into the Measurement Storage table. Once they have entered the correct number of measurements, they click OK and the system checks the data against the appropriate SPC rules. If it is OK, nothing happens. If there is a failure, the operator is alerted. In either case, a data chart is updated to show the most current information. The operator will then be able to exit, or enter more data.

That's pretty much it. There will also be a separate function to edit the specs and some reporting, but I'm not remotely ready to worry about those yet (and they shouldn't be that difficult, anyway).

 

[CBenfer]

Let me be more explicit than jdraw: There exist people in the world who don't have a frame of reference for your organization, your database, its purpose or its parts. I'm not really talking about whatever 'SPC' is or just us on this forum. My concern is you not completing the Description field for your tables and the people who come after you to maintain, edit, improve the database will have no idea what you were using the [Factory_Order] field of Data_Storage_Tbl is for. My first suggestion for your tables is to complete the Description field for every field.

If something's a foreign key to another table, note that in the Description. With the database you provided, I have no idea how your tables relate to another.

I see 2 other issues, both of which occur in Data_Storage_Tbl and Spec_Tbl.

Numerated Field Names: When you start putting numbers in field names, its a good sign you need another table. Instead of all those Test_X_Fail fields you need another table that will have a row for every test.

Numeric Data In Text Fields. Factory_Order, Operation & Part_Number all have sample data that are numbers. If all the values going into these fields are actually numeric, have the field that stores these values be numeric.

Thanks for the input. Regarding descriptions, I usually put them in when I'm done (or mostly done), since I've already made some significant changes to my tables as I've hit roadblocks and based on the responses in the other thread I'll have to make some more. I realize that this makes it more difficult for folks on here, though, so I should probably take care of that before I upload again.

Regarding the numerated field names, I can see your point. I put it in this way since it is easier for me to see and program, but that probably isn't the best way to do this. I'll have to think more about that one and how I would use them if they were in a separate table.

On the numeric data in text fields, Factory Orders and Part numbers are occasionally text rather than numeric, so those are correct. Operation is always a number, but it always has to be three digits (including the preceding zero) to match up with some legacy data, so that's why I chose a text field. I hadn't really thought about it, but I suppose it would make sense to change it to a number and force the format another way.

Thanks for the comments.

 

[GinaWhipp]

I looked at your Tables and yes we need to do some work there.

Just an FYI, I looked at your Forms and... You need to *look* at the Record Source for SPC_Data_Entry_Display_Form because there are missing fields that are still included in the Record Source. Also, upon opening the above mentioned Form it's looking for Open_SPC_Entry_Form because of the code included in SPC_Data_Entry_Display_Form, so right away that's a problem.

 

[plog]

it always has to be three digits (including the preceding zero) to match up with some legacy data

Then I would actually leave it as text. I was just going off the sample data you provided which was all numeric. If it has to match up with text data I would leave it as text so you can have the leading zeroes.

 

[GinaWhipp]

Okay, well, I think I got it figured out, at the very least it should point you in the right direction...

 

[CBenfer]

OK, I finally got a chance to get back on this today (long week). From what I can tell from the way you set up the tables:

I can see the name changes to make it obvious which tables the data comes from, and that makes sense.

You are moving the test fails from the data table to a separate table with a field linking back to the data table. I can understand the logic here, although since I'm not great at forms I'll have to learn a few things about how to edit those records appropriately when I do my pass/fail calculations.

The measurements table is the same, so that's fine (not counting the name changes).

For the specification table, it looks like you also moved whether the test should be on or off to the test table, which I'm not sure about. You also have a link to the Data table, which I think is not quite what I'm looking for. Let me explain and you can tell me if I'm missing something:

The spec table is essentially a list of how the parts should be run at any given time. Many Data Storage entries may point to a given spec table entry. However, once the spec is looked up and used, the link is "broken" from then on. For example, if my spec limits are 5 to 10 today, I want any product run today to use those limits for calculations. If I change the limits to 8 to 9 tomorrow, any new data entered should use those limits for calculations, but I wouldn't go back to any old data that passed and now consider it a fail.

With regard to the test pass/fail and on/off data, the intent in the spec table is to show whether we care about a given rule. If engineering decides that we want to trigger failures based on Rules 1, 3, 5, and 8, but ignore 2, 4, 6, and 7, we should be able to check or uncheck the appropriate boxes. When the calculations are performed for pass or fail, any tests that are turned off would be ignored and automatically set to pass. The intent of the data storage table is to let me know whether a given entry did or did not trigger a failure for the given test. So, I'm not sure that putting both sets of data in the same table makes sense.

Hopefully I explained things a little better - not sure if that helps or not, or if I'm misunderstanding how you have the tables set up.

 

[GinaWhipp]

Not ignoring you... just have to run out today. If no one passes by I'll get to this later tonight!

 

[GinaWhipp]

I have attached a new outline, let me know if I got it right this time...

 

[CBenfer]

Hey there. First, thanks very much for you time so far - I appreciate you making the effort to teach me a little bit about how to create a proper DB.

Some questions for you:

The specification table is going to need some sort of identifier to "link" it to the data. For example, if I'm starting a new job, I will enter some things like the part number and the measurement I'm performing. The system should then look up the spec limits. Once it is "registered" once, it can use the spec table ID, but the first time there needs to be something telling it which one is appropriate.

I'm still a little confused about how you have "tblTests" set up. You have tTestOn as a Yes/No and tTestOff as aShort Text. I get the On (I think), but what is the intent of the data field for Off?

Thanks,

 

[GinaWhipp]

No problem, that's why we're here!

In version 2 there is a link to tblData, take a look in the Relationships window. tblTests - Hmm, I think I got the On/Off from your origianl design but in reality it could just be tTestOnOff. If that's not right please explain the Tests again could be I just didn't quite get it.