Designing a small lab database

[Johanb26]

hi guys

I am a 2nd year information system student and my brother owns a small water testing lab. at the moement they are using excel to capture the results of the tests, but the excel file they are using is over 16mb now just with text and its full of redundancy issues.

I want to design a small database for him that will assist in keeping sample results and also minimize data redundancy.

description of activities are:

a waterplant can belong to only one client, but a client can be the foreman over many plants. each plant can have 3 different type of tests done on the plant. the tests can be done more than once a year each time providing different results. the results ofpast tests need to be remembered as well.

the idea i have for the design is:

Table1: customer
id: (PK)
customer details

table2: waterplant
id: (pk)
waterplant serial : (pk)
customer_id : (fk)

[Johanb26]

sorry i am on my tab and struggling to type.

will the above design be sufficient or do i need to add another table called waterplant_results.

the waterplant entity will contain details about the plant and its test results.

[orange]

A few questions:

Your description was about Clients, but your table structure shows Customer. Are all Customers Clients and all Clients Customers or is there some significance to the different names you are using?
You said "a waterplant can belong to one client" - it was important to mention that in your description, but I don't see it in your tables?
You mentioned 3 different types of tests, but that is not shown in your tables???
Tests can be done at varying frequency at each plant, and that does not appear in your tables??
I don't see tests, test results or test results history in your set up?

Have you looked at what exactly is stored in the current excel spreadsheet? Have you set up your tables based on the info (subjects) in the excel spreadsheet?
Is your brother's business a money making operation? Do you have a plan to develop and test the database? And train whoever must be trained? Have you considered if and how you would convert the existing data(excel) into Access?

These are just a few of the questions to consider when developing a database.

Good luck with your project.

[Johanb26]

hi Orange,

sorry it was my mistake the client and customer is the same person.

its not a money making company at the moment and converting their existing excel information into a db is for me to learn and just as a side project while university is closed at the moment.

I have not really considered the other entities yet as I only thought about doing it today and wanted to find out if 2 entitoes will work, but you have given me a lot to think about.

I also did not indicate everything on purpose as doing so is difficult with the tab.

A few questions:

Your description was about Clients, but your table structure shows Customer. Are all Customers Clients and all Clients Customers or is there some significance to the different names you are using?
You said "a waterplant can belong to one client" - it was important to mention that in your description, but I don't see it in your tables?
You mentioned 3 different types of tests, but that is not shown in your tables???
Tests can be done at varying frequency at each plant, and that does not appear in your tables??
I don't see tests, test results or test results history in your set up?

Have you looked at what exactly is stored in the current excel spreadsheet? Have you set up your tables based on the info (subjects) in the excel spreadsheet?
Is your brother's business a money making operation? Do you have a plan to develop and test the database? And train whoever must be trained? Have you considered if and how you would convert the existing data(excel) into Access?

These are just a few of the questions to consider when developing a database.

Good luck with your project.

[Johanb26]

sorry for the double post.

I want to develop and test the database during my break, but just hoping to get some guidance before starting with the actual project. I will also be the one using the db to record info and if needed I will later teach somebody else.

[orange]

It seems to me that you will need tables for the following (off the top of my head while typing)

WaterPlants

Customers

Tests

TestHistory

You will need junction tables to deal with many to many relationships.

eg

Many WaterPlants have manyTests, so a junction table WaterPlantTest could contain

WaterPlantTestId autonumber PK
WaterPlantId
TestId Green fields indicate unique composite index
TestDate
TestResult

Good luck with your project.

[Johanb26]

Hi Orange,

Thanks for the input. When I get home I wilk start designing the db in visio is it possible that I can then upload here for you to comment/criticize on ?

[orange]

see
http://www.rogersaccesslibrary.com/T...lationship.zip
http://www.rogersaccesslibrary.com/f..._Databases.zip