Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Question a challenge for DB-design specialists: Simulating Polymorphism in the database

    I have the following task at hand:

    Store items in a database, that have an arbitrary amount of properties, of arbitrary types.
    Simple example:

    item: Pipe
    Property1: length (double)
    Property2: interior diameter (double)
    Property3: exterior diameter (double)
    Property4: material (varchar)

    So I need a way to store many different types, but access them uniformly, i.e. a query that would return something like

    pipe length 50.0
    pipe interior diameter 3.5
    pipe exterior diameter 4.0
    pipe material steel

    I considered creating a separate value table for every type in use. (like "integervalue", "doublevalue", varcharvalue" etc.) This leads to the problem, that I cannot refer to these value tables with one foreign key.

    One possibility would be to store the name of the table holding the value of the property in the property-table.
    But that would mean that I store meta data within the data, which is rather ugly from an academic standpoint. It also means that I would have to perform two separate queries, one to obtain the table-name, the next to obtain the value from the table with that table-name. Or is there a way to dynamically specify the table to use in the from clause?
    Something like:

    SELECT * FROM property p, TABLE_whose_name_is_that_of(p.tablename) AS valuetable
    WHERE...

    Another possibility would be to create one value-table only, with a blob-column that holds the value in its binary representation, and a column that specifies how to interprete that binary data. Not too nice either.

    Any ideas, suggestions?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: a challenge for DB-design specialists: Simulating Polymorphism in the database

    I would not use a BLOB column. Why not just use a VARCHAR column of sufficient length to hold the longest values, e.g. 30 chars? (The longest values should not be very big anyway, since these are foreign key columns).

    Whatever you do it will not be pretty, and it does involve storing meta data. Here is a possible solution:

    CREATE TABLE property_type
    ( pt_code VARCHAR2(10) PRIMARY KEY
    , pt_description VARCHAR2(100) NOT NULL
    , pt_datatype VARCHAR2(1) NOT NULL
    , pt_data_length NUMBER NOT NU::
    );

    This will define your property types like "length", "interior diameter". For example:

    pt_code = 'LENGTH'
    pt_description = 'Length of pipe in metres'
    pt_datatype = 'N' /* Numeric */
    pt_data_length = 10 /* i.e. max 10 digits */

    You may want further attributes like decimal places, format mask, value requires description y/n etc.

    CREATE TABLE property_value
    ( pt_code VARCHAR2(10) FOREIGN KEY REFERENCES property_type
    , value VARCHAR2(30) NOT NULL
    , value_description VARCHAR2(100)
    , PRIMARY KEY (pt_code, value)
    );

    This holds the valid values for each property type, e.g.

    'LENGTH', 10,NULL
    'LENGTH', 20,NULL
    MATERIAL,'STEEL','Steel'

    CREATE TABLE property
    ( ... /* whetever */
    , pt_code VARCHAR2(10) NOT NULL
    , value VARCHAR2(30) NOT NULL
    , FOREIGN KEY (pt_code, value) REFERENCES property_value
    );

    Note that you NEED 2 columns for the foreign key because you must identify (a) the value and (b) what type of value it is.

    Now you have a database design that is as flexible as you like. The downside is of course that there are no SEMANTICS. You are free to record anything you like about anything you like, e.g. you can assign an internal diameter to a person or a pencil! So maybe now you need to go further with your metadata and define tables to specify what property types are valid/mandatory for what object types...

  3. #3
    Join Date
    Jan 2003
    Location
    Germany
    Posts
    14

    Smile

    Thank you for your elaborate answer.
    This was one of the possible solutions I considered.
    The other one I suggested (separate value tables for every possible type) seems promissing for the more efficient storage of primitive types.
    I discussed both variants with a colleague, who preferred the later approach (having done it that way in another project).
    He also proposed another idea which would dynamically create tables and subtables for the items with the appropriate number, name and type of properties involved. Although it may seem quite logical, I think that fidling with the schema at runtime might be rather tricky.

Posting Permissions

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