Data Modelling

Lately I have been doing some heavy duty data modelling  Let me give you some insights about how i do it, some tips and tricks and how i feel the designing area is being improvised around the world.

What I have felt over the years is that if the design is good, the burden of coding is hugely reduced. If its not, it has its toll on maintenance and extending the system. Coding becomes messy catering to the business needs and ultimately you lose the visibility of system's whereabouts. Since last 6 years that I have been coding I have seen some kickass designs and some designs that will make you puke.  [Recently I saw a Ted talk: You put your image and character in the systems you design. So be careful]

Believe me when I say you need to be able to see where the organization is going 10-20 yrs down the line if you are designing the data model of the business. Even a small SMB can grow into a large one. So if your initial design is not a good one (or should I say NOT adaptable enough), then you have to probably start from scratch leave alone reusing.

How do we do it ?

I usually go for a generic one. This makes me to think of all possible use cases that the system is going to encounter. Then usually we go for a data driven design model. That is,  a design model where the data decides how the system will behave and all the workflows.  At some point in time we all have done it in one way or the other.  For example, using configuration in web apps.

OR

How will the UI design look like or in other words the UI layer is built to render the design (look and feel and events and logic) from configuration which is stored as data.

What does this imply ?

-This means that you have to build a UI layer (handler) that is very generic, intelligent and able to understand the data and render the design.

-The data model should be very generic and be adaptive to accommodate new workflow and UI changes just by changing data.

Having said that, lately I had an epiphany that why not the data model itself be stored as data? That would be awesome! Tomorrow if you going to have a new feature design then there is no need to go for a new design just store it as data i.e. store metadata as data and render the user data as maps to this data.

Most of you have been doing or using it without realizing it. For e.g. Flexible Attributes and Automated website creation sites available on the internet. With in-memory DBs being the buzz word lately anything is possible. A recipe of HTML 5, a server-side scripting language, in-memory DB, jQuery, XML and Cloud Offerings (Web and worker roles) and the boundaries are beginning to look hazy. But on that later.

Recently I read an article by SAP where it was clearing the concerns raised by Oracle over SAP HANA. It said, we do not need the overheads of indexing, materialized views, paging, etc. to traverse/manipulate the data because the BigData is in memory (RAM). It prevents or eliminates the overheads from existing itself. One of the facts, that Oracle had failed to look into.

I realized one more thing. It almost eliminates the use asynchronous programming because the entire data is available readily to DBMS; I want this data...here you go ! OR Modify this data...already done !

Whoa ! Whoa ! Now you start to realize what Time-Space trade-off meant which you read about in college.

Ok now time for some tips on data modelling.

If you have read DBMS and done E-R diagrams thoroughly then you need not read this.

I will simply explain how to decide where to establish which relationship (and behaviour  between entities. i.e. 1:N, N:1 or N:N and Parental using scenarios.

Tip: When you think of creating relationship between entities don't think in terms of entity or tables think in context of entity records or table rows.

1:N

Parent(1)-Child(N) records. 1:N relationship is usually created where there is a plausible parent-child kind of relationship and the existence of a child record is of very less value without the parent record and the same child record will bear no significance if it is associated to another parent record. It does not mean that if a parent record is deleted the child will have no significance at all. For e.g. Order records under Customer records. It is obvious the child order record should not be associated with another parent customer record. If the Customer is deleted the Order will still be used to calculate the revenue. You should configured cascading delete only if the orphaned child records have no significance at all.

N:1

It is the opposite of 1:N. When you create a 1:N relation it automatically creates a N:1 from the other side i.e. Child:Parent::N:1.

What the N:1 relation signify is that A parent can have many children but one child cannot have more than one parent. It is like branches under a single trunk. A branch can originate from a single trunk of course and not from many.

A tricky scenario where you might establish a N:1 is where you want to denote a "type" or "group" or "category" to a record. The "groups" or "categories" will be a master list here. In other words the Group:Record::1:N. You may do this when you are sure that the record will be solely belong to that only one type or group or category. What if the record can also belong to multiple groups or categories? For e.g. the relation between Employees:Languages. One employee can speak multiple languages. Here you establish a direct N:N relationship.

N:N Direct and N:N Intersect

N:N Direct is illustrated in the above example. In this an intersect table/entity is built which only maps the two records in relation and nothing more (using foreign keys).

Consider the above scenario, what if you also want to know how proficient an employee is in languages which he speaks i.e. can he speak, read, write, comprehend. If you want to store this data then you have to establish a N:N Intersect relationship in which the intersect entity or table will have one more attribute/column which stores the proficiency level of the language which the employee is related to i.e. Employee1 speaks English, Employee1 comprehends German.

Parental relation is a concept involving 1:N with some constraints i.e. Cascading or restricting or removing foreign keys for DML actions such as Update, Delete, Deactivate, Share, Assign, Associate(Parent or Reparent), etc. on child records and parent records when the action is performs on one part of the relation i.e. either on the parent or the child.

If you follow the above then you automatically go for 3NF normalization and eliminate almost all data redundancies. 
  

If you want to focus on faster data retrieval then you should go for de-normalized and indexed (non-materialized) "views".

But really today we just need to focus on organizing data well so that reporting can be done. If we have will BCNF then we are fine. We rarely come across 4NF and even if we do, we unknowingly resolve it using Intersect N:N. There will be some data redundancies in this case though.