Mixed columns design problem

[globos]

Hi all,

I have a table design problem with the modeling of the dimensions of a work piece (mechanical part).
A work piece can be cylindrical (radius, height) or cuboid (width, length, height). Actually this is modeled like this :

table work_piece (
  id INTEGER,
  type INTEGER,  -- type = 0 -> cylinder, type = 1 -> cuboid
  height REAL,
  length REAL,
  width REAL,
  radius REAL
PRIMARY KEY(id));

The problem is : for a cylindrical work piece, length and width have no sense (only height and radius). And for a cuboid the radius dimension does not apply.
For now these fields are mixed in the work_piece table, but as explained this leads to incoherences.

The work_piece table is referenced in other tables through its primary key (id).

How would you model things to have a clean design ?


--
Globos

 

[SkipVought]

hi,

I would think that you interface would be the key for clearing upt the incoherences, as long as you have sufficient data elements to store the necessary values.

Behind the scenes, it would be like a redefine of storage, based on type. Only your technical people need to be dealing directly with the db. Certainly user should not.

Just my HO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[r937]

instead of REAL, i would use DECIMAL

instead of INTEGER (4 bytes) for type, i would use CHAR(1)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[alvechurchdata]

We had a weight-calculation program once which modelled solids as cubes, cylinders, spheres, prisms, pyramids etc. Our main table just had Dim1..Dim8 as the dimensions and a separate look-up table which drove the user interface.

This table held the labels that would be displayed to the user for each type of solid. For example, Dim2 might be displayed as "Height" for a cube or "Side" for a hexagonal prism.

It looks as though our models might have been more complex than yours. We had shapes like segments of a sphere and hollow cylinders so Dim2 might be need to be displayed as "Included angle" or "Inner diameter". Special fields for all these rarely-used dimensions would have been a waste and the lookup table made it very easy for us to handle them.

Skip's right. Hide the techie stuff from the users.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[BigRed1212]

For those of us playing along at home, would this be the way you all are thinking about it?

Table: shape
shapeid (1,2,3...)
shapetype (1,2,3...)
shapetypelabel (square, cube, sphere...)
whateverelse

Table:measurements
shapeid
measurementid (1,2,3...)
measurementtype (1,2,3..)
measurement (data)

Table:measuretype
measurementtype
measurementtypelabel (heighth, width, diameter...)


If so, is there a need for a table somehow that says a shape of type 2 (label is cube) should have three measurements with measurementtypes of 1, 2, and 4 (measurementtypelabel heighth, width, and depth)?

 

[Thargy]

globos,

I have found through sometimes bitter experience that decomposing storage as far as reasonably possible always yields benefits in the end. Therefore, I would recommend separate tables for each type of work piece, each with its own integrity constraints.

If you do decide to go with just one table, then you must apply constraints which enforce nullity, depending on the type. That way you won't end up with cuboid work pieces having their radius field populated. I reiterate, decompose as far as possible, and obviously normalise as far as reasonably possible.

Regards

T

 

[r937]

decompose as far as possible, and obviously normalise as far as reasonably possible"

i guess this would depend on your definition of "reasonable"

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mmerlinn]

[ ]

If I were to put all in one table I would do as you are doing and use the type as the basis as to which fields are significant for that item. As noted by others, using two tables, one for cylindrical objects and another for cuboid objects, has advantages, too.

However, we have a program that we use to store measurements of various parts and we actually combine the cylindrical and cuboid elements into the same fields in the underlying table, into the input fields on the input screens, and into the final output to HTML pages.

Since the type of part automatically determines whether cylindrical or cuboid measurements are significant, there is no problem combining them this way. We were doing the same thing manually in our card files years before we ever had computers, so it was a logical step to continue using the same system once we began using computers.

The fields contain these dimensions (Cylindrical/Cuboid):

Field_1: Overall length/Thickness
Field_2: Outside diameter/Depth
Field_3: Inside diameter/Width

An example of a generated HTML page can be found here:

http://mmerlinn.com/catalog/ellegirl25/bw_tkb/bwtkb5c0.htm#default

I realize that this is definitely not the optimum way of doing this, but for this application it works quite well.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[Thargy]

merlin,

I don't doubt for a moment that it works well. However, if you ever get past a certain number of parts, performance may nose dive. By storing in a common pot, you force your system (of whatever nature, RDBMS or not) to search throught all objects every time, even though you know in advance what type you're interested in.

By dividing down at the design stage, one eliminates the possibility of unnecessary performance issues ever arising, but sadly not performance issues in general. The other issues are that it becomes possible to make modular changes to code, i.e. tweak cuboid handling without affecting cylindrical etc. Also, the integrity constraints on separate tables can be altered, and the DBA in question can give a meaningful guarantee that it has affected nothing else.

I know these may seem like small beer, but the ability to localise and manage change is a great benefit.

Regards

T

 

[ChrisHunt]

I would denormalise it into four tables, Two define the possible shapes and the measurements that define them:

tShapes
shape_id
name

Example data:
0, cylinder
1, cuboid
etc...

tDimensions
shape_id
dim_id
label

Example data:
0, 0, height
0, 1, radius
1, 2, height
1, 3, width
1, 4, depth
etc...

The content of those tables doesn't change much over time. The other two store the details of specific peices of work:

tWorkPiece
work_id
name
shape_id

Example data
1, widget rod, 0
2, gadget box, 1
etc...

tWorkDim
work_id
dim_id
value

Example data
1, 0, 100
1, 1, 10
2, 2, 50
2, 3, 60
2, 4, 20
etc...

By not hard-coding any of the dimensions into the table design, you make your system more flexible for the future. If somebody comes along next year with a requirement for 10-dimensional hypercubic parts, your system can be adapted to cope with them in five minutes - just add new rows to the first two tables.

Of course it makes it a little more complex to write the system that way in the first place - but a little extra time invested in the start of the system can save a lot of maintenance time down the line.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[BigRed1212]

Chris,

For those of us still playing along at home, say I wanted to know what size packing box I might need for work piece number 2.

Against your data structure I think I could write:

select c.work_id, c.shape_name, e.label, e.dim_value
from tWorkPiece c left join 

(
SELECT b.shape_id, b.label, d.dim_value, d.work_id 
FROM tDimensions b left join tWorkDim d on b.dim_id = d.dim_id
) e

on c.shape_id = e.shape_id and c.work_id = e.work_id
where c.work_id = 2

(I changed name to shape_name and value to dim_value because name and value are reserved words).

This gives me a recordset like:

work_id	shape_name	label	dim_value
2	gadget box	depth	20
2	gadget box	height	50
2	gadget box	width	60

Am I thinking about it correctly?

 

[ChrisHunt]

Yeah, though you can do it without the inline view:

SELECT w.work_id,
       w.work_name,
       d.label,
       wd.dim_value
FROM   tWorkPiece w
INNER JOIN tWordDim wd
ON wd.work_id = w.work_id
INNER JOIN tDimensions d
ON d.dim_id = wd.dim_id
AND d.shape_id = w.shape_id -- Not needed, but maybe quicker

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ceh4702]

Looks like you want to be able to violate table structure design.

Parent => sub-parent ==> child

This might be described as tables within tables.

You could make one table for each table and point to the one or the other with pointers and allow a null pointer. Or you could just build a table with fields to describe both types of objects.

If you dont have a database that allows subtables, then this is what you have to do. Another concept might be a database that just does not follow table design concepts or supports something like Super-types.

If you do not like my post feel free to point out your opinion or my errors.

 

[ceh4702]

There are some databases which are object oriented and allow inheritance like in Java or C++.

Example only:

http://www.sql.org/sql-database/postgresql/manual/tutorial-inheritance.html

If you do not like my post feel free to point out your opinion or my errors.