There are tons of posts out there from back-end wizards who have seen the light when it comes to object orientation. This post has a few quick pointers for those going in the opposite direction, front-enders like myself who are OOP-fluent but just starting to lift the lid on the back-end and peer into the darkness that is the business and database layers.

1. 70% of your instincts still apply. Designing a package of classes and a set of tables is a similar process. First you achieve a sense of how your specific domain (virtual worlds for 11-year-olds, shoe stores, mortgages, etc) works. Then you carve that domain into the most logically coherent parts. In both cases, the optimal division is the one easiest to maintain. For the OOPer, that means application-wide changes can be made in one place. For the database designer, that means data is not duplicated anywhere (data is “normalized”). It also means that the basic units of the system, whether classes or tables, can be combined and recombined for combinatorial limberness.

2. People have been here. That rustling sound in the dark is relentless human intelligence. Business coders have created a lot of Object Relational Mapping frameworks to access their databases directly via whatever objects their middleware languages offer. ORM is the kind of subject that inspires 800-page wrist-snapping door-stoppers. But not always. I’m learning Ruby on Rails now, and here is ActiveRecord’s mapping: one class=>one table, one object=>one row.

new_user=User.new
new_user.login=”omar”
new_user.save!
#Whoopee!

3. Proper inheritance makes class-to-table mapping cleaner. When you read about OOP, there is usually only one kind of inheritance admitted into civilized discussion. That kind is interface-driven inheritance: the subclass overrides the methods of the superclass specified by the interface, and deliberately adds nothing more, because both classes need to be swappable in their ecosystem.

This kind of inheritance is sublime when it makes complex systems hum. But what about the kind of inheritance that made you cackle when you first learned about classes, because it saved you so much damned typing–you know, when you started subclassing wildly, without thinking much about the ultimate system and fitting into it?

I find this kind of resource- and speed-driven inheritance useful at times, even in semi-civilized contexts. But beware. It makes table design far trickier when data is ultimately persisted to the database. It’s far easier to store the data for a superclass like Warrior and a subclass like Archer in the same table if only the shared “weapon” field is saved. Once you start persisting subclass-unique fields like “favorite golden arrow” you are going to need more tables and more service calls. So it’s usually better to use proper inheritance in presentation- and business-layer classes when their data persists.

4. Relationships between tables are more direct and and potentially more numerous than relationships between classes. Practically every Actionscripter can write snippets of PHP and SQL to retrieve values from a single table in a database. When that’s all I knew, I wondered how multiple tables were related.

It’s not that complicated. Each table has a column with an unique identifying integer or “primary key.” A record in one table refers to a record in another table by storing this key. So, for instance, a house table might have a owner column. That owner column would store a number that is actually a primary key in a second, “person” table. The “owner_id” column is a “foreign key” because it refers to a key in another table.

When I first learned about foreign keys, I thought of them as a kind of composition or aggregation: a house “has a” person in its owner column. But this ended up confusing me. A house database might have several foreign keys that point back to the person table (”realtor_id”). Several different houses might point to the same person…

What was most confusing about bringing an object-oriented perspective to database design is the political question of who gets to know what. With classes, the class that composes another class is for-sure the smarter one–it knows about the class it composes, while the composed class is usually clueless. So what about tables? Does it make sense for the house to refer to the person, or the person to the house?

I stopped asking these questions when I realized they were nonsense. A database doesn’t know jack shit. It’s just a bunch of tables connected by foreign keys that can go in any direction depending on the designer’s many imperatives. It’s formally simpler and practically more chaotic than a class-based architecture–and that’s fine, because a lot of the structure in the presentation and business layers just doesn’t need to be saved.

5. More than one class can map to a table. Any decent OOP programmer will habitually break down domain logic into small parts. But this habit isn’t always the best one for table design.

For instance, I’m coding a chat application right now. For the presentation layer (Flex), I have two very distinct classes: User and ChatMember. User stores all the usual data, and ChatMember stores shared-world data about what the user’s avatar is doing in the chat room (location, last words, posture). To my mind, even though a ChatMember refers to a User, they couldn’t be more distinct. One deals with an account, another deals with a chat room.

That said, I ended up stuffing the ChatMember data in the User table when it gets saved. I’m coding the backend of the application in Rails, and even though ActiveRecord allows me to write exhilaratingly intuitive code like “my_user.chat_member.location,” I got sick of writing that phrase over and over again. For the business and database layer, the ChatMember IS the User, and they might as well share a table.

This is hardly a huge revelation, I realize. Classes are combinations of data and behavior, and when the behavior is stripped on the way to persistence, the data can sometimes be combined before it is saved. I’m just logging these points because I found my progress in table design occasionally halted by ingrained OOP habits, and I hope they help someone making the same journey from front-end to back-end.

Tags: codeNo Comments