Hi Everyone,
I have attached a copy of my database to aid in visualization of my brain wall I ran into. I am working on a chemical database to house chemicals throughout our lab. The easiest way to see what I am attempting to do is by opening the database, and clicking on relationships, as I have them laid out according to my thought process. Generalizing, a chemical needs to have multiple ChemicalID's (an autonumber PK). What I have set-up is as such:
tblChemicalInfo---to house a chemical's information from the bottle. PK is Chemical Name
tblIntermediate---Only has two fields: ChemicalID (autonumbered PK) and Chemical Name (FK for tblChemicalInfo), and acting as a "middle man" between tblChemicalInfo and the "subset" tables below.
Then my "subset" tables that house the information broken down into relevant "sections", with ChemicalID as the FK:
tblLocation
tblDateInfo---when a chemical was received, expires, disposed, etc.
tblInventory---to keep track of annual inventories
tblVirologyInfo---info that is specific only to one section of the lab, not necessary for other sections to document.
My idea is that users would log-in a chemical by going to a form that would have the "Chemical Name" field from tblChemicalInfo for them to select a pre-entered chemical (or enter a new one), the chemical information would automatically fill in on the log-in form, and then the user can fill in the relevant "subset" information. When the user is done, this would create a new ChemicalID for a chemical in the tblIntermediate. That way I can search for a Chemical (Acetone) from tblChemicalInfo, and see all the Acetones throughout the building (since each would have its own ChemicalID)
Have I overthought breaking my information down into those "subset" tables? Have I overcomplicated this thing? Any thoughts would be greatly appreciated!!!! Much Thanks!
David