Chemical Database Layout.

[dlab85]

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

[orange]

I suggest you review this tutorial to see the steps involved in taking some business facts, identifying potential tables, ensuring tables are normalized , then establishing relationships between tables all leading to a well structured data base.
It will also help with identifying Primary and Foreign keys.
http://www.rogersaccesslibrary.com/T...lationship.zip

[dlab85]

Hey Orange,
Thank you for the link. It did help me get a better grasp on my relationship setup, and he does provide some further links. My brain tends to glob up when I try and read multiple normalization and database setup articles, and end up in a temporary haze of confusion . I am still struggling with whether I have broken my tables down too far. The tblDateInfo I think could be in my intermediate table, but there is one section of the lab that needs to use the dates a little differently. I will keep experimenting with my setup and see what I come up with. Anyone else with suggestions, feel free to chime in as well. Thanks!

[orange]

After working through the tutorial, did you make any adjustments to your tables and/or relationships?
If so, please post a copy.

[dlab85]

I did make some changes to my relationships and tables. Attached is a copy of the database, with the relationships view being the most descriptive way to look at it. I made tblLocation and tblViroInfo a one-to-one relationship with tblChemicalID, because each Chemical under tblChemicalID should only have one matching record, since the information is more of a continuation on those records. And then tblInventory is a many-to-one relationship with tblChemicalID, being that multiple inventories could take place on one chemicalID. I think I am keeping with a one-to-many relationship with tblChemicalInfo and tblChemicalID. We will see how this pans out as I flush out details and make log in forms and what not.ChemManagement2013.accdb

[orange]

Do not use lookups at the Table field level.
see http://access.mvps.org/access/lookupfields.htm

I don't think tblChemicalId should be a table name. Perhaps tblChemical and ChemicalId would be a field, even the Primary key.

There are some free inventory data models at http://www.databaseanswers.org/data_...all_models.htm
some of which may help your design. None of these data models is necessarily customized to your needs. They are meant to give you some ideas for building your own model.

Good luck

[dlab85]

Orange,
Where did I have a lookup at the table field level?
ChemicalID is the primary key in the tblChemicalID. Any particular reason as to why it should be renamed tblChemical?
I feel fairly confident in the current setup that I had attached, but if there is something particular you foresee as a problem, specific input would be great! Otherwise, I feel that the data is going to flow as needed and is a great improvement over the previous layout of information I had. Thanks for the links and thoughts!
David

[orange]

I've attached the first TableField lookup I found.

If you are confident, then build some test data. Both good and bad data.
Take a document and number your tests. Identify what you are testing and what result you expect. Then add a column to record what you actually get during your test.
If everything tests well - no differences between expecteds and actuals - great. However, if there are any differences, identify what the problems is and retest. Reconcile every difference.

Good luck

[dlab85]

Orange,
What is the replacement for using a lookup field at the table level? I have designed two other databases where I use initials, test type, etc as lookup fields to allow users a drop down box to select their values from. I haven't seen a lot of info on not using a combo box lookup for a table field, and have found that it easily allows the addition of choices into the combo box with ease. That link you provided on the Evils of Lookup Fields at the table level was not useful in providing the "best" alternative. Thanks!

[orange]

The alternative is to have a separate table along the following format

tblStatusLkup
StatusId PK
StatusDescription text

with data such as

01 NotYetStarted
02 Initiated
03 InProgress
04 OnHold
05 Completed

and you link tables such as

tblProjectStatus
ProjectId
StatusId ---------------tblStatusLkup.StatusId
StatusDate

If you need a combo, you make the RowSource = tblStatusLkup
StatusId is bound, with length 0", so the combo shows StatusDescription and stores StatusId when/where needed.

Have you tried searching the forum for Lookups at the Table field level??

[dlab85]

Hey Orange,
Thanks for the clarification, I have only allotted a small portion of my brain to remembering all the rules for database design (seeing as this is not my area of expertise, I am a scientist by nature, computer geek by necessity ), so it is helpful when these scenarios are pointed out to me. Back to some more redesign!

[dlab85]

Orange,
One last thing to pick your brain about? So in an old version of this database, I have my chemical names as text names. But in the new version that I am creating, I have the text names linked to a NameID autonumber. Is there an easy way to convert all of the Text names into their NameID if I were to import the data from an excel spreadsheet? Rather than me having to change every chemical name to the appropriate NameID in the excel spreadsheet prior to importing? Thanks

[orange]

I'm not following this

I have the text names linked to a NameID autonumber.

"Links" between tables are really between PK and FK.

Can you post a database showing what you are describing? Give specific details as necessary.

[dlab85]

I don't have the ability to zip my databases down to the appropriate size, but I attached two images showing the relationships and table contents. Below is the way I had my old database. All the information was basically maintained in one record, and as such the chemical name and company name are typed out in text format (i.e. Acetone, Thermo Fisher)

Then below is the new version layout.

The tblChemicalInfo houses the main information for a chemical, BUT the chemical name and company name are now the FK of NameID and CompanyID to the PK's in tblChemicalName and tblCompany (i.e. Acetone is stored as NameID 20, and Thermo Fisher is stored as CompanyID 36). If I want to transfer my data from the old database to the new, I need to replace all the Chemical Names with the appropriate NameID. I already put all the chemical names into tblChemicalName, so they have an assigned NameID, and the same goes for tblCompany. How is the best way of transferring the data into the new database without my having to manually assign the appropriate FK ID's (I was planning on using Excel as a intermediate step to allow for column rearrangement and what not)?

[orange]

When I look at you relationships I see that Location and Virology have a 1 to 1 relationship with tblChemicalID. I think that should be a 1 (tblChemicalId) to Many(Locations) but I don't know your data.

Typically if you have a lot of data to load into tables, and that data is not normalized. You create a number of queries to take the data and separate (select out) the fields you need to insert parts of the data into the respective tables.

Quite often people have some csv or text files they want to load into a structured database. Often they load the inital data into a Temporary table; then create queries to move the pieces they need from the temporary table to the proper table.