Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Q: Object-Relational Design

    Greetings:

    I am designing a database for an Oracle 9i RDBMS. I have designed fully normalized databases in the past, but I am playing around with the idea of making this one object-relational and have some questions.

    Issue: I have a table for scientific data collection (DATA_COLL); for each record (observation) there are different sources of data collected (temperature, salinity, depth, etc.); for each source, there must be a method (thermometer, CTD, satelite sensor). Rather than have a "Method" look-up table (with columns: method_id, method, description) for each data source (TEMPERATURE_METH, SALINITY_METH, etc.), I would like to have one table, METHOD, that adds the column "column_name." Then for each data record, I would have multiple METHOD primary keys stored in several columns in DATA_COLL.

    So my questions are:
    1) Is this really an example of object-relational database design?
    2) If so, how does this look in an ERD (many-many, multiple one-many's)?
    3) Can this relationship be maintained using a traditional foreign key, or does the relationship have to be purely functional?
    4) If FKs don't work, then what functions/constructors will I need?

    Thanks for any help/insight you can offer.
    -Jason

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

    Re: Q: Object-Relational Design

    1) It sounds like you are proposing a kind of generic code table approach, not Object-Relational really - instead of having a number of code/description tables you are proposing to have one type/code/description table like this:

    Type, Code, Desc
    TEMP, THERM, Thermometer
    SALI, CTD, Whatever a CTD is
    ...

    2) The ERD would have the 2 tables with lots of relationships between them.

    3/4) If the PK of the METHOD table is (type,code), then to use FKs in the DATA_COLL you would need 2 columns for each like (temp_type, temp_code). Then you would also need a check constraint to ensure that temp_code was always 'TEMP' etc. If the PK of METHOD is just (code) then the FKs are simple, but to ensure that only appropriate codes are used (e.g. can't use a thermometer to measure depth) would have to be enforced by database triggers or not at all.

    I wouldn't go down this route if I were you, a table for each method type is more straightforward, and enforces data integrity with just simple FKs.

    FWIW, I wouldn't go for a true Object-Relational design either. It just complicates your data access for no real gain. OO is a programming methodology, it has no real place in database design IMHO.

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Thumbs up

    Thanks Tony,

    I'll give it some more thought then. For a true O-R model, would I need to define a "method" datatype? Do you know how Oracle 9i stores abstract datatypes (I assume they are as efficient on the server side as a normalized parent-child relationship, but with more complicated code for access)? It does seem to over-complicate the code though, doesn't it.

    BTW - CTD is an instrument that measures Conductivity (equivalent to salinity), Temperature, and Depth and logs it to a database.

    You've been very helpful.

    -Jason

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by orafish
    Thanks Tony,

    I'll give it some more thought then. For a true O-R model, would I need to define a "method" datatype? Do you know how Oracle 9i stores abstract datatypes (I assume they are as efficient on the server side as a normalized parent-child relationship, but with more complicated code for access)? It does seem to over-complicate the code though, doesn't it.

    BTW - CTD is an instrument that measures Conductivity (equivalent to salinity), Temperature, and Depth and logs it to a database.

    You've been very helpful.

    -Jason
    There are various O-R features in Oracle these days. Actually, one of them does make sense - user-defined or abstract datatypes. This is a logical addition quite compatible with a relational database. So you might define a type called POINT with attributes X and Y, and with built-in functions (methods) like distance_from_origin.

    What makes no sense to me, and seems to be the feature people are keen to use, is Nested Tables and Varrays. This allows you to bury repeating groups of data within records in a way that makes accessing them unnecessarily difficult. It's all based on a programming-centric view of databases that misses the point of the relational model. Oracle provides a useful feature called Object Views that allow you to create O-R style views of data in regular tables - this appeals, as it means you can satisfy the demands of the OO application designers without muddying your data model.

    There's a very good book by Tom Kyte called Expert One-on-One Oracle that explains what happens under the covers with O-R features, as well as showing how to make the best use of all the other Oracle tools. It's published by Wrox, who have just gone out of business.

Posting Permissions

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