Welcome to the 3rd article on our new Blog series about Oracle JET and Oracle Cloud.
Today we’ll start designing our application, starting with its Data Model. For that, well be focusing on Oracle’s SQL Developer Data Modeler as our tool and design the application’s underlying data model.
So, without further ado, let’s dig right in.
Data Modeling Workflow
Let’s start our SQL Developer and go right into the Data Modeler.
Open up the model browser and save the existing design with a proper, understandable name. I chose “OJetBlog-DataModel”.
Once you have done this, you can start working on your Logical Model. As you know, there are several models to represent your data model, from the most high level (not bound by the RDBMS) to the Physical Model that is totally dependent on the RDBMS.
For our exercise, we’ll model our application in our Logical Model, pass it through to the Relational Model, and the Physical Model, through the generations of specific DDL for our Oracle Cloud Database. Any changes that we need to make in our database will be performed at the Logical level and then, using the SQL Developer tools, passed through our workflow and finalized in a DDL that will be executed on our DB. Keeping this workflow ensures coherence in your designs and a properly documented and maintained DB.
Creating our Logical Data Model
So, using the Data Modeler browser, locate the Logical Model, right-click it and then choose “Show”. This will show your Logical Model diagram, which should be a blank canvas by now.
The image above highlights the tools available to create your logical data model. You can create Entities, Views and relationships, place notes and images, etc… For this exercise, I’ll create our entities and their corresponding relationships.
First, let’s think about what entities should be involved in our application and their relationships in terms of cardinality. As a quick summary of our project’s goal, we’ll be able to create an Evaluation of a Worker’s performance during a Project Sprint. A project will have a lot of sprints, and each sprint will have a series of evaluations, one per each worker. Of course, workers will perform several sprints also, sometimes even overlapping (working in two distinct sprints at once, on different projects). So, we’ll have:
- Worker – one of our company’s employees involved in a given sprint.
- Project – one of our company’s projects.
- Sprint – a project sprint with a given start and end date that roughly equates to a set of work items being performed by several workers within a project scope.
- Evaluation – an evaluation of a given worker on a given sprint.
- Worker (N) – Sprint (M) – One sprint relates to the collection of project work done by several workers on a given period. On the other hand, a worker necessarily works on more than one sprint. So this equates to an N:M relationship between these two entities. As you know, N:M relationships are typically bad and we should try to avoid them. I try to resolve this issues immediately in the Logical Model. I think it has a lot of advantages tackling this as soon as possible. So, I’ll create a relationship entity to transform this N:M relationship into two 1:N relationships. To see more on this, check out this article on database normal forms, specifically the 3NF.
- Project(1) – Sprint (N) – Each project will have multiple sprints, but each sprint belongs to only one project. A simple, classic 1:N relationship.
- Worker (1) – Evaluation(N) – Each worker will have multiple evaluations, but each evaluation will refer to only one worker.
- Sprint(1) – Evaluation(N) – Each sprint will have multiple evaluations, but each evaluation will refer to only one sprint.
So, let’s create our entity. Select the appropriate icon in the toolbar, and then draw the entity in the canvas. You’ll be presented with the Entity Properties dialog.
From here, you can specify your entities to the tiniest detail, but we’ll focus on the first two sections: General and Attributes.
In the General Section, I’ll just specify the name of the Entity, in this case, Project. Then I’ll go into the Attributes section and specify the attributes of a project, as seen in the dialog below.
Attributes can be created using the toolbar. In this case, I created the project ID, project name, and customer name. If this would be used for anything else, I would probably have Customer as an ID to a Customer entity, but for our exercise, this is enough. Please notice that in the case of the ID, I checked the “Primary UID” checkbox, which should set this attribute as the primary identifier of this entity. When you check it, it automatically also checks the “Mandatory” checkbox. So this takes care of the primary keys.
But when you have relationships between entities, you should also have Foreign Keys. I bit on that later, when we create our relationships.
Go ahead and create the Project entity and the Sprint entity as well, using something like these attribute definitions:
Most of the attributes have a Logical Data Type. Logical Data Types are the ones which are not associated with a specific business domain. You can think of them as the usual types, such as numeric, boolean, date, string (VarChar), etc… As you can see, the first three attributes are of logical data type (Numeric and Date), but the fourth one isn’t. It’s a Domain-based attribute, which means that it has a specific structure specific to a business context. We want our Sprint Status to take only a given set of possible values and we do this by setting a specific Domain type.
Specifying Domain Types
To manage your domain types, go to the Domain Administration Tool. In there, you can create your own domain types, as I did for the Sprint Status. Let me show you how.
Select the option to add a new Domain type, give it the name you want and then specify to which logical data type will this map to. In our case, we chose the VARCHAR data type, with Char unit and a size of 20, which is enough to hold a sprint status.
Once the logical type is set, we’ll define the list of all possible values by going to the Value List option. In there we add our Sprint statuses, as shown in the next image.
And that’s all there is to it. Ah… don’t forget to save it. Your domain type is created and ready to be used when defining your attributes.
Entities have relationships between them, as I mentioned before. We even characterized them so now it’s time to create them in our diagram.
Creating relationships is very simple. You select the type of relationship you want to create between two entities by clicking on the respective icon in the toolbar. Then you click on the Source entity and then on the target entity and the Relation Properties dialog appears for you to specify its details.
Let’s take the example of the Project and the Sprint entities. Create a relationship between the Project and the Sprint entities.
The relation properties dialog appear and you can fine-tune your relationship. I just click ok. 🙂
You’ll notice that there’s a new attribute in the Sprint entity called ID1. This corresponds to a Foreign Key to the Project Entity and is automatically added when you create a 1:N relationship on the N side.
I go to the Sprint properties dialog (double-click on the entity in the diagram), Attributes and double-click on the ID1 attribute from the list of attributes. I then change the name of the attribute in the newly opened dialog to ProjectID, as it refers to the Project ID in the Project entity.
And this is how you create your relationships. Time to create our complete Logical Model.
The full Logical Model
So now, you only need to create your logical model using the instructions I explained earlier. For the sake of time, let me just take my ready-made pie from the oven, which is to say, show you my complete Logical Model. Here it is in all its glory. I use the Bachman notation, but SQL Developer also supports Barker and Information Engineering notations. Try them out and see which is better for you. This article on Wikipedia is an excellent starting point to understand the different notations.
From these two diagrams, you can understand the Entities, the Relations and the respective attributes involved. The only thing missing is the list of values associated with the Project_Role domain type that you can see in the Worker Sprint entity. Here it is:
Now all you have to do is create this model yourself.
I’m pretty sure it can be improved, as I only have some basic database modeling knowledge. Feel free to place suggestions in the comments
Our Database model is designed. Next time, we’ll go through the Relational and Physical Models and put the actual database artifacts in our Cloud database. Stay tuned!
P.S: next week there will be no article, as I’ll be enjoying some Carnival holidays! 🙂
José Rodrigues, a.k.a. Maverick
Post image by Jon Olav Eikenes