Vryjdfkk

I am creating a database, but I am a beginner. We test several different kinds of paint, with specific test methods, and I would like to document all of the results. So for an example, I would test one type of paint for adhesion, blister resistance, and sag. But, the adhesion testing records both 1 and 7 day results. I have created a table already, and was informed the structure was completely wrong. I only have one table, with about 100 columns that has all of the testing results. An example of my column headings are: ProductCode, BatchNumber, Adhesion(1 day), Adhesion (7 day), Sag. And the product information and test results are listed below. How should this actually be structured?

We would need a larger view of what you're doing to be super accurate, but based on your testing example you'll want a basic setup like this:

CREATE TABLE dbo.Products

(

    productCode NVARCHAR(100)

    --Any other columns relevant to describing a product go here

    ,CONSTRAINT PK_Products PRIMARY KEY (productCode)

)

GO

CREATE TABLE dbo.TestTypes

(

    testType NVARCHAR(100)

    ,CONSTRAINT PK_TestTypes PRIMARY KEY (testType)

)

Go

CREATE TABLE dbo.Tests

(

    testID int IDENTITY(1,1)

    ,productCode nvarchar(100)

    ,batchNumber int

    ,testType nvarchar(100)

    ,passed bit

    ,CONSTRAINT PK_Tests PRIMARY KEY (testID)

    ,CONSTRAINT FK_Tests_Products FOREIGN KEY (productCode) REFERENCES dbo.Products (productCode)

    ,CONSTRAINT FK_Tests_TestTypes FOREIGN KEY (testType) REFERENCES dbo.TestTypes (testType)

)

Products will just contain your basic info about a product and I'm assuming your ProductCode is a unique value that can be used to identify a particular product. If that is not true you can slap an IDENTITY column in there to be your primary key.

TestTypes will hold the name of each type of test you want to have a record for, right now it's just a primary keyed name to enforce uniqueness and for use with a FK to constrain your test column's value. You can add any other useful information that may need to be known for a particular test to this table.

Finally, you can record specific Tests for a particular Product and batch into the Tests table. I am somewhat assuming the tests results are a simple pass/fail, if not you can change up the passed column to reflect the results you need to record. So what will happen with this table is that every test for a product gets it's own row with it's result, this allows you to run a simple query on a productCode and a batch to retrieve all the Tests without having extraneous columns that may or may not be filled in.

This also allows you to add new Tests to the system without having to alter your Table structure and if applicable your products don't have empty columns for tests that don't apply to them.