Sfrgttk

How can I write humor as character trait?

Delete multiple columns using awk or sed

Microchip documentation does not label CAN buss pins on micro controller pinout diagram

Can I say "fingers" when referring to toes?

What does "Scientists rise up against statistical significance" mean? (Comment in Nature)

Why does this expression simplify as such?

Taxes on Dividends in a Roth IRA

Why is the Sun approximated as a black body at ~ 5800 K?

Why is the "ls" command showing permissions of files in a FAT32 partition?

How do you make your own symbol when Detexify fails?

Is there a RAID 0 Equivalent for RAM?

Change the color of a single dot in `ddot` symbol

Pre-mixing cryogenic fuels and using only one fuel tank

Why can't the Brexit deadlock in the UK parliament be solved with a plurality vote?

How many arrows is an archer expected to fire by the end of the Tyranny of Dragons pair of adventures?

What kind of floor tile is this?

15% tax on $7.5k earnings. Is that right?

Make a Bowl of Alphabet Soup

Why do Radio Buttons not fill the entire outer circle?

Is there any evidence that Cleopatra and Caesarion considered fleeing to India to escape the Romans?

Is it allowed to activate the ability of multiple planeswalkers in a single turn?

Does an advisor owe his/her student anything? Will an advisor keep a PhD student only out of pity?

What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?

Does the Linux kernel need a file system to run?

What kind of table structure to use to display product, testing, and test results?

Inventory Database Design IssueDatabase redesign opportunity: What table design to use for this sensor data collection?Save history editable data RDBMSDesigning Simple Schema for Disaggregation of Demand ForecastModeling a ticket system based on a set of disparate kinds of tickets?“Variant” values structure and their use for multiple columnsOptimum design / structure for product data database?What kind of database should I use?How can I define and declare a good PRIMARY KEY for my dataset?Interesting Database Designs for Product Catalogue including Recursive

0

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?

database-design normalization

share|improve this question

asked Mar 8 '16 at 13:45

user89089user89089

1

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Who informed you about the structure? Can't you ask that person for help?
  
  – Marco
  Mar 8 '16 at 14:43
  

  
  
  
  

add a comment | 

0

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?

database-design normalization

share|improve this question

asked Mar 8 '16 at 13:45

user89089user89089

1

bumped to the homepage by Community♦ 9 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Who informed you about the structure? Can't you ask that person for help?
  
  – Marco
  Mar 8 '16 at 14:43
  

  
  
  
  

add a comment | 

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?

database-design normalization

share|improve this question

asked Mar 8 '16 at 13:45

user89089user89089

1

share|improve this question

asked Mar 8 '16 at 13:45

user89089user89089

1

share|improve this question

asked Mar 8 '16 at 13:45

user89089user89089

1

asked Mar 8 '16 at 13:45

user89089user89089

1

asked Mar 8 '16 at 13:45

user89089user89089

1

1 Answer
1

active

oldest

votes

0

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.

share|improve this answer

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent answer, just want to expand on the DB being the container of the data, and the report being the rendering into information of the data - meaning that your initial thought was to store what you wanted to see: A list of paints, their tests, and the results in a grid. This grid can be created by querying the results table, and pivoting the data. Google PIVOT function.
  
  – Alocyte
  Mar 9 '16 at 10:25
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f131585%2fwhat-kind-of-table-structure-to-use-to-display-product-testing-and-test-result%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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.

share|improve this answer

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent answer, just want to expand on the DB being the container of the data, and the report being the rendering into information of the data - meaning that your initial thought was to store what you wanted to see: A list of paints, their tests, and the results in a grid. This grid can be created by querying the results table, and pivoting the data. Google PIVOT function.
  
  – Alocyte
  Mar 9 '16 at 10:25
  
  
  

  
  
  
  

add a comment | 

0

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.

share|improve this answer

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent answer, just want to expand on the DB being the container of the data, and the report being the rendering into information of the data - meaning that your initial thought was to store what you wanted to see: A list of paints, their tests, and the results in a grid. This grid can be created by querying the results table, and pivoting the data. Google PIVOT function.
  
  – Alocyte
  Mar 9 '16 at 10:25
  
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

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)

)

share|improve this answer

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

answered Mar 8 '16 at 15:36

DuffyDuffy

97837

answered Mar 8 '16 at 15:36

DuffyDuffy

97837