Razer Technology Solutions, Inc.

332 Gordon Drive
Exton, PA 19341
610-524-7073

n-ary Data Modeling: Ternary Transactional Model

Domain Description: Assume that we are running a software development business (consulting
firm, IT Center, etc….). In the operation we have people and projects that are run on a regular basis.
Every employee has a varied skill set that may or may not benefit every project. Also, each and every
project will use a different, brand new skill set. Over time, there will be some consistency in the skills
used, but consistency can not be assumed. We would also like to be able to tell who is doing what on
each project. Sound like a real-world situation? You bet. Every software development effort ever engaged
fits this bill. Based on our brief description, we can draw some of the following conclusions about what
the data model should look like:

• There are three entities: employee, skill, and project
• Employee is identified by an employee id
• Skills are identified by an arbitrary skill id
• Pprojects all have a unique project code

Some of the expected relationships between the entities are:

• Project requires 1 or more skills or else it's a no-brainer!
• Skills will be used across multiple projects
• Projects will use a varied skill set that changes from project to project
• An employee has 1 or more skills
• A Skill is learned by 1 or more employees
• Not every employee will have all the skills
• Employees must have at least 1 skill!
• A project staffs 1 or more employees
• An employee is staffed on 1 or more projects
• A project must have at least 1 employee (the project manager)

We typically diagnose this as several disjoint binary relationships. In fact, we see a circular linkage between
employee, skills, and projects. The important distinction to notice is that each entity has a unique participatory
relationship with each of the other entities and that all relationships can exist at the same time between the
entities. In fact, in order to staff a project properly, you must know who is going to do what on your project
and what kinds of skills you need to meet the project goals.

Each of the entities described above participates in the relationships we've described. That is a strong indicator
of a transactional ternary relationship. We have in effect, a three-way many-to-many relationship with each
entity always articipating in the relationship.

figure 2: transactional ternary

This results in a diagram that looks something like the one shown above. Now the question is, how does this get implemented into physical database structures?

Prev Next