Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Multiplicity, Change and MV

Re: Multiplicity, Change and MV

From: Neo <>
Date: 10 Apr 2006 15:57:14 -0700
Message-ID: <>

> Change bothers me. Especially in database schema, and specifically when we want to accomodate change in the cardinalities of the relationships we are modelling. .
Over the course of developing and supporting various RMDB based applications for many years, I also experienced the re-occuring process of updating schemas, migrating data and updating related code/queries to implement additional/unanticipated features; and wondered what schema would reduce or eliminate this repeating phenomenon. What I found was that it requires a very generic initial schema having a few tables (ideally one) like T_Thing, T_Attribute, T_Value; and new/unanticipated things are added as records in existing tables as much as possible rather than adding new tables or fields. The downside of this approach (in addition to performance considerations) was the reduced utility of SQL which has to be compensated by more complex/generic coding. After experimenting with such schemas, my conclusion was that the more generic the schema, the more impractical it becomes in RM.

The impracticality of implementing extremely generic schemas in RM, was one of the primary factors that led me to develop a new data model which allows each db to start with a schema so generic that it eliminates the need to ever specify one; whether at the beginning when the db is empty or later when the db is populated; and also automatically normalizes data and maintains referential integrity. On the plus side, the new data model is so generic that it can handle a wider variety of applications than practical with any one existing methodology (ie hierarchal model, network model, relational model, etc). Another plus side is that existing queries and code are more resilient to future changes. Conversely, there are a number of down sides, the main one being that it is less efficent within the scope of a more specialized methodology, another being more complex queries/code/script.

Below is an abridged script which show how the initial query to find what courses a lecturers teaches is unaffected by going from one course to many. Note that in my method of modelling, all constriants (except referential integrity) requires user code (at this time) where as RM can implement basic one directly at db level.

// Store Tom teaches French in db.

(create tom teach french)

// Store Bob teach English in db.

(create bob teach english)

// Recall what Tom teaches.
// Returns 1 thing: french

(select tom teach *)

// Store Tom teaches German.
// Note, I don't have to modify schema to allow this.
(create tom teach german)

// Recall what Tom teaches.
// Returns 2 things: french and german.
// Notice the original query works!

(select tom teach *)

If you would like, please pose additional situations to compare how RM and my model handle them. For example, how to update the name of lecturers/courses, or how to have two lecturers/courses with no, same, or multiple names, etc. If you would like, I can post/email the script/db/exe (fits on floppy). Received on Mon Apr 10 2006 - 17:57:14 CDT

Original text of this message