Table Design Help

[CBenfer]

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.

I looked in Relationships and I see where you have it connected, but here's my problem:

As a user, I'm going to input certain features of the job I'm about to run - Part Number, Sequence or Operation Number, and Measurement Name. From there, the system is going to need to look up the correct limits in the Specification Table. So, those fields need to be in the Specification Table for me to look up the correct one to use.

I have the same variables in the Data table because I may want to access them later. So, for example, if we decide to delete a specification I will remove that item from the Spec table, but I'll still want to look up later what the part number was.

Not sure if that makes sense.

Regarding the Tests:

In the spec table, the engineer (or whoever has access) will designate which tests apply. So, of the eight standard tests, we may choose to only apply #1, #4, and #6. I stored those as flags for "used" or "not used". When the operator enters data, the system should check the most recent entry and see if it violates any of the tests that have been deemed applicable, so I need to know if the part passed or failed each test, which will need to be stored for later retrieval.

Hopefully that helps.

 

[GinaWhipp]

I think what you're saying is you need a Junction table to store Parts as they relate to Specifications so that you can add/subtract as needed. Do I get that right?

 

[CBenfer]

I think what you're saying is you need a Junction table to store Parts as they relate to Specifications so that you can add/subtract as needed. Do I get that right?

I'm not sure. Each part should only ever refer to one specification at most, but it can refer to no specifications if the spec has been deleted since the part was run.
From what I can tell, Junction tables are typically used for many-to-many relationships, but you might be referring to a use of them that I'm not familiar with.

I don't feel like I've answered your question, but I'm not sure what else to say.

 

[CBenfer]

I was trying to think of a better way to describe what I wanted in the last post, I came up with a different thought entirely. If I don't allow records to be deleted from the specification table, there is a many-to-one relationship between the data storage table (which contains info for a given Factory Order) and the specification table. I can add an "active/inactive" field to the specification table which would allow me to inactivate specs without deleting them, which would keep the relationship intact. To do so, I would need to move the Operation, Part Number, and Measurement name out of the data table and into the spec table, but I think that would work. Does that make more sense?

 

[GinaWhipp]

My apologies... only had 3 hours of sleep and apparently this coffee is not working! My answer doesn't even make sense to me.

Yes, what you posted does make sense AND I always like Inactive as opposed to Delete.