Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    3

    Yet Another Newbie Problem

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    3
    Could you elaborate somewhat?
    As far as I know this is not supported by MS Access which has been chosen as the platform.

    Tim

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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 .

  6. #6
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    3
    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
    Last edited by ngnear; 01-25-04 at 16:10.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •