Help designing tables (1 Viewer)

[captaman]

I'm new to Access/database design and I'm having trouble figuring out how to best configure my tables.

I'm designing a database to keep track of quality control testing of a chemical's stability (i.e. the chemical is kept long term at 2 different temperatures). We are storing several different lots of this chemical at those 2 temperatures. Every 3 months all the lots at both temperatures are pulled and tested using several assays.
So I have multiple lots of the chemical all undergoing multiple tests/assays every 3 months.

I want to design a database that can allow personnel to enter in new test results as they come in. I will also create reports to display the data but that's down the line.

Here's what I have so far but I'm sure it has room for improvement:

tblTests:
TestID (PK, autonumber)
TestName (text) (lists the name of the different tests used)
Units (text) (i.e. the units of that particular test's result)

tblLots:
LotID (PK, autonumber)
LotName (text)
TempID (FK from tblTemps)

tblTemps:
TempID (PK, autonumber)
Temp (holds the values of the 2 storage temperatures)

tblResults:
ResultID (PK, autonumber)
TestID (FK from tblTests)
LotID (FK from tblLots)
TestResult (number) (i.e. the value of the test result)
TestTime (number) (months in storage)

Does anyone have any recommendations for how I could best organize my tables?

Thanks!

 

[rzw0wr]

I would suggest you Google normalization.
Read a few of the articles and get an idea of what is.
Most developers do not go past normal 3. I don't for most databases.

Then re-post your question.
Your questions will be better stated and the answers will not be near as confusing.

Dale

EDIT:
Also when starting a database, start a the end. Decide what you want out of the database so you will know what you need to store in it.
Decide what you need on the reports and/or exports.
This tells you what tables you need and what data to store in them.
The middle or the developing part will then be a little easier.

 

[captaman]

Thanks, I've done some reading and I think that I was able to design a somewhat normalized database (at least as much as I need for my small database).

 

[Prayder]

I use Access 2007 and purchased a book called Access 2007 bible. It has helped a great deal. It also does well at explaining in depth normalization of tables and the database as a whole.

 

[catalin.petrut]

Depending on how complex you want your app, your tables can suffer modifications. Suggestions:
a. the lot table should not have temp id. You should create another table, related on lot id, where you mention the temp and also the start and the end of that 3 months test.
b. in test table should have some fields to store the normal value and the lower and upper admitted value.