Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36

    Question Relational database for mice breeding

    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!

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would suggest having all mice in 1 table, and then have another table that relates the mice whether they are mates, offspring etc.


    tblMice
    -pkMiceID primary key, autonumber
    -dteBirth (date of birth)
    -Gender

    tblMiceRelations
    -pkMiceRelateID primary key, autonumber
    -fkPMiceID foreign key to tblMice
    -fkSMiceID foreign key to tblMice
    -fkRelationshipID foreign key tblRelationships describes the relationship that fkSMiceID has with fkPMiceID.

    tblRelationships (records that delineate the type of possible mice relationships: mate, offspring etc.)
    -pkRelationshipID primary key, autonumber
    -txtRelationship

    Pardon my ignorance, but by strain do you mean species? Do only certain genotypes relate to a particular strain? I need to understand if there is a relationship between strain and genotype to be able to structure it properly.

    Assuming that cages can be moved from room to room over time, I would recommend having a table for rooms and a table for cages and then a table that relates the two.

    tblRooms
    -pkRoomID primary key, autonumber
    -txtRoomNo

    tblCages
    -pkCageID primary key, autonumber
    -txtCageNo

    tblRoomCages
    -pkRoomCageID primary key, autonumber
    -fkRoomID foreign key to tblRooms
    -fkCageID foreign key to tblCages
    -dteRoomCageEffective (effective date of when the cage was in the room)


    Of course, you will need to relate the mice to the cage in which they are placed

    tblRoomCageMice
    -pkRoomCageMiceID primary key, autonumber
    -fkRoomCageID foreign key to tblRoomCages
    -fkMiceID foreign key to tblMice
    -dteRoomCageMiceEffective

    The above are just some initial suggestions, I would guess that flushing out the table structure will take some more time & thought

  3. #3
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    Thank you, jzwp11, for taking the time to help me out. I think your design makes a lot more sense to me than the structure I had come up with. By strain I do not necessarily mean species, the only difference in species would be that there is one room of rats. But the data input for the rats is exactly the same as the mice, so I didn't necessarily think they needed to be separated. I am pretty sure there are only 3 possible genotypes: -/-, +/-, and +/+. And this is true for every strain. The different strains are just different genes added or knocked out. And yes, the cages will move between rooms from time to time, so having a table for rooms and a table for cages and then a table that relates the two is probably a good idea. I hope this has clarified some things. Thank you again for helping.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK, then if I understand correctly, you would have a table of strains and a table that contains the 3 genotypes

    tblStrains
    -pkStrainID primary key, autonumber
    -txtStrain

    tblGenotypes
    -pkGenoID primary key, autonumber
    -txtGenotype

    Now, since there can be 3 genotypes per strain, we need to relate them

    tblStrainGenotypes
    -pkStrainGenoID primary key, autonumber
    -fkStrainID foreign key to tblStrains
    -fkGenoID foreign key to tblGenotypes


    Now you can modify tblMice to include the strain/genotype

    tblMice
    -pkMiceID primary key, autonumber
    -dteBirth (date of birth)
    -Gender
    -fkStrainGenoID foreign key to tblStrainGenotypes

    In terms of ordering, you would have a typical order/order details type setup

    I don't know if you deal with vendors that have an item number system for the mice that they sell, but if so, you can have a table of items

    tblItems
    -pkItemID primary key, autonumber
    -txtItemNo
    -fkStrainGenoID foreign key to tblStrainGenotypes
    -Gender
    -UnitPrice
    -fkVendorID foreign key to tblVendors

    The fkStrainGenoID and gender essentially make up the description of the "item" (i.e. mouse) that the vendor offers for sale


    tblOrders
    -pkOrderID primary key, autonumber
    -txtOrderNo
    -dteOrder (order date)
    -fkVendorID foreign key to tblVendors

    tblOrderDetails
    -pkOrderDetailID primary key, autonumber
    -fkOrderID foreign key to tblOrders
    -fkItemID foreign key to tblItems
    -longQTY (quantity of the mouse type you are buying)
    -currUnitPrice

    tblVendors
    -pkVendorID primary key, autonumber
    -txtVendorName

  5. #5
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    I just spoke with the lab coordinator, and there are actually more than just those three possibilities for the genotypes, but if I understand correctly, that shouldn't require any change to the structure you just suggested. The one thing that might call for a little adjustment is that some of the new strains will have two genes knocked out and therefore will have two genotypes listed. However, could you just list all the double genotype possibilities as separate genotypes and keep this same structure? Thanks again for your help.

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I just spoke with the lab coordinator, and there are actually more than just those three possibilities for the genotypes, but if I understand correctly, that shouldn't require any change to the structure you just suggested.
    You are correct, there would be no structure change required; you would just add the additional genotypes as records.

    The one thing that might call for a little adjustment is that some of the new strains will have two genes knocked out and therefore will have two genotypes listed. However, could you just list all the double genotype possibilities as separate genotypes and keep this same structure?
    With this table as I posted yesterday, you can associate any number of genotypes to a strain without any change in structure:

    tblStrainGenotypes
    -pkStrainGenoID primary key, autonumber
    -fkStrainID foreign key to tblStrains
    -fkGenoID foreign key to tblGenotypes

  7. #7
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    That is what I thought, thanks. Sorry for the confusion, but what are the PMiceID and SMiceID fields in tblMice?

  8. #8
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    Again, sorry for having to hold my hand through all this, this is my first time really using access let alone designing a database. Here is how I have set up the relationships, is this correct?

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Both fields relate back to the primary key field of tblMice. Since you cannot have two fields with the same name in the table, I just threw in a P and an S in order to distinguish them. I generally use the P and S to signify Primary and Secondary, but that is just for my own benefit; they really have no significance.

    As to the relationship diagram, the join from tblMice to the PMice and SMice should be one-to-many. Remove the two joins you have currently. Add tblMice a second time (Access will add a number to the end of the table name, but it is still the same table), and then create one join from each table to tblMiceRelations (one goes to PMiceID and the other to SMiceID)

  10. #10
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    Ok, so I see how this structure accounts for which mice are mating pairs, but how does it connect which mice are the offspring of these mating pairs? Also, should the MiceRelateID be connected to anything or is that just going to be used for forms and queries?

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The MiceRelateID is not related to anything yet. Are only mating pairs put in a cage or can a cage contain both mating pairs and other mice that do not have mates? If a cage only contains mating pairs, then you can use MiceRelateID in RoomCageMice in place of MiceID.

    Ok, so I see how this structure accounts for which mice are mating pairs, but how does it connect which mice are the offspring of these mating pairs?
    This part gets a little tricky because an offspring of a mating pair can in the future mate with a mouse and have their own offspring.

    Let me illustrate with some example records

    Let's assume that we have 5 mice in tblMice whose primary key values are 1 through 5


    Let's say in the table Relationships we have the following

    RelationshipID|Relationship
    1|Mate
    2|Offspring

    In MiceRelations we could have the following:

    MiceRelateID|PMiceID|SMiceID|RelationshipID
    1|1|2|1
    2|1|3|2
    3|2|3|2
    4|3|4|1

    The first record says that mouse 1 and 2 are mates.
    The second record says that mouse 3 is the offspring of mouse 1 (this is where the Primary/Secondary delineation helps me out since an offspring is secondary to the parent)
    The third record says the mouse 3 is the offspring of mouse 2
    (i.e. the second and third records indicate that mouse 3 is the offspring of the mating pair mouse 1 & mouse 2)
    The fourth record says that mouse 3 is the mate of mouse 4

    Technically speaking you don't even need record 1 (unless of course the mating pair never produces offspring & you want to know that info), since you can determine which mice were the parents of mouse 3 by records 2 and 3.

  12. #12
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by jzwp11 View Post
    Are only mating pairs put in a cage or can a cage contain both mating pairs and other mice that do not have mates?
    The cages usually contain either only a mating pair or only offspring. The only time a cage will contain both is when the female mate gives birth and the offspring will remain in that cage until they are weaned (separated by male and female and put into different cages accordingly) 21 days later. These offspring are not input into the database until after they are weaned, so for the sake of simplicity, let's just say both mating pairs and offspring will never be in the same cage together.

    Your explanation of how offspring are related was great, I understand now, thank you.

    Also, one more piece of data needs to be accounted for, and that is the date the mating pair was mated. Which table should this go into?

  13. #13
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    These offspring are not input into the database until after they are weaned, so for the sake of simplicity, let's just say both mating pairs and offspring will never be in the same cage together.
    Based on this, then you should make the change I suggested earlier

    Also, one more piece of data needs to be accounted for, and that is the date the mating pair was mated. Which table should this go into?
    Since the mating is defined in MiceRelations, I guess that would be the best place to put the date. The field would be left blank when you are just doing the parent/offspring. Technically you could use that date field to record the DOB (when you define the parent/offspring), but you said that you also buy mice so the better place is in the mice table.

  14. #14
    shiphtfour is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Dec 2010
    Posts
    36
    Sorry, just a few more things that I forgot need to be included. Each cage can contain up to 5 mice, so the mice are numbered and given ear punches accordingly so that you can tell which mouse is which. So this mouse number (1-5) I am guessing should just go in tblMouse?

    Also, the mating pairs for each strain are numbered on the paper tags attached to each cage, so this number could go in tblMiceRelations?

    I am wondering if relating tblRoomCageMice with MiceRelateID is the right thing to do since although this works fine for mating pairs, you could essentially only put one offspring in a cage? Or am I wrong?

    Also, sometimes two different strains are mated together and distinguished as a new strain, which will be accounted for in all the offspring, but how would I account for the fact that this mating pair is part of this new strain too? As in if I run a query to find out how many mating pairs there are for this new strain, I think a separate field in tblMiceRelations that lists what strain each mating pair is part of needs to be present.

  15. #15
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Each cage can contain up to 5 mice, so the mice are numbered and given ear punches accordingly so that you can tell which mouse is which. So this mouse number (1-5) I am guessing should just go in tblMouse?
    If the number given to each mouse will only ever be used for that mouse then it should go in tblMice/tblMouse; however if you assign the numbers based on the mice in a specific cage then no. For example, if you number the mice 1 through 5 in cage A and 1 through 5 in cage B, then putting the number in tblMice/tblMouse would not be appropriate. It would be better to have the field in the table where the mouse is assigned to a specific cage (RoomCageMice table).

    Also, the mating pairs for each strain are numbered on the paper tags attached to each cage, so this number could go in tblMiceRelations?
    Is this yet a different number than the one you discuss above? If the number relates to the mating pair, then it sounds like it should be in tblMiceRelations.

    I am wondering if relating tblRoomCageMice with MiceRelateID is the right thing to do since although this works fine for mating pairs, you could essentially only put one offspring in a cage? Or am I wrong?
    This goes back to your earlier post, in that post you said:
    These offspring are not input into the database until after they are weaned
    If the offspring are not entered into the database until they are weaned (and moved to a separate cage), that implies that you are net yet tracking them. If that is indeed the case, then MiceRelateID would be more appropriate since it implies that you only worry about the mating pairs and not the offspring. However, if that is not the case and you want to track each individual mouse in the cage, then miceID would be appropriate.


    Also, sometimes two different strains are mated together and distinguished as a new strain, which will be accounted for in all the offspring, but how would I account for the fact that this mating pair is part of this new strain too? As in if I run a query to find out how many mating pairs there are for this new strain, I think a separate field in tblMiceRelations that lists what strain each mating pair is part of needs to be present.
    This does not make sense to me. The two mates would not change their own unique genetic makeup (i.e. strain) by mating, it would only be the offspring that is the new strain; however since the two strains are used to make a third, there is a way to define this relationship (much like the relationship between the mice themselves)

    Since the new strain is still a strain, it would still be entered into the Strains table. To define the relationship between the two original strains and the new resulting strain, you need a junction table.

    tblStrainRelations
    -pkStrainRelateID primary key, autonumber
    -fkPStrainID foreign key to Strains table
    -fkSStrainID foreign key to Strains table

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-03-2010, 10:41 AM
  2. Relational tables - again
    By BarbT in forum Access
    Replies: 6
    Last Post: 10-31-2010, 10:28 AM
  3. Relational tables help
    By BarbT in forum Access
    Replies: 5
    Last Post: 10-21-2010, 09:03 AM
  4. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 PM
  5. Relating 2 Tables - Relational Problem
    By jeng in forum Database Design
    Replies: 5
    Last Post: 04-01-2010, 09:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums