Table relationships and structure. (1 Viewer)

Hello,

First of all, sorry if Im not clear enough, english isnt my native.
Im quite a beginner in MS Access and databases in general, so I need some help in designing tables and their relationships.
Im trying to create database for product testing results, where each product can have many different tests, and each test have some result. For each product I also need to define a lot of parameters like material used, quality demands, technological and geometrical parameters for each device the product is made with. So in my opinion, I need tables like ProductInfo, ProductTechnPar, ProductGeomPar, ProductQuality, TestPerformed, Test1Result, Test2Result and so on. How do i need to relate these tables, so i can create a form to input results? Since each product has unique batch number, maybe I need batch number field in every table? Im asking all of this, because I have already made a database for this, but I had all this information in 1 table, which is wrong, so I want to create it again and correctly.
Sorry for the long text.. Hope for some advice

This depends on the data. Things that apply to most products should be part of the ProductInfo table e.g. Product Weight.

From your description it seems that each product can have arbitrary number of parameters and each parameter can have an arbitrary number of tests. So you would need to create a number a Parameter table that has keys of Product and Parameter and a Test table that has keys of Product, Parameter and Test Number.

There is a trade-off between complexity and flexibility. If you have only a few parameters and they don't change, you can just put them in the ProductInfo table. Tables do not have to be in the normal form. If only a few tests are performed, you could make the test results fields of the Test table.

If you don't understand the issues of database design, you could start with the Wikipedia article https://en.wikipedia.org/wiki/Database_normalization

tr91,

Work through this tutorial from RogersAccessLibrary. It has a solution, and what you learn can be used with any database. You have to work through it to get the benefits.
There is more info on Normalization here.

Good luck and welcome to the forum.

RogerCooper, jdraw,

Much appreciation for your help. I went trough all the information you gave me, and I have an idea now how to structure the tables and treir relationships.

But Im still confused how to design a SINGLE FORM for all these tables. With all the data in one table it was easy, but since now I have multiple tables, how can I be sure that every record, every test result will be appropriately assign to every product. Im confused because sometimes I would need to leave some table record empty, or add new table for some new test for example.. thanks in advance!

tr91,

Your English is more than adequate. I suspect your issue is with the complexity of the project/task. Your tables and relationships will all be related to the details of your business rules and related processes. There are a number of free data models at Barry Williams' site. I'm not suggesting there is an answer there, but if you review a few models, you'll see how tables and relationships are depicted in diagrams.

I think you might want to start with a smaller piece of the problem and build a data model and database. Get some experience with something a little less complex; learn and experience some data base concepts.

I would also suggest you start by writing an overview description of the business -- a day in the business. Gradually add some detail. In order to help you, readers need more info about your business processes and business rules. And, for you to design the database, you'll need more details (specifications).

Do you know what the outputs of this database should be? Can you mock them up in a form or report? Once you know what must come out, you know what must be input or calculated.

Show us the data model you have so far --jpg in a zip file. You can post attachments if you zip the file(even if you don't have 10 posts.)

jdraw,

Again, thank you for your time.

Ok, Ill be a little more specific. We are plastic pipe manufacture company. We produce plastic pipes from many materials for many purposes, like cable protection, drainage, water, gas and so on. Various tests are performed to every product, like impact resistance, oven test, ring flexibility and so on accoridng to product standard. The idea is to create Access database for storing, analysing and reporting the product information and the testing results. Before all of this was done in complex Excel file, therefore it was really time consuming. Now I would like to create a single form to input all the product information and the result for the tests which were performed for that product. Lets say, there are fields for the product information, then there is dropdown with all the tests and when I select impact resistance test, it gives me the fields to input results for that test. And after that with all the product information data staying the same, I should be able to select another test and input results for that one.
When the results are entered, I should be able to select the product by its unique batch number with all the test results for that product, so I can create a test results report for it.
I attached a picture of table relationships. In many of the tables there are much mores fields, but I hope you can get the idea am I on the right track or not, and most important how to design a form. If you need, I can give more specific information. Thanks in advance!

The tutorial and data model links I gave are the best I can offer at the moment. I looked at your relationships and do not understand the extruder1 and extruder2 tables. Looks redundant.

You have working knowledge of the excel application, its faults, its good features and you could map out some structure and test it (with some test data) to ensure it works. You can do this with pencil and paper.
The data base structure is key. Get that sorted and then work on inputs/interface.

There are people on a couple of forums who have experience in laboratory testing with multiple samples, multiple tests etc.
I don't remember names at this time, but I have seen such posts. They could be helpful if you can find them.

Ok, thanks.

I know its probably too much to ask, but maybe you could provide me with simple database with 3 tables (each 2 or 3 fields) ProductTBL, TestPerformedTBL and TestResultTBL properly related and a simple form to input data in all 3 tables. I tried it myself, but always got confused on the part where i try to input data..
It would mean a world to me, to have a correct example to learn from..

Thanks anyway

tr91,

I've been out of town most of the day. Here are a few sample tables with relationships, but , since I don't know your business in any detail, these are mostly "best guesses" .

In overview your company builds many Products. You also conduct Tests on those products to ensure they meet quality standards. In addition you record results of the various tests and can recall tests and results for a variety of analyses.

So the first things that jump out as "things of interest" are Products, Test and Results.

This would lead to 4 tables (to start)
The rules (as I'm making them up)
There are many Products
There are many Tests
Not all Tests are performed on all Products
Some Products are subjected to many Tests
Some Tests apply to many Products
Employees/Testers conduct the test(s)
A TestOnProduct may have 1 or many Results
Some Results may have additional (important Notes/Comments)

You'll want to identify all of the Products that could be tested.
You'll probably want details of all of the Tests you perform.
So you want to record info about this Test On This Product and record the Results.

The tblTestOnProduct is a junction table. I have given it a separate PK TestOnProdID.
To ensure uniqueness you should create a unique composite index on the combination
ProdId, TestID, TesterID and (if you could run several tests on this Product on the same day) TestDateTime.

A junction table is a means of resolving a Many to Many relationship. You can not have a direct Many to many relationship in a relational database. You make two(2) one to Many relationships -- between each of the tables involved in the Many to Many and the junction table.
Here you have many Products and many Tests -- a Product could undergo several Tests and a Test could be performed on several Products.

I am attaching an entity relationship diagram with some sample attributes. I hope it's helpful to you.

Good luck with your project.

Thank you, you are being very helpful. Your guesses are very good, its pretty much how it should work and it makes sense to me.
Just a few thing though.. As there are various tests and each test have different parameters, there should be result table for every test, and all these tables should be related to TestOnProduct, right?
And about form.. I should make a form for every table and then drop all of them in main form as subforms? And how can I provide that the primary key values record its values in foreign keys fields? :/

Create some simple test data. Test the data against the data model. See if the model supports the rules. If anything does not work Investigate and resolve the cause.
Is it bad data?
Is it a problem with the model?
is it a confusing business rule?
is there a hidden/undefined entity involved?

You can do much of this with pencil and paper. Create some test data records. Identify what you think should happen with each record. Then , with pencil and paper, create some columns to represent the tables; put the associated data value in that column, using the business rules, can you get the output expected from the model. If yse, go to the next record. If no, then reconcile the issue-Solve the WHY is this happening? Make adjustments, then redo all the tests until it works.

Do not get too involved with the form at this point.

Ok, I took your advice, and drew the tables on paper, then step by step entered the data as I need. Finally I saw the bigger picture how the data will be related using the keys. But.. everything would be nice and easy if one column only with values positive or negative would ne enough. To be clear, one test results give information like force applied, deformation, second test have results like number of blows, number of failed pieces and third test results could have fields like water temperature, pressure, time... It means, if there are 20 different tests with completely unrelated results, then I need 20 different tables to be able to save that kind of information. Or for that purpose there is this ResultImportantComments table? But than again, it would contain hundreds of columns.. seem like i just cant get rid of this Excel spreadsheets thinking.. :banghead:

seem like i just cant get rid of this Excel spreadsheets thinking

Click to expand...

Yes that is a common problem. Access is not an enhanced Excel, contrary to what you may think. These products are built upon different object models and are quite different.

You can not effectively build relational databases using "spreadsheet mode development".

You need to identify what you are trying to accomplish. Identify the things involved, and how they are related. What are the relevant attributes for each thing. It is a different mindset.
Quite often the advice for excel people when learning Access ---unlearn your Excel habits.

In my view you have an orientation suggesting this thing is extremely complex. And it very well may be. But start with something small, design it test it, make it work. Then go to something more involved. You didn't build your current excel solution on the first day.

As for this

I need 20 different tables to be able to save that kind of information. Or for that purpose there is this ResultImportantComments table? But than again, it would contain hundreds of columns..

Click to expand...

No. That isn't a solution. Again, start with a simple database; do a few tutorials; learn by doing.
Good luck.

Here's a revision to the original sample ERD.

I have added Samples of Products, Categories of Tests, Expected Results now part of the Test on Individual Sample and Observed Results (Actuals).

Thanks very much for your effort. I was away for a while, but I created structure like you suggested, but struggled to create data input form, just to test how it works. I tried to use form wizard, but no luck..
I attached empty database with the tables and relationships, based on your example, maybe you could give some hint how to input data?

I created structure like you suggested

Click to expand...

No the structure is not like the sample.

I suggest you go back to the tutorial I mentioned in post 3 and work through it.
Good luck.