Is this a common database design / OO Mapping technique?

Question

I've recently been asked to rewrite a booking system that was not performing as well as a client would like. I've found the database schema / object mapping technique used to be quite unusual and was wondering if anyone else has come across anything similar.

The old system used about 25 classes for things like customers, appointments and orders etc, each of which has a unique property called 'class_id'.

When looking at the database, each class had a corresponding table, however none of them contained any foreign key fields although they had to be linked in some way as the application allowed the client to carry out various levels of reporting.

After further investigation, I noticed that the database contained another table called 'application_hierarchy'.

This table contained 4 columns which mapped out the relations between different records:

1. class_id - Which identified the record type
2. object_id - Which referenced the id of the record within its corresponding table
3. parent_class_id - Which identified the record type of the related record
4. parent_object_id - Which referenced the id of the parent record within its corresponding table

For example, if customer 5 has three different orders with id numbers 10, 11 and 12:

The Customer class was ID 4 The Order class was ID 7 So:

class_id | object_id | parent_class_id | parent_object_id
7          10          4                 5
7          11          4                 5 
7          12          4                 5

There would be a large number of rows in this table for every single object record in the application and these would be combined with a large join query to create the reports.

Trying to import this data into the new schema has been an absolute nightmare, mainly because the original author has neglected to document which class is identified by which id number!

Is this a commonly used technique or was the original developer just being a PITA?

Any advice / comments appreciated.

Thanks.

Answer 1

You might find you answer here : Stefan Tilkov: Thoughts on the Generic vs. Specific Tradeoff.