Follow @DBDebunk Follow @ThePostWest


Conveying data fundamentals to practitioners, losing neither rigor, nor the audience is a difficult task. There are many experienced professionals with tool expertise, but poor foundation knowledge for which there is little regard. Even in academia education has been substituted by training, which is not the same thing.

One dilemma faced by an educator is the tension between the simplicity of examples effective for conveying general concepts or principles and the complexity of the reality to be represented in databases. The latter requires integration of many concepts and principles, as well as thorough business knowledge. This is part of the reason why I normally refrain from specific online modeling/design advice and limit myself to the general principles that must be adhered to in the process.

I was recently taken to task by EJ for "locking myself into refuting myself [for a] simplistic and obviously made up design model…". The "model" was a one table example:

EMPLOYEES {EMP#,EMPNAME,HIREDATE,SALARY,COMMISSION}

where only some employees earn a commission. I argued that "While the business model specifies that there are two types of employees--commissioned and non-commissioned--which should be represented by a table each, the design bundles both into one". In other words, it is a case of entity supertype-subtypes (ESS).

Note: Before I address the criticism, can you figure out what two tables did I have in mind? In my book PRACTICAL ISSUES IN DATABASE MANAGEMENT I proposed:

SAL-EMPS {EMP#,EMPNAME,HIREDATE,SALARY}
COMM-EMPS {EMP#,COMMISSION}

with a FK constraint on COMM-EMPS. But now I advocate:

SAL-EMPS {EMP#,EMPNAME,HIREDATE,SALARY}
EMPS {EMP#,EMPNAME,HIREDATE,SALARY,COMMISSION}

with a disjunctive constraint on both R-tables, ensuring that they are mutually exclusive with respect to employee rows. Can you figure out why I changed my mind?

EJ: Right off the bat, a more insightful view would be: While the business model TODAY specifies that there are two types of employees...

Mine was a simple example to convey the general ESS concept and the table design principle which corresponds to it. In that context EJ's comment is not "more insightful", but rather another principle in conceptual modeling--the time dimension--which certainly should be adhered to, but which is orthogonal to the one I was focusing on.

EJ: But then, are commissioned and non-commissioned really types of employees? Well, what if some but not all employees were also paid bonuses and awards, for example. Would we now have commissioned, non-commissioned, bonused, non-bonused, awarded, and non-awarded as types of employees? And would there be a table for each? And would they all be mutually exclusive? Or would only some be mutually exclusive and others mutually inclusive? In what combinations? Of course , no matter Fabian’s implication by extension that it does.

'Real world' is used in two distinct senses: the real world as the reality to be represented in the database (RW1) and the real world of common database practices (RW2) which are easily confused.

Most of the questions listed by EJ are at the business level, which involves perceptions of reality. There is no scientific basis on which to prefer one perception over another. If the perception is of ESS (RW1), the logical design must reflect it; and if the ESS structure is complex, so will be the logical model.

A true RDBMS supporting updatable views, constraint inheritance and ESS should facilitate the handling of complex ESS structures (RW1). What EJ means by "none of this reflects the real world" is that practitioners avoid ESS designs because SQL implementations do not! A a practitioner should not confuse RW2 for RW1.

EJ: More properly per the real world, salary and commission are types of wage, not types of employee. Then having recognized that reality, we could also recognize bonus, award, and whatever else as additional types of wage that some but all employees may be entitled to earn, and do and have earned depending on their job.

As I explain in Business Modeling for Database Design, not only can one's entity be another's attribute, but both perceptions can also co-exist. Thus, salaries and commissions can be modeled as both attributes of employee entities and as entities with attributes in their own right, provided there is consistency.

EJ: By encouraging “…commissioned and non-commissioned, which should be represented by a table each …,” Fabian encourages modeling and implementing business conversations rather than the structure that underlies the business conversations. That is, business will talk of having commissioned and non-commissioned employees, but that’s purely from conversations on process points of view. Structure always underlies process. So the focus should be on looking through the business conversations and processes so as to see the underlying supportive structure, and that is what should be modeled and implemented. Then, if and when needed, the business conversations and process points of view rendered as extracts, table views, and whatever.

Aw, c'mon. There was no conversation. I used a simple example to convey the design principle for ESS conceptualizations. EJ proposed instead:

JOB {job-id,job-title-dsc,job-narrative-txt}
EMPLOYEE {emp-id,emp-nm,emp-hired-dt}
EMPLOYEE JOB {emp-id,job-id}
WAGE TYPE {wage-type-cd,wage-type-nm}
WAGE ELIGIBILITY {emp-id,job-id,wage-type-cd}
WAGE PAYMT {emp-id,job-id,wage-type-cd,wage-paymt-dt,wage-paymt-amt} 

I suspect it is a design abstracted from conversations common to many enterprises. Fine, but

• it is not appropriate for conveying the ESS design principle
• it does not invalidate it
• nothing precludes its inclusion in any business model, if and where perceptions in the enterprise demand it

So, please.