One Constraint for Multiple Columns 1

[AbidingDude]

I'm working on something for the Inspection area at work. They get these containers of small plastic spheres that get inspected. They measure the diameter with a set of calipers and record it on a paper report.
I'm working on a database for them. They always measure 10 and average them out. So I thought a good table would be:

CREATE TABLE ball_dia (
	bdia1 NUMERIC(5,3),
	bdia2 NUMERIC(5,3),
	bdia3 NUMERIC(5,3),
	bdia4 NUMERIC(5,3),
	bdia5 NUMERIC(5,3),
	bdia6 NUMERIC(5,3),
	bdia7 NUMERIC(5,3),
	bdia8 NUMERIC(5,3),
	bdia9 NUMERIC(5,3),
	bdia10 NUMERIC(5,3),
	unit VARCHAR(3),
	Caliper VARCHAR(10)
);

(where 'unit' is inches or millimeters, and 'Caliper' is the ID of the caliper used).
I also figured it would be a good idea to put a constraint on the diameter columns:

bdia1 NUMERIC(5,3) CONSTRAINT bdia1_valid CHECK (bdia1 > 0),

I don't really mind copying and pasting for all 10 columns but I was wondering if there was a way to apply a single type of constraint to multiple columns - something like:

CONSTRAINT LIKE 'bdia%' CHECK ('bdia%' > 0)

 

[Andrzejek]

One way to do it would be to have 2 tables:

Calipers table:[tt]
CaliperID
Caliper
Unit[/tt]

and Balls table:[tt]
BallID
CaliperID
BallDiameter
[/tt]
And your application takes care of 10 records in Balls table per CaliperID

Bonus with this approach:

They always measure 10 and average them out.

[tt]Select AVG(BallDiameter) As MyAvg
From Balls
Where CaliperID = 1234[/tt]

The 'always' statement in this situation ALWAYS raises red flag for me. No matter what Management says, (almost always ) they change their mind: "We measure 10 balls, but that's not enough. From now on we need to measure 12, or 15 of them" or "to save time and money, from now on we will measure only 8 balls instead."

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[AbidingDude]

The 'always' statement in this situation ALWAYS raises red flag for me. No matter what Management says, (almost always ) they change their mind...

Good point...