Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

One Constraint for Multiple Columns 1

Status
Not open for further replies.

AbidingDude

Programmer
Oct 7, 2012
74
US
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:
SQL:
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:
SQL:
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:
SQL:
CONSTRAINT LIKE 'bdia%' CHECK ('bdia%' > 0)
 
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:
AbidingDude said:
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
 
Andrzejek said:
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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top