"subclassing" another table

[carpeliam]

In an Object-Oriented Heirarchy, I have a superclass table and a subclass table. The id (primary key) for each subclass element references an identical superclass element's id, indicating that they are the same element.

Should I include the following:
PRIMARY KEY (ID) REFERENCES SuperclassTable (ID)

Is that necessary, optional, encouraged, forbidden? I hope I explained myself correctly- if not, please let me know.

Thanks in advance. Liam Morley
imotic@hotmail.com
"light the deep, and bring silence to the world.
light the world, and bring depth to the silence."

 

[rycamor]

From the MySQL manual online:

"The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. See section 5.4 Functionality Missing from MySQL." (See

http://www.mysql.com/doc/C/R/CREATE_TABLE.html

, and

http://www.mysql.com/doc/M/i/Missing_functions.html)

Again, relationships are not directly available in MySQL, they have to be created by using your programming language to compare table and record contents. You could think of it as just creating two tables, and using your programming language to make sure the ID columns match up, or using a third table that simply references ID's for you.

To otherwise answer your question, though, I would need to know a little more about what you are doing with those tables.

If you really want to do objecto-oriented database programming, I recommend you check out PostqreSQL (

http://www.postgresql.org),

because it was written with the OO programmer in mind (that's why it's called an "Object-Relational" database.