Mixin Classes and Relational Databases

 
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding
View previous topic :: View next topic  
Author Message
Dalem



Joined: 11 May 2005
Posts: 6

PostPosted: Mon Jun 13, 2005 8:40 pm    Post subject: Mixin Classes and Relational Databases Reply with quote

My objects are mixins and I would like to store them. Unfortunately, I don't have much experience in the database area, especially concerning interfacing OO design with relational databases.

I have thought of two solutions, and I don't like either of them:

1) Store everything in one table, allow NULL values. I find this to be unacceptable. Unless someone can explain how this idea is preferable, I won't be using it.

2) Create a table for each mixin-class and a table for objects. There would be a foreign key reference, either all in the object table or one in each mixin table, for each mixin that the object derived from. While is a bit cumbersome and requires me to translate the data myself, it is better than #1.

Does anyone have a better option for me to consider? Have I missed something? Is there some standard solution to this problem?

I did some googling and from what I saw a commercial application would probably use one of the object->relational mapping libraries to handle this for them. I didn't find any that were free however.

Thank you for the help.
Back to top
View user's profile Send private message
Author Message
Dalem



Joined: 11 May 2005
Posts: 6

PostPosted: Sat Jun 18, 2005 5:35 am    Post subject: Reply with quote

So, I stopped by TMC for the first time in a while and was confronted by the discussion between Tyche and lindalb regarding ODBMS vs. RDBMS. During the course of discussion tyche mentioned a few ways to use Relational Databases to do exactly what I am looking to do, the most interesting one using SQL:99.

I had been strongly considering MySQL, but it doesn't come near supporting REF(), or I haven't read anything that implies it does. Postgres, on the other hand, seems pretty close with the ability to define types and use them as fields. Are there any free RDMS's that do provide support for this syntax?

Do I have other options? My previous thought was to use foreign keys and join, is that what you would suggest?
Back to top
View user's profile Send private message
Author Message
eiz



Joined: 11 May 2005
Posts: 152
Location: Florida

PostPosted: Sat Jun 18, 2005 9:46 am    Post subject: Reply with quote

REF() is pretty gruesome, IMO. I am unaware of any free databases that support it, and PostgreSQL's table inheritance is broken in important ways. It has other ways to define types, as you mentioned. I don't use them and they're really not appropriate here anyway. Joins work.

In terms of features, just about everything blows MySQL away. PostgreSQL, MaxDB (formerly SAP DB), Firebird and Ingres are all free. Check them out. I also use SQLite for some stuff, though I've never tried doing anything major with it (my largest sqlite DB is ~1million rows, single user). It's pretty light on features, but it's fast.
Back to top
View user's profile Send private message Visit poster's website
Author Message
Tyche



Joined: 13 May 2005
Posts: 176
Location: Ohio, USA

PostPosted: Mon Jun 20, 2005 7:14 pm    Post subject: Reply with quote

I don't believe any free SQL:1999 ones exist for most definitions of free.
You can certainly mix and match different ways of mapping when and if it makes sense.
I think there are about ten ways to do it, if you include SQL:1999 features.

But ignoring those most RDBMSs can do the following...

1) Each class maps to a table - this is the straight forward way

Code:

  Foo (name, x)
   |      \
   |       \
   |        \
  Bar (y,z)  Baz(p)

 CREATE TABLE Foo (
   foo_id ID(or INTEGER) NOT NULL,
   name   CHAR(nn)   NOT NULL,
   x      blah,
   PRIMARY KEY (foo_id));
 CREATE TABLE Bar (
   foo_id ID NOT NULL,
   y blah,
   z blah,
   PRIMARY KEY (foo_id),
   FOREIGN KEY (foo_id) REFERENCES Foo);
 CREATE TABLE Baz (
   foo_id ID NOT NULL,
   p blah,
   PRIMARY KEY (foo_id),
   FOREIGN KEY (foo_id) REFERENCES Foo);


Multiple inheritance just inverts the parent child relationship, notice the foreign keys. Also one might try more appropriate names for ID keys. The name should reflect whether the mapping is partial, disjoint or overlapping generalization. BTW most of the other methods below can be used for MI as well.

Code:

  Bar (y,z)  Baz(p)
   |         /
   |       /
   |      /
  Foo (name, x)

 CREATE TABLE Foo (
   foo_id ID(or INTEGER) NOT NULL,
   name   CHAR(nn)   NOT NULL,
   x      blah,
   PRIMARY KEY (foo_id));
   FOREIGN KEY (foo_id) REFERENCES Bar(bar_id));
   FOREIGN KEY (foo_id) REFERENCES Baz(baz_id));
 CREATE TABLE Bar (
   bar_id ID NOT NULL,
   y blah,
   z blah,
   PRIMARY KEY (bar_id),
 CREATE TABLE Baz (
   foo_id ID NOT NULL,
   p blah,
   PRIMARY KEY (baz_id),


Side effects:
+ the tables are normalized and happy.
- doesn't eliminate the inheritance relationship (navigation)
+ the application doesn't need to know which subclass to search

2) Eliminate the superclass - Works when superclass is not itself instanciated (not a partial generalization).

Code:

  Foo (name, x)
   |      \
   |       \
   |        \
  Bar (y,z)  Baz(p)

 CREATE TABLE Bar (
   foo_id ID NOT NULL,
   name   CHAR(nn)   NOT NULL,
   x blah,
   y blah,
   z blah,
   PRIMARY KEY (foo_id)); 
 CREATE TABLE Baz (
   foo_id ID NOT NULL,
   name   CHAR(nn)   NOT NULL,
   x blah,
   p blah,
   PRIMARY KEY (foo_id));


Side effects:
+ the table is still normalized.
+ eliminates the inheritance relationship
- you cannot enforce uniqueness of name across tables
- the application has to know which subclass to search
- doesn't allow Foo instances

3) Eliminate the subclasses - Works well superclass can be instanciated. Only works for disjoint generalization because the attribute added can only be a single value.

Code:

  Foo (name, x)
   |      \
   |       \
   |        \
  Bar (y,z)  Baz(p)

 CREATE TABLE Foo (
   foo_id ID NOT NULL,
   type CHAR(nn) NOT NULL, /* type - attributes are null that don't apply to the type */
   name   CHAR(nn)   NOT NULL, /*    NULL, "bar", "baz" or some other codes
   x blah,
   y blah,
   z blah,
   p blah,
   PRIMARY KEY (foo_id)); 


Side effects:
- the table is not normalized
+ eliminates the inheritance relationship
- you cannot enforce uniqueness of name across tables
+ allows Foo instances
- the application has to handle type logic and nulls
- the application doesn't need to know which subclass to search

4) Eliminate subclasses - But allows overlapping of generalizations.

As 3 above but add...
Code:

   bar_type BOOLEAN NOT NULL,
   baz_type BOOLEAN NOT NULL,


If both bar_type and baz_type are FALSE then you have a FOO. Both bar_type and baz_type are TRUE for an overlapping generalization. This is useful in multiple inheritence.

5) Meta-data mapping method one. I've done this to implement an indirect meta data model for dynamic attributes. I've seen variations on it in commercial software.

Code:

 CREATE TABLE BigMetaDataTable (
   entity_name CHAR(nn) NOT NULL,
   obj_id ID NOT NULL,
   attribute_name CHAR(nn),
   value CHAR(nn),
   PRIMARY KEY (entity_name, obj_id)); 



6) Meta-data hack. Someone did this to TinyMush. It's positively sinful as if 5 isn't. Wink

Code:

 CREATE TABLE BigBallOfMush (
   obj_id ID NOT NULL,
   data   BLOB,
   PRIMARY KEY (obj_id)); 
Back to top
View user's profile Send private message Visit poster's website
Author Message
Dalem



Joined: 11 May 2005
Posts: 6

PostPosted: Tue Jun 21, 2005 4:10 pm    Post subject: Reply with quote

Thank you for taking the time to expand on each of the options for me Tyche.

You probably guessed that the first looks most attractive to me right now. However, having the supertype reference each of the subtypes with foo_id means that I must have a row in both Bar and Baz corresponding to the Foo. I have a overlapping generalization, but I don't want to have a Bar for every Foo. Is my only option which maintains data integrity to have a seperate key for each subtype in the supertype?

The meta-data technique looks interesting, however I feel like I would have a lot of space overhead (stores object ID for each attribute, and also conceivably object name), more importantly I wouldn't want to inspect the data manually.
Back to top
View user's profile Send private message
Author Message
Tyche



Joined: 13 May 2005
Posts: 176
Location: Ohio, USA

PostPosted: Tue Jun 21, 2005 6:34 pm    Post subject: Reply with quote

Dalem wrote:

However, having the supertype reference each of the subtypes with foo_id means that I must have a row in both Bar and Baz corresponding to the Foo. I have a overlapping generalization, but I don't want to have a Bar for every Foo. Is my only option which maintains data integrity to have a seperate key for each subtype in the supertype?


In both diagrams in (1) the subtype references the supertype. In the first diagram you can have a Foo without a Bar or Baz, but not a Bar or Baz without a Foo. In the second diagram you can have a Bar or Baz without a Foo, but a Foo must have both a Bar and Baz.
Back to top
View user's profile Send private message Visit poster's website
Author Message
Dalem



Joined: 11 May 2005
Posts: 6

PostPosted: Tue Jun 21, 2005 8:23 pm    Post subject: Reply with quote

I'm sorry, I didn't check the diagram given at the top of the example closely enough.

I was treating Foo as the supertype in both examples and thought that using foo_id as a reference to multiple subtables was a good way avoid all the unique id's. If it was legal that is.

That is all cleared up now.
Back to top
View user's profile Send private message
Author Message
Tyche



Joined: 13 May 2005
Posts: 176
Location: Ohio, USA

PostPosted: Tue Jun 21, 2005 9:02 pm    Post subject: Reply with quote

Dalem wrote:
I'm sorry, I didn't check the diagram given at the top of the example closely enough.

I was treating Foo as the supertype in both examples and thought that using foo_id as a reference to multiple subtables was a good way avoid all the unique id's. If it was legal that is.

That is all cleared up now.


Oh yeah, in the first diagram I did call it foo_id in the subtype tables as it points to the Foo part of either a Bar or Baz. But it really does double duty as it also is the bar_id or baz_id in the subtype tables. That's probably confusing, so you can do..
bar_id references Foo(foo_id)
baz_id references Foo(foo_id)
in the Bar and Baz tables in the first diagram depending on your preference. Or you could just remove the type from the name and do something like this everywhere.
obj_id references Foo(obj_id)
obj_id references Foo(obj_id)
Which doesn't really cause problems because the relationship is clear by the foreign key statement.

You can also vary (2) by including a table for only Foos if Foo is instanced. The same caveats apply except you can have Foos.

There's another mapping that involves putting the actual definition of generalization relationship itself in a many to many associative table (join table) which might be useful for a more complex MI relationship. I can't remember how it's done though or where I've seen it. I've never used it.
Back to top
View user's profile Send private message Visit poster's website
Author Message
Dalem



Joined: 11 May 2005
Posts: 6

PostPosted: Wed Jun 22, 2005 1:39 pm    Post subject: Reply with quote

I feel like I have a many-to-one relationship, so I think I will stay away from associative tables, though I did enjoy reading up on them.

Thank you for the reference.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    mudlab.org Forum Index -> Coding All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2002 phpBB Group
BBTech Template by © 2003-04 MDesign