University Virtual Learning Environment 3.0

Table Inheritance: A Great Gift From PostgreSQL

Though we have discussed PostgreSQL's object-relational features in our database class, it is not until now that I have used the database's inherit feature. It is a feature wherein a table can be designated as a parent table by one or more child tables. An entry in the child table would automatically be reflected in the parent table. Child tables may have other columns than its parent table - what's important is that it inherits all columns of the parent.

This is specially useful in users management, when most of the times users of the system are separated into roles. In UVLE, for example, there are five roles, namely, root, administrator, instructor, student, and guest.

When the "is-a" relationship, like administrator "is-a" user, is represented in non-object-relational database (or more specifically, MySQL), it spans at least two tables. The more the roles are, the more tables are "artifically linked" by foreign keys. This then adds problems to atomic insertion, id generation, and more complicated select statemetns, to name a few. Since we have to make non-trivial SQL queries, autogenerated data access objects are hard to create.

With PostgreSQL, all of these worries are wiped out. You don't have to worry how to get the last inserted autogenetated primary key to insert half chunk of the administrator user, PostgreSQL does it for you! =)

Another promising feature of PostgreSQL are views. I think this has a tremendous potential in making the UVLE data access object design a lot cleaner, but we'll stil l see.

Does MySQL have these features? Well, MySQL 5.0 is still to be released, but the beta version proved to be promising, though not as feature-robust as PostgreSQL.

posted by Ardee Aram @ 10:31 PM