Re: OOP real life example (was Re: Why is MySQL more chosen - Mailing list pgsql-hackers

> So my initial thinking is that this is a profound problem.  But after a little
> more thought, I can make the question_id field of the question table be a
> SERIAL type and the primary key.  That way, when I insert rows into either
> the position question or the binary question table, it will be picking the
> values out of the same sequence.  I won't have actual primary key integrity
> checking, but I'm fairly safe in assuming that it won't be a problem.
>
> Then my second thought was, perhaps I could write some sort of CHECK procedure
> which would verify integrity by hand between the two tables.  Or perhaps I
> could manually state that the primary key was the question_id field when
> creating both the child tables.  I'm really not sure if any of these
> approaches will work, or which one is best to do.
>
> So now that I hear there is a way to get from an object-relational solution to
> a solution using views, I'd like to know how to do it in general or perhaps
> with my particular problem.

The problem is, table inheritance is just syntatic sugar for creating
separate tables, and a view that does a UNION SELECT on them all
together, projecting only the common columns.

You want to go the other way around, with a setup like this.
   table question contains:question id - a unique identifier for each questionquestion_type -  binary or positioncommon
attributesof binary and position questions
 
   table binary_question_data contains:question id - references question tableattributes belonging only to binary
questions
   table position_question_data contains:question id - references question tableattributes belonging only to position
questions

If you need frequently to select just binary or just position
questions, you can create a pair of views to deal with them, along
the lines of
   CREATE VIEW binary_question ASSELECT question.question_id, ...FROM question, binary_question_dataWHERE
question.question_id= binary_question.question_id    AND question.question_type = 'B'
 

Now you have two data integrity guarantees that you didn't have
with table inheritance: two different questions cannot have the
same question_id, and a question can never be both a position
question and a binary question.

> I'm a big fan of OOP, as are the other people working with me on this
> project,

As am I. That's why I use, for example, Java and Ruby rather than
C and perl.

> so I would (personally) rather work around the existing inheritance
> mechanism

Well, an inheritance mechanism alone does not OO make. Please don't
think that table inheritance is OO; it's not.

> than implement a solution I probably won't understand later
> using views, though I'd like to know it also... what is your advice?

The implementation with views is standard, very basic relational
stuff.  Primary keys, foreign keys, and joins. If you do not
understand it, I would strongly encouarge you to study it until
you do, because you are going to be using this stuff all the time
if you use databases.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org   Don't you know, in this new Dark Age, we're
alllight.  --XTC