I am trying to design a relational database for breeding mice in a laboratory, but I am new to access, so I am unsure what the best approach may be. The database will be used primarily for two things: census and ordering mice.
The census I assume is achieved through queries. I would like to be able to generate a report containing: total number of cages, number of cages for each room, number of cages for each strain, number of mating pairs for each strain, number of offspring for each strain, number of female and male offspring for each strain, and number of mice with a certain genotype for each strain.
The database will also be used by researchers who want to order mice. This I assume will be accomplished by a form and maybe also a query (Ideally they should be able to filter the mice based on strain, genotype, date of birth, and available mice and then select the specific mice which they would like to order). Then there should be some way of notifying the assistants in back working with the mice that a new order has come in and they can sign those mice out (this I am not sure how to do).
So, back to the actual design of the database. With these uses in mind, I really appreciate some input on what tables I should have, what fields they should contain, and how they will relate to each other.
Although my knowledge of access is limited, my thoughts for setting this up so far are as follows:
There will be five main tables: offspring, mating pairs, female mates, male mates, and cages. I am not sure if I should split the offspring into separate tables based on strain or just have the strain in a field in the mating pairs table.
The offspring table will have these fields: MouseID, CageID, ParentsID, Mouse Number, Date of Birth, Sex, Genotype, User, Date Sent/Retired. The CageID field will link to the unique CageID in the cages table and the ParentID will link to the unique ParentID in the mating pairs table. The mouse number is the number 1-5 given to the mouse to distinguish it from the rest of the mice in the same cage.
The mating pairs table will have these fields: ParentsID, CageID, FemaleID, MaleID, Strain, Suffix, Date Mated. The FemaleID will link to the unique MouseID in the female mates table and the MaleID will link to the unique MouseID in the male mates table. The CageID field will link to the unique CageID in the cages table.
The female and male mates tables will have these fields: MouseID, CageID, ParentsID, Date of Birth, Genotype, Date Retired. The CageID will link to the unique CageID in the cages table. The ParentID will link to the unique ParentID in the mating pairs table.
The cages table will have these fields: CageID, Cage Number, Room. I give a separate Cage Number because the cages are numbered sequentially on their paper tag for each strain.
Am I on the right track? Should I only have one table containing every mouse in the lab instead of separating them into offspring, male mates and female mates? Will this design allow for the forms and queries that I want? Also, I want people working in different rooms in the back to be able to input new mice at the same time, which leads me to the conclusion that these tables should be separated out either into different strains or different rooms. If this is the case, a few of the fields would have to change or would no longer be needed I think.
Any help, suggestions, or references are greatly appreciated, thank you!