×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Mixed columns design problem

Mixed columns design problem

Mixed columns design problem

(OP)
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 :

CODE

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

RE: Mixed columns design problem




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Mixed columns design problem

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

RE: Mixed columns design problem

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
www.alvechurchdata.co.uk

RE: Mixed columns design problem

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)?

  

RE: Mixed columns design problem

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

RE: Mixed columns design problem

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

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

winky smile

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

RE: Mixed columns design problem

 

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

 

RE: Mixed columns design problem

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

RE: Mixed columns design problem

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

RE: Mixed columns design problem

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:

CODE

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:

CODE

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?
 

RE: Mixed columns design problem

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

CODE

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

RE: Mixed columns design problem

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close