Database entities under object-oriented paradigm

[delikedi]

I've been developing a database that I created from scratch, with a large number of entities and numerous vba procedures in it, for a while now. I've become comfortable with the concept of normalized database design and also with writing procedural VBA code.

I believe I understand the basic idea behind object-oriented programming. It is a world of objects that have internal workings and that interact with each other via interfaces. I haven't truly operated in such a world, but I believe I get the basic differences between procedural approach (subs and functions that call each other) versus an object oriented approach (objects with internal subs and functions, interacting with each other).

I'm wondering how database entities translate into objects in an OOP environment. There's an undeniable resemblance between the properties of an object and the fields (or attributes) of a database entity. From the outset, it seems like all database entities (like the people table) can be translated into objects (like the person object). Facts about a person (like date of birth) can be read from the database while initializing the object, and the object can calculate additional properties (like age) that are derived from those facts.

I'm curious about your experiences about applying/using the object-oriented approach to work a database. Do you write classes the way you design tables? Do you use inheritance to relate objects, or write new classes to represent a record of a query, perhaps? What do you use to represent relationships? Have you experienced Object-relational impedance mismatch ? What dictates design, i.e. do you design the database to suit your objects or do you design objects to suit the database?

 

[DJkarl]

In my personal experience, VBA is not much of an OOP language, it codes more like a scripting language. Now that is not to say it cannot be done to some extent, but many of the common practices in a standard OOP language, inheritance, polymorphism (such as method overloading), etc are not supported in VBA.

“Do you write classes the way you design tables?”
Not typically no, however in .NET 3.5 MS introduced LINQ to SQL (http://msdn.microsoft.com/en-us/library/bb425822.aspx) which will build classes based on a RDBMS, essentially creating a table as a class, and allows you to use your programming language on the objects which the compiler then ties back to the database objects.

“Do you use inheritance to relate objects, or write new classes to represent a record of a query, perhaps?”
Inheritance is good…where it makes sense. Doing things simply because they can be done isn’t good, where a table is a sub-type or extension of another table then it makes sense to have a class inherit from a parent type, IE if every table in a database has a field called ID that is the same data type, then it makes sense to create a parent class or interface whose base contains that definition.


“What do you use to represent relationships?”
In VBA this is difficult, which is why I don’t typically use objects to relate to tables. In VBA I write SQL and let the SQL engine manage the relationships if they exist, my concern and focus in VBA is typically automation, and most of the data operations can be done using SQL. SQL is generally the more efficient way to process the updates and manage the data, using recordsets and other VBA objects have their uses but looping through a recordset to find a record vs. using a SQL statement with a where clause, the SQL statement wins in readability and performance more often than not.
(http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic5)

“Have you experienced Object-relational impedance mismatch ?”
Yes, imagine you have carefully designed your classes to mirror the table structures of the database you are connecting to. The DBA for that database decides that performance is an issue on a particular table and so decides that splitting some of the fields out into their own table makes sense and creating a join table to relate them to the original parent. This scenario creates some interesting challenges in your existing code, do you create two new classes, change the existing class, then go update all of your code to handle the two new classes and the changed old class, or do you create 3 completely new classes, then load that data into the old class thereby preserving the rest of your code but making it more difficult to trace back to the source data? No easy answer and in my experience no one size fits all answer will work.

“What dictates design, i.e. do you design the database to suit your objects or do you design objects to suit the database?”
I rarely have a choice in the matter, but when I do I think less about the programming and more about the data storage. This is two fold for me, if the data is stored properly then accessing it via SQL, objects or any other method will be fast and efficient. If I were to try and design a database that tied to the VBA object model then it may make sense in VBA, but using the query editor may not work so well. I think the database should be platform-agnostic; meaning whatever programming language you use it should work equally as well.

 

[delikedi]

Thank you for your thorough answer.

I've come to better realize that a relational database is actually a translation of a real life system into a virtual system. Your notion that a database should be platform-agnostic makes a lot of sense. The platform and/or the application that manipulates the database comes second while solving a data-centered real life problem. I know first hand how much code can be wasted simply by changing a field of a table. It is like moving a part of the foundation of a building. I understand from your comments that this is no different in the world of OOP.

Your notion about SQL made me glad that I've leaned towards SQL to manipulate data in my VBA code as opposed to focusing on Access' DAO or ADO methods. I guess good knowledge of the ubiqutous SQL language has benefits in all RDBMS platforms or programming languages.

The reason for my post was the fact that I am about to start a new project that is half business and half self-improvement, and it involves a database component and and Java language. I was wondering how it would differ from what I did in Access but I understand that there are no clean-cut rules of design involving OOP and DB symbiosis.