Thursday, March 26, 2020

You Say Whatchamacallit, I Say Department

Last time out I tried to explain some of my thinking behind selecting the names of things we need to track in a relational database application. Specifically, I likened them to the nouns of a natural language as a way to help differentiate between entities themselves and the attributes that qualify them. It's more complex than that, though. Let's drill into one aspect of that complexity using the example of departments within an organization.

In terms of the Discourse Blocs analysis originated in Will Pitkin's original formulation, the specific structure here is:
X is one kind of Y.
The relationship between X and Y is that Y is a higher level group of similar X's, a hierarchy. X does not modify Y, which would be another possible X and Y rhetorical structure.
X is an attribute of Y.
Let's go back and look at two of the proposed tables I outlined in the previous post.

  • 2018 Budget
  • 2019 Budget
  • 2020 Budget
  • Current Employee Assignment
  • Department
  • Human Resources
  • Location
  • Employee
  • Payroll
  • Sick Days
  • Vacation Days

We're focusing on "Human Resources" and  "Department".

It's obvious to users of our natural language, that the rhetorical structure here is that "Human Resources" is ONE KIND OF "Department". (At least I think it's obvious to most of us.) We recognize that "Human Resources" is a qualifier (also referred to as an adjective) for the noun "Department". But the underlying rhetorical structure is that Human Resources is ONE KIND of Department, which is different in rhetorical meaning from, say, "Large Department" or "Small Department". "Large" and "Small" are attributes of ANY department.

In company A, Human Resources is a large Department.
In company B, Human Resources is a small Department.

There are rhetorically speaking, two X and Y structures at work.

  • X is ONE KIND of Y.
    Human Resources is a Department in Company A and in Company B.
  • X is AN ATTRIBUTE of Y.
    Human Resources is large in Company A and small in Company B.

Turning to the way we apply this understanding to the way we design tables in a relational database application, we can make the following conclusions.

In a relational database application that means we have one table called "Department", i.e. the noun, and that "Human Resources" is one of the records in that table  along with "Operations",  "Marketing", etc. They are all "One Kind Of", and that means they are all records in the Department table.

In other words, the specific terms differentiate between instances of "Department". Each such instance becomes one record. (I'll get to another aspect of this principle later, when I talk more about the problem of repeating groups and spreadsheet style tables.)

In the previous post, I also described the relationship between Employees and Managers along the same X and Y principle.

A "Manager" is ONE KIND OF "Employee".

Note that unlike Departments, we don't normally attach descriptors like "Manager" to nouns like "Employees" even though the X is one Kind of Y structure is the same. I.e. we don't say "John is a Manager Employee." We DO recognize, though, that the underlying rhetorical concept is consistent, i.e. "X is a kind of Y".

 Pitkin explored this important concept in his original work: most rhetorical structures can be expressed in more than one way. It's important to look for and identify those rhetorical relationships and not focus exclusively on the specific words in a block of discourse.

I realize that I'm pounding hard on this point. I'm doing so because it's so important to my way of thinking about table and relationship design.

It's not just the specifics of the words in a phrase that we need to understand. We also have to understand the principles by which we describe one thing in terms of another. At the risk of over-simplification, what I'm proposing is that it's not enough to notice that we can, and do, refer to a "Human Resources Department", even though we do not refer to "Manager Employee" or "Salesperson Employee". It's not just the actual syntax that matters, it's also the semantics behind that syntax which matters.

And now we have a better way to understand that meaning in terms of the X and Y structures.

Let me illustrate it this way. One of these statements is true, one is not. It has to do with whether the X and Y relationship is ONE KIND OF or AN ATTRIBUTE OF.

Once a Human Resources Department, always a Human Resources Department.
Once a receptionist, always a receptionist.

If we go to work on Monday in the Human Resources Department, we don't expect to go into work on Tuesday and find that the Human Resources Department is now in charge of Sales instead.

If we go to work on Monday as a receptionist, it's quite possible that we can go back to work on Tuesday as a Marketing Specialist due to a transfer or promotion.

And that's the kind of logic we need to bring to our tables in a relational database application.

I'll sum it up by saying that, if you decide you are looking at a potential component of your database, and you decide that it is an example of ONE KIND OF thing, it becomes a record in a table. If you decide it is an example of AN ATTRIBUTE OF, it becomes an attribute, or field, in a table.

Records and Attributes in a Relational Table