A Method for Object-Relation Database Mapping
It is very common for an application written in an object-oriented environment such as .NET or Java to represent relevant real-world objects as object-oriented classes. For example, a business application may want to keep track of customer orders, so they may create a “Person” class, with a first name, last name, and address. There may then be a “Sale” class, to record sales, which would point to, amongst other things, a “Person” object.
This would generally be saved in a relational database, in a “Person” table and a “Sale” table. This mapping from objects to relational database tables works fine much of the time, however it has many shortcomings when things get more complicated...
Class Inheritance
Continuing the example above, we will have a problem if a customer can be an organisation or an individual, which is very common (this is a problem because our “Sale” object points to a “Person” object). Because people and organisations are often considered to be the same type of thing (which is common in law), it would make sense to have a “Person” class and an “Organisation” class, which both inherit from a “Party” class (“Party” in the legal sense, rather than the fun sense).
A “Party” class would then hold things common to both organisations and people, such as an Address, phone number, and for a web application a login and password. The “Organisation” class would have a “Name” attribute, while a “Person” would have a family name and a given name. A “Sale” object would then point to a “Party” object, and so a sale could be for an organisation or a person.
The problem now is how to save this structure into a relational database. This is a problem that many programmers have come across, and some common solutions are as follows:
- Create a Sale and Party table in the database, with the party table holding attributes for both organisations and people. When saving an organisation, the “name” attribute would be used, and the “family name” would be null, and vice-versa. This solution often suffices in small applications, however it also has drawbacks, for example you cannot enforce in the database that only people or only organisations can be related to another object. Besides, in the back of your mind, you know it’s a hack which will probably come back to haunt you later on down the track.
- Another option is to forget the Party table, and have a Person and Organisation table, along with a sale table. The sale table could either have a customer id, which would be unique over all people and organisations, and so the relevant Person or Organisation could be found, or a flag to say that the customer ID is a Person or Organisation. In both circumstances, no foreign key can be used so you are opening yourself up to referential integrity issues. Another possibility would be to have “SaleToPerson” and “SaleToOrganisation” tables, with each sale having one row in one of those tables. While referential integrity could be maintained, you have made a one-to-many relationship (one customer can have many orders) into a many-to-many relationship (one order could be owned by many customers). This is quite a messy solution which introduces more programming to make it work.
There are also other possibilities; all of them hacks with problems of their own. Fortunately, there is a better way...
The Solution
The most elegant, hack-free solution may seem pretty obvious: just create a Sale, Party, Organisation and Person table. Every record in the Party table has a corresponding record in either the Organisation or Person table, and so the Sale table has simply to point to the Party table. Because you are mirroring your object model in the database, you can take advantage of the object oriented features such as inheritance throughout your application, without filling your application with messy, hacked up code. However, you may have several questions at this point:
- When one object is spread across multiple tables, how do I link all the pieces together?
- If a Party table has no name attributes, how do I print the name of a customer?
- Should I use an Object table?
- Won’t it be slow, inefficient, and difficult to program?
- Is it really worth using this method?
I have been using this method since late 2004, and will explain the answers to all those questions below. One thing is for sure: I don’t know how I ever lived without this method before.
Implementation Details
The “Object” Class
Every object created in a language like Java or a .NET language inherits from the Object class, which is a very important and useful concept, and in fact it is useful to have an Object table in your database, which every object has a record in. The only important attribute to have in this is a unique ID for every object in your database. Because .NET and Java objects do not have an ID field, I recommend making an abstract class called “DBObject” for all objects you will save to the database. This will have an ID (probably an integer), and can also have other attributes such as “Last update date”, “update ID” (for prevent concurrent edits), “date deleted”, or anything else you would like to have common to all database objects.
Continuing the example above, the “Sale” object would extend “DBObject”, “Party” would extend “DBObject”, and “Person” and “Organisation” would extend “Party”.
A “Sale” would then have data in the DBObject and Sale table, like so:
DBObject Table
(Primary key) |
Last update |
|
102
|
18/10/2006
|
Sale Table
Sale Date |
(key pointing to Party table) |
Subtotal |
|
12/10/2006
|
56
|
100.00
|
Linking a row in the DBObject table to its corresponding row in another table
You should hopefully be asking: “What links these 2 separate records together?” One way would be to put a foreign key in the sale table called “DBObjectID”, which in this case would be 102. The Sale table would then have its own primary key. However, this is not recommended as it insinuates that the DBObject with ID 102 and the sale object are separate entities, which they are not. It is better to have them both have an ID column, which is the primary key in both tables, and in the Sale table also a foreign key to point to the DBObject table, like this:
DBObject Table
(Primary key) |
Last Update |
|
102
|
18/10/2006
|
Sale Table
(primary key, and foreign key pointing to DBObject.ID |
Sale Date |
(key pointing to ID column in Party table) |
Subtotal |
|
102
|
12/10/2006
|
56
|
100.00
|
So, if you would like to load a “Sale” from the database, and know the ID is 102, you can get all the data with an SQL query such as:
SELECT DBObject.ID, DBObject.lastUpdate,
Sale.saleDate, Sale.customerId, Sale.subtotal
FROM DBObject INNER JOIN Sale ON DBObject.ID = Sale.ID
WHERE Sale.ID = 102
Drop the WHERE clause and you retrieve all the Sale objects. So, to print the details of sale we just populate the Party with the ID 56 and print it’s name, right? Well, no, actually. A Party is an abstract class, so we can’t instantiate it, so we need to find out if the party with the ID 56 is a person or an organisation.
Recording the Object Type
Given a Party ID, we could just check the person table for a person with the ID 56. If one exists, then it’s a person, so we can get all the information about the person and print the given name and family name; if not then it must be an organisation, so we can then populate an organisation with and print out the organisation’s name.
If the idea of doing that seems morally wrong to you, don’t worry, there is a better way. If it seems acceptable to you, then picture this: you have a model with hundreds of classes, and you know only the DBObject ID. Do you still feel ok, checking every single table just to populate one object? If so, then you can skip the next paragraph.
Every object in the DBObject table needs to record the type of object that it is. This could either be strings defined in your application, such as “Person”, “Organisation” etc, or just record the full name of the class type (for example, in C# this would be person.GetType().FullName;).
A person record may therefore look like this:
DBObject Table
(Primary key) |
Classname |
Last Update |
|
56
|
MyApp.Model.Person
|
1/06/2006
|
Party Table
(primary key, and foreign key pointing to DBObject.ID |
Phone number |
|
56
|
+649-555-1234
|
Person Table
(primary key, and foreign key pointing to Party.ID |
First Name |
Surname |
|
56
|
Ken
|
Tacky
|
Given that you know the Party ID is 56, you can now populate the object with 2 queries to the database:
SELECT classname FROM DBObject WHERE ID = 56
Then, based on the classname, you know it’s a person, so you can get all the data for the person in one more query:
SELECT DBObject.lastUpdate, Party.phoneNumber,
Person.firstName, Person.surname
FROM DBObject INNER JOIN Party ON DBObject.ID = Party.ID
INNER JOIN Person ON Party.ID = Person.ID
If the idea of 2 queries is still too much to stomach, then, like me, you can include the classname with every foreign key link. The sale table would then be:
DBObject Table
(Primary key) |
Classname |
Last Update |
|
102
|
MyApp.Model.Sale
|
18/10/2006
|
Sale Table
(primary key, and foreign key pointing to DBObject.ID |
Sale Date |
(key pointing to ID column in Party table) |
Customer
Classname |
Subtotal |
|
102
|
12/10/2006
|
56
|
MyApp.Model.Person
|
100.00
|
Given a Sale record, you can now get the customer information with a single query.
Efficiency, Difficulty, and Worth
The obvious feature of the SQL is that it is full of joins. The SQL to populate each object will have at least 1 join in it, and often 2, which is clearly slower than join-less queries. Also, because the DBObject has a record for every object, this table grows very large very quickly. However, because they are all joined on primary keys the joins are fast, and modern servers can handle tables with millions of rows, this has not been a problem for me. While efficiency is important, many believe that in business applications, elegance is the most important.
Also, clearly it is more difficult to write methods to populate and save objects to and from the database when the object gets spread across 2 or more tables. However, because every object extends the DBObject class, you can actually write save and populate methods into that object, which, when combined with reflection, entirely remove the need to write populate and save methods for subsequent objects. In fact, this is exactly what the open source Shunde .NET Framework does for you. If using ASP.NET 2.0 with SQL Server, have a look at my Shunde Framework pages, as it removes all the difficulty of writing Object Oriented database coding, and has many more very useful features, such as ASP.NET form creation based on objects. If you are using another platform, then you will need to write your own framework.
Finally, is it worth all this trouble? Yes.
|
