Ms-access – standard database layout for repetitive information

database-designms access

I was recently tasked with creating a database to store a modest amount of data. I am learning as I go, but my main hold is figuring out the best way to layout the data. The problem is that for each test, there are n number of identical objects being tested and data is recorded for each, but the test id, date, test operator, etc.. are the same for all n objects.

So I see two options. I could have one entry per test but then have data columns named "test_result_1, test_result_2, … test_result_n" for every result that we will be recording. This would lead to many columns for each test entry and would make searching for a test result difficult since you would have to search over n columns.

The other option is to have one entry per object, which would result in n entries having identical test ids, dates, test operators, and any other information that is specific to the test but not the objects. This would reduce the number of columns per entry and make searching for test results easier, but it would also increase the total amount of data stored and lead to more input required when done manually.

So is there any standard method for handling data of this type? It is a bit repetitive either way and I can see pros and cons to each side. I could see it being something that is considered on a case by case basis, but I figured I would ask if there is something I'm not taking into account that makes one option the standard.

Best Answer

You need two related tables. E.g.:

Table "Test": TestId PK, operator, test_date

Table "Result": ResultId PK, TestId FK, Object, TestResult

Where the "Test" table is the main table and the "Result" table is the details table. Test.TestId is a primary key (PK). Result.TestId is a foreign key (FK) related to Test.TestId. I.e. you have a one-to-n relation between "Test" and "Result".

In Access' relationships editor it would look like this: enter image description here

Splitting a table into different tables to avoid repeating information is called Database normalization (Wikipedia)