Hi,

I've kinda been thrown in at the deep end at work after complaining that everyone and his dog has his own private "database" in Excel which is of no use to anybody else and that we need one central "real" relational database.
Being the one-eyed in the valley of the blind I've now taken on responsibility for specifying and ultimately implementing the DB.
At the moment I am stuck with the following:
Lets say I need to describe the dimensions of articles stored in the database. My problem is that some are square, some round, some oblong etc.. The simplest can be described with diameter and height, more complex may need length, width, height, end radius, top radius and wall height and more.
Question:
Should I put all shapes in one table and assign NULL to the dimensions not needed or should I define a shape ID of some sort and use different tables for different shapes, or have I missed the obvious solution?

Thanks in advance!

Tim

Define shape as a super-entity with each individual shape specification defined as a sub-entity.

Data(data_id)
Shape(shape_id, [type])
ShapeA(shape_id, dimension_1, ..., dimension_N)

Could you elaborate somewhat?
As far as I know this is not supported by MS Access which has been chosen as the platform.

Tim

you asked "Should I put all shapes in one table and assign NULL to the dimensions not needed or should I define a shape ID of some sort and use different tables for different shapes"

that is a good description of the supertype/subtype modelling problem

what r123456 (nice name, by the way) was trying to suggest was the second of your choices

put everything that each shape has in common into the main "supertype" table for your articles

then the attributes that they don't have in common would go into a subtype table

your articles might look like this in the main "supertype" table --

23 description23string 4 7 12 shape3
24 description24string 6 8 11 shape4
25 description25string 2 0 19 shape3
26 description26string 4 8 15 shape2

note that there are several columns that all shapes have in common -- the description, and three numeric ones, and then in the last column there is a "type" indicator to show which of the various subtype tables holds that particular row's additional data

in the shape3 table you would have

23 A 93 82 1011 937 900
25 B 86 21 1214 832 800

in the shape 4 table you would have

24 93 ZZ

and in the shape2 table you would have

26 FOO 9 3 8 6 4 24435 3 8 51101

does that help?

The supertype/subtype solution is good if you have a constrained number of shapes. But what if you have new shapes showing up all the time?

What you could have is

Shape(ShapeID, ShapeName)

ShapeDimension(ShapeDimensionID, ShapeID, DimensionName)

Object(ObjectID, ObjectName, ShapeID)

ObjectMeasure(ObjectID, ShapeDimensionID, MeasureUnit, Measure)

You'd have to put some intelligence in to assure that the ShapeDimension Shape is the same as the Object Shape when you collect your ObjectMeasure .

Rudy

Thanks for that, I think i'm getting there.
Am I right in thinking the "type" column is for information only and that the relationship is built through the first ID column?
I guess "type" will be used as an indicator to decide which action is appropriate when querying, updating or displaying information.

Certus

the number of shapes is restricted to less than 10 and is static.
The products in question are tablets and capsules.

Tim

yes, that's right, the "type" column in the main or supertype table tells you which subtype table the rest of the information for that row is found in

yes, the links are done through the id, which is the primary key of the supertype table, and both the primary key and foreign key in the subtype tables

Shape(ShapeA, Big ShapeA)

ShapeDimensions
(1, ShapeA, Height)
(2, shapeA, Width)
(3, ShapeA, Depth)
(4, ShapeB, Radius)
(5, ShapeB, Depth)

ObjectValues { ObjectID , DimensionID , Value }
(1, 1, x)
(2, 1, y)
..

A point to consider,

Additional constraints must now be implemented to ensure that ObjectID in the ObjectValues relation contains exactly x-tuples corresponding to ShapeDimensions. In otherwords, if two instances of ShapeA existed in the ObjectValues relation then exactly six rows would be required, unless of course you have optional dimensions which would then require additional code to enforce integrity.