relationship problems

[Magger]

Hi,

I'm fairly new to accessdb. I am trying to build a db in access based on my use case and getting completely stuck. I'm having trouble translating my requirements into a bunch of tables and relationships. The easy stuff seems to be ok, but I'm really getting stuck on the tricker parts of the use case.

Is this something that I can get help with here?

My use case is:

• A customer can send into a business a number of samples from a batch of product. The customer thinks in batches and seek reports around batches.
• a batch can have many samples (usually up to 8 or 9 though)
• each sample can be present to this business in about 5 different ways. I guess a sample type.
• each customer can order a slightly different number of tests on each sample. there is about 9 different tests can be done but usually its just 1-3 tests per sample.
• a single report per batch are sent back to the customer with the sample test results individually and summarized.

I think that covers most of it. I have tried to draw an ER diagram but still can follow the logic from the above statements and ER.

hope there are some helpful people in here to help me with this project.

thankyou
Magger

 

[plog]

You know the business rules, we know databases in general. The best way to do this is for you to show us what you think it should be and us pointing out glaring errors and asking questions.

So, post the best ER diagram you have

 

[Magger]

this is what I have so far.

 

[Magger]

BatchNo:
I forgot to mention, the "BatchNo" is received from the client and is alpha-numeric and logic changes from client to client. It is however usually unique but could be a chance it isn't I guess.
SID:
The Sample ID (SID) is a number, also from the client and is usually a 1,2,3,4,5,6,7,8,9 or 10 but a small number of clients also use A,B,C... just to be difficult. So logic is "batchNo.SID" to illustrate heirachy. eg if BatchNo = 2A14_2025 and SID=2, then sample identification is 2A14_2025.2
Tests:
There are 9 tests that can be ordered. Usually client orders about 2-3 of these tests per sample.
1 test could have numerous different measured results. eg received weight (g), quality (1 to 10)
SampleType:
there are 4 different sample types you can receive. Any type could be subjected to any tests. Not sure if we need sampletype or just apply tests direct to sample based on what ordered?
TestGroup:
I came up with this idea as most clients order a recipe of tests, like a small group and often just stick to this. eg Basic, Standard, Premium will have different number of tests. Be good to program this somehow.
TestResults:
Each test conducted, we need to input the results into accessdb and later present these test results into the report. Each sample should have a list of tests conducted and associated results.

 

[plog]

The only big issue is TestGroup. When you feel the need to numerate field names (Test1ID, Test2ID, etc.) it's time for a new table. Data should be accomodated vertically (with more records) and not horizontally (with more fields). So to group 4 tests you would add 4 records to some table rather than fill in 4 fields of 1 record.

Of course, after reading your further explanation, I don't think TestGroup should be part of this section of your database. tblClient, tblBatch, User and Roles are all good as is. Here's notes on the other tables.

tblSample - use an autonumber primary key for this table. You can still record the SID the customer gives you, but it shouldn't be the primary key (nor should BatchID). Using an autonumber primary key will make this simpler to reference in other tables.

tblSampleType - TestGroupID should come out of this.

tblSampleTests - new table, this will hold all the data of each test (sort of like TestGroup now)
key fields:
- SampleTestID, autonumber, primary key
- tblSampleID, number, this links back to that autonumber you will be adding to TblSample (see above)
- TestID, number, this links to the Test table to get TestName and TestCode
- Test result fields, e.g. Name, Role

Those changes will now house all your test and results for all samples. If you are performing just 1 test on a sample, you add just 1 recrod to tblSampleTests, if you are doing 20 tests, you add 20 records.


That covers the meat of your database. To implement your groups of tests, we sort of work off to the side of the main database. This is what the new restructured TestGroup table will look like:

TestGroup
TestGroupID, autonumber, primary key
ClientID, number, this will hold the primary key from tblClient to identify which client this group is for
TestID, number, this will hold primary key of Test table to identify which specific test this is
TestGroupName, text, will hold values like Basic, Standard, Premium and be used to group your tests together

That table will now allow you to group tests for a client together. It doesn't hold any real data itself, it just groups together your tests so it can be used to populate tblSampleTests. Here's how it will work:

You will populate this table appropriately--for every client you want to assign a group to, fill out a record for each test of that grouping. Once its set up build a form for users to interact wiht tblSample. There will be a dropdown on that page based on TestGroup.TestGroupName. If the user wants to use a group of tests they will select the appropriate grouping name and click a button. That button will then run an APPEND query and add every test that is assigned to that grouping to tblSampleTests and a form will refresh to show the user all those tests have been added.

 

[arnelgp]

I'm fairly new to accessdb. I am trying to build a db in access based on my use case and getting completely stuck. I'm having trouble translating my requirements into a bunch of tables and relationships.

you can start by the easiest method. Normalization.
Import your records (I hope you have them in a spreadsheet) in a Grand table.
then apply normalization by creating tables from your grand table that
have repeating values. repeat the step further for each step of Normalization rule.

 

[Magger]

The only big issue is TestGroup. When you feel the need to numerate field names (Test1ID, Test2ID, etc.) it's time for a new table. Data should be accomodated vertically (with more records) and not horizontally (with more fields). So to group 4 tests you would add 4 records to some table rather than fill in 4 fields of 1 record.

Of course, after reading your further explanation, I don't think TestGroup should be part of this section of your database. tblClient, tblBatch, User and Roles are all good as is. Here's notes on the other tables.

tblSample - use an autonumber primary key for this table. You can still record the SID the customer gives you, but it shouldn't be the primary key (nor should BatchID). Using an autonumber primary key will make this simpler to reference in other tables.

tblSampleType - TestGroupID should come out of this.

tblSampleTests - new table, this will hold all the data of each test (sort of like TestGroup now)
key fields:
- SampleTestID, autonumber, primary key
- tblSampleID, number, this links back to that autonumber you will be adding to TblSample (see above)
- TestID, number, this links to the Test table to get TestName and TestCode
- Test result fields, e.g. Name, Role

Those changes will now house all your test and results for all samples. If you are performing just 1 test on a sample, you add just 1 recrod to tblSampleTests, if you are doing 20 tests, you add 20 records.


That covers the meat of your database. To implement your groups of tests, we sort of work off to the side of the main database. This is what the new restructured TestGroup table will look like:

TestGroup
TestGroupID, autonumber, primary key
ClientID, number, this will hold the primary key from tblClient to identify which client this group is for
TestID, number, this will hold primary key of Test table to identify which specific test this is
TestGroupName, text, will hold values like Basic, Standard, Premium and be used to group your tests together

That table will now allow you to group tests for a client together. It doesn't hold any real data itself, it just groups together your tests so it can be used to populate tblSampleTests. Here's how it will work:

You will populate this table appropriately--for every client you want to assign a group to, fill out a record for each test of that grouping. Once its set up build a form for users to interact wiht tblSample. There will be a dropdown on that page based on TestGroup.TestGroupName. If the user wants to use a group of tests they will select the appropriate grouping name and click a button. That button will then run an APPEND query and add every test that is assigned to that grouping to tblSampleTests and a form will refresh to show the user all those tests have been added.

wow. Thankyou so much @plog. Just reading your response things seem a little clearer to me. Can see years of experience there. Wow. I've tried to translate what you have mentioned into an updated ERD. I think I've got it correct, but feel free to point anything out thats not correct. Couple of questions come to mind:

1- is it possible for a PK too have relationships to 2x tables? eg tblClient_ID to tblBatch and tblTestGroup
2- is it ok to name the PK in all the tables just ID? or should they be more description, eg CLID for tblClient PK.

I'll whip this up in Access and see how I go and report back. Thankyou once again for your guidance. So helpful!!!

Magger

 

[GaP42]

If I am interpreting this correctly, and in support of suggestions from @plog the table relationships are represented graphically as:

Some questions:
User has a relationship to Sample - what is the nature of this relationship? Are you interested in knowing who tested a sample? (eg in general a User has overall responsibility and can be assumed to conduct the test, however some tests are conducted by someone else?)
UserRole looks to be the role of an Employee, rather than the role a user has in relation to a sample. Are you expecting to be able to say different users can have different roles in relation to a sample .. perhaps tester, contact manager for the sample, reports on ?) If so the relationships would need change.

However in your model a User has many roles, but the user has (responsibility for) many Samples. As the User as attributes like email, name etc - these items would be repeated for the same user in each Role - that is not correctly normalised. Hence the questions above. Generally I would Expect the relationships would be User (1) - (N) Role (N) - (1) Sample

I see your new ER - which appears to be the same - except Sample - User - Role

EDIT:
A further question/ clarification - your updated ER does not show where test results are held. However you did say:

1 test could have numerous different measured results. eg received weight (g), quality (1 to 10)

That would imply another table to hold the results of a test - however I would question whether each of Received Weight, Quality etc .. might be test themselves, or, at least for those egs, observations made on receipt of the sample. Perhaps some more specific results eg % H2O content, ... may provide greater clarity. Perhaps conflating Test Group and Test? At this time, I would be recording a result in the SampleTest table: One TestSample - one result.

TestGroup relates to TestType not to Test - otherwise you are forever doomed to specifying the Group to which a particular performed test belongs. That would not be practical - the Client/Customer has one or more typical panel of tests (defined by type) that are performed against the samples they supply. But that is really me being a bit pedantic about the term Test vs TestType - they are equivalent representations in each ER.

 

[Magger]

If I am interpreting this correctly, and in support of suggestions from @plog the table relationships are represented graphically as:
View attachment 118459
Some questions:
User has a relationship to Sample - what is the nature of this relationship? Are you interested in knowing who tested a sample? (eg in general a User has overall responsibility and can be assumed to conduct the test, however some tests are conducted by someone else?)
UserRole looks to be the role of an Employee, rather than the role a user has in relation to a sample. Are you expecting to be able to say different users can have different roles in relation to a sample .. perhaps tester, contact manager for the sample, reports on ?) If so the relationships would need change.

However in your model a User has many roles, but the user has (responsibility for) many Samples. As the User as attributes like email, name etc - these items would be repeated for the same user in each Role - that is not correctly normalised. Hence the questions above. Generally I would Expect the relationships would be User (1) - (N) Role (N) - (1) Sample

I see your new ER - which appears to be the same - except Sample - User - Role

EDIT:
A further question/ clarification - your updated ER does not show where test results are held. However you did say:

That would imply another table to hold the results of a test - however I would question whether each of Received Weight, Quality etc .. might be test themselves, or, at least for those egs, observations made on receipt of the sample. Perhaps some more specific results eg % H2O content, ... may provide greater clarity. Perhaps conflating Test Group and Test? At this time, I would be recording a result in the SampleTest table: One TestSample - one result.

TestGroup relates to TestType not to Test - otherwise you are forever doomed to specifying the Group to which a particular performed test belongs. That would not be practical - the Client/Customer has one or more typical panel of tests (defined by type) that are performed against the samples they supply. But that is really me being a bit pedantic about the term Test vs TestType - they are equivalent representations in each ER.

Hi @GaP42,

Thanks for contributing. I'll try and respond to your points below:
1. I just wanted to track an employee who conducted the testing of an individual sample. Later, may help identifying an employees throughput or maybe where an employee is making errors.
2. Later, an employee may also be restricted in accessdb of certain forms so no accidents happen in the db. I'll review the User,role,sample relationship as you suggested.
3. Yes. A sample is subjected to say 2 tests. Testtype1 may have 3 results to be recorded. Testtype2 may have only 1. I hope that is a little clearer. So Testtype1+Testtype2 = Testgroup1 which the client ordered. Sorry, but I'm still unclear where the results will be stored. My brain is spinning atm. lol Regarding result fields a test may have eg below:
testtype1 results: Wetweight, Dryweight, moisture%, %ofsample_OK, %ofsample_Rejected etc...
testtype2 results: avg_size, size>20mm, size<20mm etc...

Results type shouldn't really matter in the end, just need to allow for a testtype to have many results fields in case I need to add more down the road.

thanks in advance
Magger

 

[Magger]

you can start by the easiest method. Normalization.
Import your records (I hope you have them in a spreadsheet) in a Grand table.
then apply normalization by creating tables from your grand table that
have repeating values. repeat the step further for each step of Normalization rule.

thankyou @arnelgp, I do have some records, but much of what I'm discussing above are new concepts and the current records don't have such data or context. I'll have to read up on normalization. Thankyou for your suggestion.

 

[arnelgp]

thankyou @arnelgp, I do have some records, but much of what I'm discussing above are new concepts and the current records don't have such data or context. I'll have to read up on normalization. Thankyou for your suggestion.

that is new I supposed.
because from my experienced there must be a a real working manual system first before you translate/convert it to
a database. But yours is the opposite, good luck then.

 

[Pat Hartman]

1. Naming all the PK's "ID" is not "cool". It just causes confusion and completely obfuscates the relationships between tables. It prevents you from looking at a table and figuring out what are the relationships. You MUST open the relationship diagram to see them. Go back to your original names.
2. Use the same name for the PK and FK (even in your original they are different). If you want to identify FK's. Then add _FK as a suffix. This lets you see the FKs but not use duplicate names. Just FYI, the duplicates are never a problem because in a query that includes the child table, you would include only the FK field and never the PK to which it points. Therefore, no duplicate name issue.
3. NEVER, name the FK by the name of the text field in the related table as you did with Role. That is the ultimate confusion. It appears that you are relating Role a text field in one table to Role a text field in a different table and relationships are ALWAYS data field in the many-side table to PK in the one-side table.
4. Are all tests for a sample done by the same person? If so, sample is the table that relates to user, otherwise it is test that relates to user.
5. Test Group and Batch are not different. They are the same and shouldn't be separate tables with no relationship. The tests requested should be a child table of Batch and that is what should be used to populate the tests requested for each Sample.

 

[LarryE]

From what you have given us, it sounds like a design as shown below might give you a starting point:

Notice several points:

1. All Primary Keys have text names. Never use just ID. As Pat said, it just gets confusing later on.
2. You had the field name "Name" in several tables. The word "Name" is reserved in ACCESS and should never be used as a field name. So there is ClientName, TypeName, UserName etc.
3. I included an Order table on the assumption that any client could submit multiple batches for testing on a single day and even if that has never happened, that event should be provided for. So you don't need the SampleReceivedDate in your table.
4. I am still unclear as what users do, but I included the table as per your diagram.
5. If you think you will need added functionality later on, include it now. You will thank me later for that advice.
6. I didn't include a Test Group table because I agree with Pat that it is basically the same as a batch. Each batch already has that information, and you can group them together if you wish later on in the batch report that goes to the client.

 

[mike60smart]

thankyou @arnelgp, I do have some records, but much of what I'm discussing above are new concepts and the current records don't have such data or context. I'll have to read up on normalization. Thankyou for your suggestion.

Hi Magger

Do you have an example of the tests to be carried out on each of the TestTypes Basic,Standard & Premium?

 

[Pat Hartman]

You need to clarify whether one user runs every test for the sample or if some tests are done by different people since that determines which table holds UserID.

 

[GaP42]

So Testtype1+Testtype2 = Testgroup1 which the client ordered

The client specifies, when the batch is supplied, the tests to be applied as one or more test groups?
A test group will involve at least one test. However do all tests belong to a test group?

Core relationships:

• A client raises an order requiring the testing of one or more batches. (from @LarryE )
• For each batch one or more tests, as required by the client, need to be conducted.
• For any batch in an order, one or more samples may be taken that are subject to specific types of tests.
• The types of tests that may be requested (in a order/batch) may be specified as a (client-specific?) test group or individually.
• When one or more test groups are specified for a batch, the type of tests required are thereby specified.
• A sample taken from a batch is subject to one or more specified sample-tests.
• Each sample test may produce one or more test results.
• An employee has responsibility for a sample - conducting the test (NOTE: if the role type for an employee in relation to a sample is only to say that the employee is responsible for the sample, and there is no other defined role for an employee regarding a sample, then there is no need for EmployeeRole or RoleType- the Sample table will record the "ResponsibleEmployeeID" FK). If you need other roles/responsibilities to be recorded for other employees relating to the same sample then retain these entities/tables.

I suggest you sketch out on paper /spreadsheet some consistent sample data for Order/Batch, Batch/Sample, Sample/SampleTest as well as Batch/TestType - where TestGroup(s) are requested, and then SampleTest/TestType. This can help to firm up the names/concepts for the data and workflow.

The Employee table may be utilised later for user access control (as a later extension you suggested), however EmployeeRole is probably not suited to this use.

Which brings me to this ER model:

 

[LarryE]

If you really wish to classify groups of samples as "Basic", "Standard" or "Premium", then just create a batch Rating field in the batch table. That way you don't need to mess with a separate table and the joins it implies:

You can use a combobox or listbox on the batch input form to select which Rating to give each batch.

 

[LarryE]

@Magger
Would you be interested in trying a template I developed using your requirements as a model for the design? I don't have every requirement but have enough to at least present as a preliminary model. Your requirements indicated each batch having multiple samples and having multiple tests. I can attach the model template if you are still interested a possible solution.

 

[Magger]

thankyou all for your contribution. I'm still reviewing all your comments and will get back to this thread, just swamped at work atm.

regarding test results. I wanted them in groups, as 1 test even has multiple "observations" that needs to be recorded. ie imagine you had a sample of oranges. The test might be called "quality assessment" or "shelf life" and within that test a number of observations are discovered and recorded. Over the weekend I tried @GaP42 suggestion that each observation was test, but I got cornered when I had test groups associated with a client. It meant I needed to enter tests in 80 times if I had 80 clients. So need to rethink a bit here.

regarding user signoff. I'd like this at the test group level. ie not at batch or sample, but at test group. ie "quality assessment" which has multiple observations = signoff.

@Magger
Would you be interested in trying a template I developed using your requirements as a model for the design? I don't have every requirement but have enough to at least present as a preliminary model. Your requirements indicated each batch having multiple samples and having multiple tests. I can attach the model template if you are still interested a possible solution.

@LarryE that is nice gesture. If you don't mind I'd love to have a look. thankyou.

 

[GaP42]

regarding test results. I wanted them in groups, as 1 test even has multiple "observations" that needs to be recorded. ie imagine you had a sample of oranges. The test might be called "quality assessment" or "shelf life" and within that test a number of observations are discovered and recorded. Over the weekend I tried @GaP42 suggestion that each observation was test, but I got cornered when I had test groups associated with a client. It meant I needed to enter tests in 80 times if I had 80 clients. So need to rethink a bit here.

This is where you need to use terms very carefully:
Trying to tease out TestGroup, TestType and TestResult.
From the above: 1 test has multiple "observations" appears to be somewhat ambiguous:

• each observation is a TestResult, the test result must be the outcome of applying a specific TestType (eg weighing the sample, drying and re-weighing the sample, chemical extraction and assay of protein, sugar, fats, Na etc)
• A TestType may belong to one or more TestGroups: eg Quality Assessment *
• When a Batch is received, the type of Test/ Test Group is specified: eg Batch A is to be subject to a Quality Assessment. Shelf Life Assessment may be another Test Group that is needed for Batch A.
• As a TestGroup is a collection of TestTypes then when a record is made of the batch, the SampleTest list is generated from the collection and any single test types identified in the batch spec. which generates the list of all tests to be applied.
  • TestGroup "Quality Assessment" involves (say) a number of TestTypes:
    eg SampleWeight, DryWeight, Moisture%, ProteinWt, TotalSugarsWt, TotalFatsWt
• When a sample of the batch is taken the employee then determines from the list of SampleTests associated to the Batch from which the sample was taken, the test to be performed. Conducting the Test allows a record of the observation to be made against that sample. Some tests require a single observation while others may require a series of recorded observations (results) eg the Shelf Life TestGroup (say) requires involves assessing/recording viability for the sample when stored at 0C and for another sample at Room Temp at Intervals of 7, 14, 28, 56... days (Maccas last forever). So the time series type of test for shelf life does require a dedicated TestResult table to hold these multiple results for a specific TestType which is specified in a SampleTest.

* Indicates that the relationship between TestGroup and Test Type is many to many - which would require introduction of another table TestGroupMembers

Sign off by the Employee: (I imagine this is where the employee has reviewed and certifies the results of the SampleTests they have conducted against the Sample they were assigned). In that process I would imagine an ability to view the TestTypes and TestGroups requested for the Batch. When signing off against a TestGroup the list of TestTypes specified by the Group would be identified in SampleTest, allowing all tests in that group to be signed-off..

Note: it may also be a matter of practice that for a Batch the TestType/Group requested may involve taking multiple samples and the same or different employees repeating the same tests for a different sample from the same batch and the report needing to compile/average the results. The ER appears to cope with this.

It meant I needed to enter tests in 80 times if I had 80 clients. So need to rethink a bit here.

So using the TestGroup, the associated tests can be added in one step to the SampleTests. Each test however requires at least one TestResult to be recorded.