Freefall: conceptual modeling and the Object-Relational Mapping Diagram (ORMD)
A long time has passed since my last post about Freefall, where I quickly explained the major steps I took for Requirements Analysis.
Let me first say that the project is almost complete and the reason why I chose not to update the series further until now is because the following step (development of an Object-Relational Mapping Diagram) was a task I really consider complete only today. In fact, when you develop such a complex diagram, it is quite impossible to think of all the attributes you’ll need so early in the development process. More likely, the game you’ll play would be that of first define in the conceptual model a basic set of attributes you’re sure you’ll need, then develop the logical model based on the attributes you identified in the conceptual modeling. From this point on, you’d probably tune each of the two forementioned models based on the discoveries you’ll make in any of the two, e.g. if you find at any point that in order to implement a given functionality you need an attribute you did not include in your conceptual (ER) diagram, you’d first add this attribute in a table definition, and then include it in the diagram too. On the other hand, you may come up with a better arrangement of the attributes in your conceptual model, and this will of course reflect in the respective changes in your logical model.
All this procedure has become very stable in Freefall, so I can now show you the ORM Diagram.
Freefall ORMD
The Object-Relational Mapping Diagram (ORMD)
I chose not to develop neither an Entity-Relationship Diagram nor a Class Diagram to design the system, but an Object-Relational Mapping Diagram instead. Why? The final ORMD shown below is a refinement of a first, very rough ER Diagram consisting of only the Entities discovered in the Requirements Analysis. ERs are very useful tools for quickly and easily focus on initial ideas, when they’re still few and blurry. But as you develop the system more and more they soon become not much suitable, as they cannot represent composite values or entities relationships such as composition and aggregation. Not to mention inheritance, which is not supported by pure relational modelling. These limits are overcome with Class Diagrams; so, instead of simply creating two separate diagrams (an ER Diagram and a Class Diagram), I created an Object-Relational Mapping Diagram, which I used to map Objects (expressed in UML Classes) to Relations (expressed in ER Entities), as the name implies.
Now, it’s worth saying that this is not the intended scope of Object Relational Mapping techniques, but I took the freedom to borrow the ORM Diagram tool in order to simply refine that initial, rough ER Diagram into something more expressive and powerful.
The orange boxes are entities, as they’ve been identified in the Requirements Analysis phase. Note as they are all and only the ones early actually identified. The blue boxes are classes. In total, we have 9 entities and 19 classes. The reason why we have more classes than entities (as I explained above) is an effect of the refinement: in fact, I first created as much classes as the entities were, and then started wondering how to extend the design. Let’s discuss these extensions in detail.
Discussing the extensions in the design
The first kind of refinement is regarding how to model addresses. Right from the beginning I tried to look forward and decided to give the possibility to potentially use any existing addresses in the world, thus promoting extensibility. So I created two classes: a parent class named Address, which contains information related to the address’ state and city, and a child class named ItalianAddress, which also adds attributes to represent the street name, the street number and the ZIP code. The State will be of type States_e, where the trailing ‘_e‘ stands for enum, which we’ll discuss in a later post. ZIP codes are not treated as enums because when the development started (October 2012), italian districts reorganization was in place, so it had no much sense to create enums as it was not (and maybe still is not) a stable concept. It’s clear that a customer can have one or more addresses, but we’re not interested in knowing all the addresses owned by a given customer, so we simply ask him for one, the main one. This way, in the Freefall little world, each customer has one and only one address and that’s the reason why the relationship’s multiplicity between the Customer and Address classes is a 1-to-1 aggregation relationship - where the whole is the Customer class and the part is the Address class, since in no way if a Customer object is deleted (or even killed!) an Address object’s lifetime terminates. We have to decide which object must refer to the other. In that it is a 1-to-1 relationship, any of the two objects can do this, but the common sense suggests us to let the Customer class refer to the Address class. Another reason for doing so is that the Customer is a central entity in our domain, much more than what the Address is meant to be, so it is more likely to ask for the address given the customer, rather than the contrary. Finally, The Address class is an abstract class, since in no way we can instantiate generic addresses if we don’t know what their structure is. We will use rules to prevent the Address class (resp. table) from being created (resp. populated) or manipulated:
CREATE RULE no_insert_in_address_table AS ON INSERT TO Address DO INSTEAD SELECT operation_not_allowed_FNC('INSERT',
'Address');
where the operation_not_allowed_FNC(text, text) function simply raises an exception each time someone tries to manipulate the table whose name is passed as the second argument:
ERROR: Operation not allowed: INSERT INTO Address.
The two parameters are simply used to personalize the exception message. Other rules are created for UPDATEs and DELETEs, so each of the 5 abstract classes appearing in our ORM Diagram (Address, TelephoneNumber, Operation, OrdinaryPlan and BankAccount) will have 3 rules, one for each event: INSERT, UPDATE and DELETE.
The next kind of refinement pertains to telephone numbers. Also in this case I used a base abstract class, named ‘TelephoneNumber‘, which holds information about country codes, treated as enums. TelephoneNumber has two subclasses: ‘CellPhoneNumber‘ and ‘HomePhoneNumber‘. The former models mobile phone numbers (to do so it holds the operator prefix and the core number), whereas the latter models home phone numbers and so it holds town prefixes and core numbers, too. The reason why they both store the core number is because not necessarily it has the same format for cell phones and home phones. CountryCodes, operator prefixes and town prefixes are declared to be of enums type. A Customer is supposed to possess no, one or more than one alternative telephone numbers, so the relationship’s multiplicity is declared to be a 1-to-many relationship. Moreover, it is an aggregation relationship, because just as in the Address case, if a Customer gest accidentally killed in a car crash his telephone numbers remain; another reason is because it would be completely a nonsense stating that a Customer ”is composed by” TelephoneNumbers. Since a Customer can potentially own more than one telephone number, we use a container to hold them in the Customer table. Note: the best choice here is to store TelephoneNumbers and not separated CellPhoneNumbers or HomePhoneNumbers, since we have no mean to know in advance what kind of phone numbers Customers will already own. An identical reasoning applies to the Operation table, since we want to register operations towards both type of phone numbers, while in the Contract table we’re most likely to include only CellPhoneNumbers, of course.
Last kind of refinement is related to the modelling of bank accounts, which are of interest for licensed customers only (customers owning a PayMonthlyPricePlan). Again in this case I created an abstract base class, named BankAccount, which only stores information about the balance, and a derived class, ItalianBankAccount which models an italian bank account using the italian version of the International Bank Account Number (IBAN) and storing information about the currency code (‘EUR’ for Italy). Just like addresses, I made the system suitable for future developments giving the possibility to use other kinds of Bank Accounts if required.
And that’s it for now. In the next post we will translate the conceptual model seen today into a logical model and we will talk about inheritance, so don’t miss it. See you! :)
Molto interessante. Un approccio moderno e strutturato che porta ad un buon SW. E permette di sfruttare al meglio tecnologie che aumentano la produttività e la qualità.
Ciao Giuseppe, credo anch’io che l’ORMD sia molto utile per raffinare il modello dalla sua concezione alla sua approvazione perché partendo da un ORMD vuoto, si creano le prime entità e man mano che la conoscenza del dominio aumenta, si aggiungono classi, relazioni, cardinalità e cosí via. Il tutto senza far esplodere il numero di diagrammi prodotti. Purtroppo ha 1 piccolo difetto: i diagrammi intermedi non sono disponibili, poiché tutti i raffinamenti avvengono su quello iniziale, ma per contro ció implica che tutte le modifiche che dovessero essere in seguito necessarie non vanno riverberate su altri N-1 diagrammi. Come sappiamo, non esiste una “silver bullet”, ma credo che l’ORMD sia un validissimo strumento di modellazione. Ciao! ;)
The post is remarkably well written, but i disagree on the way you conceive the aggregation relationship. While it is clear that, in the real world, an Address (or a PhoneNumber) still exists after its owner Customer dies, it is also true that conceptual data modeling within a software project is not about describing the world as it is (from a philosophical perspective, I mean): it is about an application domain.
Now the point is that in your application you probably don’t want to create an address and then to associate it to a customer. You create an address only when you create a customer. It is also true that when you create a customer you probably do not want to look if, by chance, another customer has the same address in order to associate it, because that would overly complicate the user experience AND because most of the times you just don’t need to normalize your database to that level. Most of the times the best solution, when creating the second Customer, is to create a second Address whose values are accidentally identical to the first one.
If this is the scenario, and 98% of the times it is, than the relationship between customer and address is more appropriately modeled as a composition. In fact, while it sounds absurd that a Customer is “composed of” its Address, it is not absurd at all that a Customer “record” is composed of an Address “record”. And that’s the point.
Hello Antonio, let’s clarify something:
Now the point is that in your application you probably don’t want to create an address and then to associate it to a customer. You create an address only when you create a customer.
Well, the point is that I DO WANT to create an address before it is associated to anyone. That’s because I’m imitating the fact that an address exists besides the existence of a person and likewise it continues existing even if it is later associated to someone else. Now, why should I delete the address whose owner (sob!) died? I rather think that it is more rensembling reality leaving the address untouched and setting the reference to it in the customer to NULL. When someone will later use that address again, he will simply point to it.
[...] Most of the times the best solution, when creating the second Customer, is to create a second Address whose values are accidentally identical to the first one.
Why not simply making the second Customer point to an Address already associated to another Customer? Me and my sister live at the same address, not two different addresses whose information are identical. Let’s abstract. Suppose you want to retrieve all the people which live in the same house: you would ask for something like this:
Your suggestion would make this request not satisfiable, because p1 and p2 would have references to different Address instances for what the DBMS knows because pointing to different PKs.
If I did not convince you let’s talk more about. BYE!
Nicola,it really depends on what your requirements are. If it makes sense for your application to let users “search” for an existing address just in case it has been already identically entered for another customer, then let them do so. No doubt it will help you in identifying co-located customers.
I was just telling you that “rensembling reality” quite often leads to design mistakes, because what really matters is not reality but the very small portion of it which is represented by the application you have to create.
What is really important, in your example, is
1) how likely are two customers to share exactly the same address
2) how important it is identify such situation
In my experience, 1) is generally really low and 2) is also low to medium. In this scenario your “fully normalized” solution complicates the user interface and rarely make end-users happy. On the contrary, when 1) and 2) are both high, your solution makes a lot of sense.
I mostly agree with your reasoning when considering real-life projects. Of course one must take into account actual needs and model the system accordingly, but in this case we are dealing with an academical project, so each decision is made not with respect to actual needs, but with potential ones. Since those are potential, the only way to identify them is with respect to reality and what they are most likely to be. And in real life is highly likely that two people share the same address, I think.
Apart from this, it is important to point out that Freefall runs entirely on console, as it is the backend of a mobile phone network operator. You can imagine that when customers perform operations, such as calls and messages, those roll continuously onto a company’s screen, so the whole is completely transparent to users and therefore no UI is actually needed.