Objects and tables have different purposes in programming. On one side, the objective approach is focused on building applications out of objects that have both data and behavior, and on the other side, the relational paradigm is focused on storing data. This means that it is not essentially easy to convert an object into a database table. You need a technique to proceed. If one technique is not enough to solve your problem you may need to move to the next approach, but at the end I will show that the problem may be harder than it looks.
For this post, we will be using Object Relational Mapping (ORM), which maps a data model to object model and viceversa. It is a technique for converting data between incompatible type systems using object-oriented programming languages. Some key aspects for this are the following:
Mapping attributes to columns. This approach is very naive, you just add a column for every attribute and set the correct data type. For this to work, every attribute only has one value (it is not a list of attributes)
Mapping classes to tables. Here is when things become difficult. The problem starts when you want to implement hierarchy and inheritance in the classes, You can use different approaches. One is to use one data entity for an entire class hierarchy. The advantage of this approach is that it is simple and that enables polymorphism in an easy way. The disadvantage is that every time a new attribute is added anywhere in the class hierarchy a new attribute must be added to the table, which can be annoying.
The second idea is to use one table per concrete class.The advantage of this is that you can create reports very easily because all the data is in a single table, but the disadvantages are that when you modify a class you need to modify the table and all the tables of the subclasses.
Finally the third idea is to use one data entity per class. As in the example in the image, where the primary key is used in all the entities. The main advantage of this is that it goes well with and OO notion: it supports polymorphism and it is easy to modify subclasses and superclasses. On the other hand, this has some disadvantages: it leads to having many tables and it takes longer to read and write data.
Implementing many-to-many associations
For this case, you usually use a data entity (a table) whose sole purpose is to maintain the association between two or more tables in a relational database. It means that you use and extra table in which you can put two keys and the relationship that you want to show.
The next video talks about ORM and is very clear in the ideas behind the concept:
Using a relational database helps you with the mismatch between the object paradigm and the table paradigm. In a non-relational database the task becomes very difficult since there’s no standard in the data presentation. The main reason for this chaos is that you have different types of noSQL databases. You have Bigtable-like systems (HBase, Hypertable, etc), Key-value stores (Tokyo, Voldemort, etc), Document databases (CouchDB, MongoDB, etc) and Graph databases (AllegroGraph, Neo4j, Sesame, etc.) In the case of graph databases, there is an approach that helps you model the relationships but it is out of the scope of this post 😦 I found it here.