Confused about setting up tables and relationships

[Vetgeorge]

I am trying to set up a staining quality db to monitor stains we prepare in-house to stain cell biopsy samples. Basically, we make a stain solution, that solution is composed of "ingredients." Each ingredient has a manufacturer lot number. When we make a batch, we assign our batch an in-house lot number as well as preparation date. Then we stain a control sample and evaluate the staining quality.
The ingredients always remain the same, but their lot numbers may change. Each time we make a new batch, we assign a new lot number to it.

Below is a diagram/example. I need assistance in figuring out how many tables to create and setting up the relationships. Thank you for any help you may offer.

	Date Prepared	Lot Number	Quality Evaluation
Hematoxylin Stain	1/1/2017	1	Acceptable
Component 1	Hematoxylin dye	A123
Component 2	Aluminum potassium sulphate	B234
Component 3	Sodium iodate	C345
Hematoxylin Stain	2/1/2017	2	Acceptable
Component 1	Hematoxylin dye	A123
Component 2	Aluminum potassium sulphate	B234
Component 3	Sodium iodate	C345
Hematoxylin Stain	2/15/2017	3	Unacceptable
Component 1	Hematoxylin dye	A890
Component 2	Aluminum potassium sulphate	B555
Component 3	Sodium iodate	C710

[kd2017]

Can an ingredient from a single mfr lot be used in multiple solutions?

[orange]

Here is a link to articles/tutorials on database design and planning.

I recommend you try (work through) 1 or 2 of the tutorials identified in the practical experience area. Then, write a description of your "requirements" and use the process from the tutorial(s), and create your draft design. You will learn the process if you do the work. It is a definitely a learning by experience approach and using pencil and paper is recommended.

Don't be too quick to jump into physical Access.

Good luck.

[kd2017]

Here is a model I'm imagining from your description, it has more information than you asked for and maybe more than you need... But as orange said you and your organization really need to work together to figure out your requirements. I'm not telling you this is how it's done or supposed to be done.



OR if the solutions are truly always the same then something like this might work:

[Vetgeorge]

Thank you orange and kd for your help!
I did have the information drawn out on paper and thats how my diagram came about. I just couldn't figure out what element(s) needed to be joined together with relationships / foreign keys. The other problem which kd brought up is that the chemical components can be used in making other stain solutions. Which added to my confusion.


kd you did include inormation which i knew was also going to be in the database, such as the preparer's ID, the reviewer's ID, extra things i left out of my original question just to simplify things. You're right, there are going to be other items that will be added. This is a great start. I'm going to try building it and see how far I can get.