Answering the Big Questions with Simple Answers

Mathematical and Linguistic Logic — The Rules of Normalization and the Rules of Rhetoric.

Relational database design is based on predicate logic mathematics, more specifically, the mathematical theory of sets. Unfortunately for many folks, the mathematics involved tend to be dauntingly complex. And I'm speaking from personal experience here.
At least partly for that reason, people have codified a set of design principles, or rules, to make it more accessible to a wider audience. We know these principles as the Rules of Normalization.
There are at least five Rules of Normalization, although only the first three are considered crucial to sound database design in many, if not most, cases. To make matters more challenging, different writers have come up with different formulations of the rules!  Here's a fairly typical overview of one of those formulations.
For a table to be in the First Normal Form, it should follow these 4 rules:

1. It should only have single (atomic) valued attributes/columns.
2. Values stored in a column should be of the same domain
3. All the columns in a table should have unique names.
4. The order in which data is stored does not matter.

For a table to be in the Second Normal Form,

1. It should be in the First Normal form.
2. It should not have Partial Dependency.

A table is said to be in the Third Normal Form when,

1. It is in the Second Normal form.
2. It doesn't have Transitive Dependency.

Do What Now?

See what I mean about accessibility? What are atomic valued attributes? What is a partial dependency and how does it differ from a transitive dependency and how do you know either of them when you see them? It's enough to make a grown person cry, right?
Seriously, though, while it is does take dedication and study, nearly anyone can understand and implement the Rules of Normalization. As a matter of fact, if you intend to work with MS Access to build relational database applications for yourself, your organization or clients and customers, you must do so.
As self-identified MS Access trainer and advisor, one of the ways I've tried to penetrate that layer of complexity on behalf of new developers is to offer analogies between the natural languages we all speak and the "language of the database" inherent in the Rules of Normalization. In a recent post, for example, I compared Tables, Fields and Relationships  between tables in a database to the Nouns, Adjectives and Verbs of a natural language.
I would like to expand on that further in this post.

Natural Language and Rhetoric, Database Language and Normalization

While the range of potential topics for discussion in natural languages is open-ended, we rely on a commonly understand set of rhetorical patterns for all of them. We use the same language structures to talk about an endless variety of topics: poetry, politics, business, science, wisdom, war, disease, culture, family, religion, and on and on and on. The important concept at work here is that there is considerable overlap both in vocabulary and—more importantly—in the rhetorical structures that organize our discussions of each of those subjects. That is the central thesis of Will Pitkin's PhD Dissertation, A Pedagogical Model of Discourse in which he argued that virtually all "discourse blocs" follow the same clearly identifiable rhetorical patterns. Understanding how discourse is composed of those building blocs is an essential skill. We seldom pay attention to that fact, but it is crucial to our ability to communicate complex subject matter. There are, as I see it, "Rules of Normalization" for rhetoric and we all intuitively rely on them to communicate.

Databases, in my opinion, are very much like that too. Any given database uses the same building blocks regardless of the subject matter it contains. A college professor, for example, might have a database to organize her library of poetry and literature, but it would probably not have a component devoted to rare mammals of the tropics. The scientist studying those mammals, on the other hand, would have tables devoted to species and habitats, but probably nothing about rhyme and meter. Yet poet and scientist both rely on the components of a database—tables, attributes and relationships between tables—in exactly the same ways.

The Role of Ambiguity

The problem is that natural languages tend to be a lot more forgiving of ambiguity.
Another way in which we can contrast natural languages and database languages is to recognize that ambiguity is an inherent, and sometimes even a desirable, element of natural language discourse. Poetry relies on it, as do politics and humor. The interplay between two or more possible interpretations of a phrase or sentence delights us, confuses us, and even enrages or mislead us. All of us have employed ambiguity at one time or another to achieve one or more of those goals. And the more skillful the writer, the more enjoyable it can be.

Technical discourse—which is the closest natural language analogue to the relational database applications of interest to us—is the most likely rhetoric to try to limit ambiguity.

To be useful, the language of a database must exclude ambiguity to the greatest extent possible. That is, after all, one of the primary reasons the scientist goes to the trouble of using it to classify the specimens collected for study. If you can't differentiate between those specimens, what's the point? It matters a great deal that the attributes of a small furry mammal are clearly and unambiguously stored in a table.
So how does this relate to our theory of database design? Let's take a look at the three components needed to "describe" any subject in the language of the database.

The Language of the Database

• Tables are the Nouns of the Database Language
• Fields are the Adjectives of the Database Language
• Relationships are the Verbs of the Database Language

Tables—The "Nouns" of the Database Language

Tables are the objects in which we store data about the subjects of concern to that database. In a database of poetry, we'll need to talk about things like "poets" and "poems", at the very least. If the database is intended to support analysis of poems, then it needs to talk about things like "rhyme" and "meter" and so on. If it's intended to support biographical history of poets, it will need still other tables. Each of those topics—or entities to use a more database-friendly term— is a candidate to become one table. And the names of those things correspond to the names of the tables. A table called Poet", a table called "Poem", a table called "Rhyme", and a table called "Meter" would appear in this database. More importantly, only those things identified as "poems" can be stored in the poem table. On the surface, this seems obvious when we are in the natural language realm. A poem is a poem, not a helicopter. But it's not always that simple, unfortunately. Is James Joyce's Ulysses intended to be a novel or a poem? It's certainly much longer than other poems like a novel is, but the language and style of it set it apart from most novels. It's been compared to Homer's Odyssey, itself an extended poetic tour de force hundreds of pages long. So, poem or novel, or a bit of both? That's where ambiguity fits nicely in a natural language, but much less so in a database. You can talk about Ulysses without ever classifying definitively. You can't store it in a database both as a poem and as a novel.
And that means we have to identify and define the characteristics by which we classify "Poems" in this database. By identifying the appropriate attributes, we can safely store data without ambiguity.

Attributes—The "Adjectives" of the Database Language

To make it clear what is, and what isn't, a Poem, the Poem table needs to store attributes, or characteristics, that pertain to Poems and ONLY to Poems. Poems may or may not have rhyme schemes, for example. And if they do, that rhyme scheme will be one of those previously defined,  iambic pentameter, for example, or blank verse. That means one of the fields needed for the Poem table is "RhymeScheme". There are many different possible values for that attribute, but there's only one category, or field, for them all. I'll provide more information on this in a later blog.

Relationships Between Tables—The "Verbs" of the Database Language

Poets WRITE poetry. Poems ARE WRITTEN BY poets. No other relationship exists. Poems are not BAKED BY poets. Poets don't DRIVE poems. In order for the database of poetry to make sense, then, there is one and only one possible relationship between the tables for Poets and Poems.
The verb "WRITE" in this natural language formulation corresponds to the logical relationship between the Poet table and the Poem table.
Let's foreshadow a future discussion by pointing out that Poets seldom limit themselves to a single poem in their entire career, at least they don't do so by design. So, while the relationship itself is between "poet" and "poem", the reality is that we have a table of poets and a table of poems, there will be one OR MORE poems in the poem table for each of the poets.
I think that's enough for now. I'll continue my thoughts on the Language of Databases in future installments.

Speaking of Relationships, How You Doin' ?

Many years ago I first started thinking about relationships. At that point, I was studying language and linguistics in preparation for what I thought would be a career in teaching English. That relationship didn't work out.

Many years later I discovered MS Access and fell in love all over again, starting another relationship that's lasted quite a bit longer.

What I didn't realize at the time, though, is that much of what I'd learned about rhetorical competence way back in the 70's was applicable to understanding normalization in developing relational database applications some 20 years later. And it's still relevant today as a I look back over several decades of a (mostly) enjoyable career.

Specifically, the linear legacy in both careers is that relationships are at the heart of everything.

The concepts behind Bloc Discourse* included the realization that comprehension of the content of any individual block of text is only one of the skills needed to understand expository discourse. There is a higher level principle at work. That principle depends on understanding the relationships between various units of discourse--sentences and paragraphs as well as longer blocs.

These days, when I try to explain Normalization to new developers, I often call upon a metaphor that combines both relational database design and rhetorical competence.

First, I like to think of Entities as the database counterpart of Nouns and phrases in a natural language. They are the names of things of interest.

Second, Entities and nouns don't stand alone. We need Attributes to provide the detail that makes our Entities meaningful within a context. They are the counterparts of adjectives in a natural language. They provide the nuance and flavor that distinguish one instance of an entity from another. The red car as opposed to the blue car. In a table of cars, therefore, we have a field called "CarColor" to hold that attribute of each car.

And third, in order to construct meaningful sentences, we need the linking words, verbs in a natural language and relationships in a database language. "Customers PURCHASE cars." "Dealers SELL cars."

All three components are required to communicate meaningfully, whether we are talking about a technical article describing the structure of discourse or a relational database application describing the sales operation of Red and Blue Motor Car Company.






*A Pedagogical Model of Discourse, Willis J. Pitkin, 1973

I May Have Been Ahead of Myself By 20 Years

First, a bit of background is in order. Recently, my ex-wife and her new husband suggested, if that's the right word, I might want to remove a few boxes of my things from their garage. They've been patient for a while, so even though I have no space for it all here, I went and picked them up.

One of those boxes contains papers I wrote while working on an advanced degree in English in the early 1970's, so maybe 45 years ago or there about.

I pulled one out and re-read it. It was on the general topic of rhetorical competence and the ability to understand technical papers by recognizing the structural relationships between units of discourse in those papers. My conclusions included the observation that, while content is important to reading competently, understanding those relationships and the writers' intent in establishing them was an equally important skill. In other words, the relationships between blocs* of text are just as important as the semantic meaning of those blocs of text.

So, as I read this paragraph, I couldn't help but think I was foreshadowing the days when I moved on to the development of relational database applications and returned to that fundamental concept: relationships between entities.

And finally, during the course of this analysis, it became clear that there were different kinds of relationships involved between certain units Specifically, it is possible to identify units from a structural viewpoint exclusively, in terms of bloc signals and sequencing of units. But in many cases, the analysis here resorted to what is more properly termed an "operational" analysis, wherein other considerations were made in deciding what the relationship with and between blocs should be. (emphasis added)

Forty five years ago, the key role of relationships was already being developed in how I understood effective technical communication. Twenty years later, when I first encountered Database Normalization, I guess it's fair to say that I was already primed to "get it".

And for that I will always be grateful to the late Will Pitkin and the other members of my thesis committee, John E. Lackstrom and Karl J. Krahnke.




=====================
*I used the spelling "blocs" because much of the thinking behind this approach came from a PhD Dissertation written by one of my professors, Dr. Willis J. Pitkin, who used that term in his work.

All At Once or One At a Time?

Like most Access developers, I pay a lot of attention to the technical aspects of creating tables in a Relational Database Application. Making sure all of the relevant entities are identified and tables are built for them. Identifying relationships between those entities and defining Primary and Foreign Keys to support and enforce referential integrity within those relationships. Sometimes, though, it's not enough to identify relationship as one-to-one, one-to-many or many-to-many. You also have to take into account the logic, or business rules, that apply to the business itself. That's what I want to talk about today.

Concurrent Status Vs Sequential Status

When designing and building a new Relational Database Application it's rather important that you understand whether a relationship is "All at Once" (i.e. concurrent) or "One at a Time" (i.e. sequential). Initially, they can look very much the same, but they're not. And that has implications for the design of the tables and the relationships between them. Let's look at a couple of examples, and see if we can sort out a workable solution.

One at a Time

These may sound like dumb questions, but I'm trying to make a point I'll explore later.

How many employees can drive the same company car at the same time? 

How many company cars can the same employee drive at the same time?

Pretty obviously ( I hope), only one person can physically drive a car at the same time, and one person can only drive one car at a time. Those logical restriction are based on the nature of "driving a car". Over time many different people can take turns driving different cars, of course. (If you happen to have a teen-age driver in your household and only one car, I suppose that could seem like more of a hypothetical proposition 😉.)

It also makes sense that an employee can drive different cars on different days. And different employees can drive one specific company car on different days. This can be thought of as a "One at a Time" scenario.

All at Once

Here's another set of questions that might help understand why it's important to know how it works before you start building tables in a Relational Database Application.

How many classes is Yoshiko enrolled in this semester?

How many students are enrolled in the Math 101 class this semester?

Again, it should be obvious that a person can enroll in one or more classes each semester. There's an upper limit to the course load any one person can handle, but the exact number varies from person to person and from semester to semester. Moreover, because a Class can enroll one or more persons, there is no logical restriction on the number of concurrent enrollments by different students. The only restriction here is that one student can't take the same class more than once in a given semester. This can be thought of as an "All at Once" scenario.

Concurrent Vs Sequential History

At first glance, one might assume that both of these scenarios represent Many-to-Many relationships, which means, in turn, that they require a junction table like this one, which illustrates a concurrent status for students and classes.

Students Concurrently Enrolled in Classes

Each student can concurrently enroll in one or more classes each semester. Each class can concurrently be taken by one or more students each Semester that class is offered by the school.

Our junction table for students and classes includes two foreign keys, one for each student and one for each class. In addition, to enforce the requirement that students can enroll in a class only once each semester, the StudentClass junction table includes the SemesterStartDate as part of the three field composite Primary Key. That allows students to enroll in a class more than once, sequentially. That might happen if a student failed the class on the first attempt and need to retake it.

For Company Cars assigned to Employees, we initially might want to design our tables the same way because we know that one or more employees can check out a company car, and that a company car can be assigned to one or more employees.

Company Cars Assigned to Employees

Our first attempt at a junction table for employees and company cars includes two foreign keys, one for employees and one for company cars. In addition, the CheckOutDate is included in the composite key. That means an employee can only check out a Company Car once on any given date. That is, in fact, what we want.

However, it turns out that this table design actually allows us to create records that would violate our rule about Sequential, or One at a Time, assignments. It is possible, with this design, to assign an Employee to two or more different Company Cars on the same date, or to assign the same Company Car to two or more different Employees on the same date. See the sample records in this query.

One Company Car Assigned to Two Employees on the Same Day

Both Bezalel Simmel and Georgi Facello end up being assigned the Ford Focus on the 2nd of January, 2020. And that is fully supported by the Junction Table. Obviously, this is not logically acceptable. We need a different kind of table design. Here's how the first attempt at resolving this problem might look.

Composite Primary Key on Company Car and Checkout Date

 This approach seems like it might work. The Ford Focus can only be assigned once per day, which means that Giorgio gets it on the 2nd, Bezalel gets it on the 3rd, and Parto gets it on the 5th. 

However, there's still another problem. Parto gets two different Company Cars on the 5th. The Primary Key restraint doesn't prevent that; it only prevents assigning the same car twice on the same day.

The same problem would appear, though, if we tried to enforce the Primary Key restraint on EmployeeID and CheckoutDate. Each employee could get one car per day, but the same car could be assigned twice (or more) on that same day.

Our next step in resolving this problem will be to add an additional unique index to the junction table, like this.

Unique Index on Two Fields Not in Composite Primary Key

By adding a second unique index on the other two fields we need to restrict, we can now prevent duplicate assignments to the same employee on the same date, in addition to preventing duplicate assignments of the same company car on the same date. 

Note: the desired restriction is enforced by the Unique property. The Primary Key is unique by default. The second index we created, on EmployeeID and CheckoutDate, could be applied without the unique constraint. However, because it is possible to create that second index and make it unique, we can achieve the desired restriction on Employees and Company Cars as "One at a Time".

There Is More than One Way to Get the Right Answer

It's worth pointing out that there are other ways to go about handling this situation. For example, the composite primary key could be on EmployeeID and Checkout date while the second, unique index could be set up on CompanyCarID and CheckoutDate. 

Not only that, we could assign a new AutoNumber Primary Key to this table, and make two other unique indexes which would achieve the same result.

Surrogate Primary Key and Two Unique Indexes

The key features of this approach to designing the tables for a Sequential Type of Many-to-Many Relationship include the following:

1. It limits records for each combination to "One at a Time"
2. It supports a history of transactions in which two entities (in this case, employees and company cars) are involved over time.
3. It enforces all of the restrictions at the table level. That means no additional wads of code have to be written.

 

Singing (and Dancing) With Access

Dancing with the Stars, Singing with the Idols

I like to think I can make MS Access sing and dance. Give me a task and most of the time I'll find a way to accomplish it. Once in a while it takes longer to navigate through a new kind of process, but so far I've never failed (well there was that once when I couldn't mimic Excel exactly in a datasheet view in a form, but it was on the clients "it would be nice..." list anyway). However, there are limits to what even Access can do. When I find myself stumped, I tend to want to blame Access. But that's just an excuse, on several levels. It shames me when I resort to blaming someone else for my shortcomings. That makes me human, I guess. It doesn't make me right.

Que Será Será.

 Access is what it is. How trite and tautological, right? But one of the things that alternately frustrates and amuses me is that Microsoft designed Access to do exactly what it does, no more and no less. I refuse to admit defeat until I've exhausted all of the options. Sometimes I'm quite delighted to learn there is a way to do something I would have thought impossible. Often those tasks relate to working with URLs or other things webbie. It's something I know little about. However, I've learned never to say never, except when I've spent enough time Bingoogling to conclude I'm not going to find an answer. Then it's time for a long walk and maybe a fresh cuppa Costa Rican dark roast.

The corollary to that, of course, is that Access isn't what it isn't. VBA is a rich, powerful coding language that works under Windows on the desktop. It's not a web coding language. It never was, and despite all the Sturm und Drang about it over recent years, there's little chance it ever will be (I'd say zero chance, but, you know, never say never). You want to make web pages, go learn a web coding language. There are lots of them and you are smart enough to figure it out, if you must. Just don't waste my time and yours whining about something that nunca será.

 Mommas Don't Let Your Babies Grow Up to Be Cowboy Coders

I run into this from time to time on the on-line forums. The question comes in the form of "How do I write a function (or maybe some SQL) to do X, Y, and Z? And then, when the answer is along the lines of "Use the built-in Access feature that does X, Y and Z, the response comes back, "But I want to do it this way. How do I do that?"

One common example is form design. Bound forms are the heart and soul of nearly every Access Relational Database Application. Writing a lot of code to handle unbound forms seems like a real waste of time and resources in all but a few very specific situations. If you want to ride off into the sunset on a white mustang, be my guest. But don't lose sight of the fact that the end of the road for most cowboys includes a lot of isolation.

If you don't want to do it "the Access Way", that's your call, of course. But  don't complain when it turns out to be harder, less reliable and less flexible than "the Access Way".

Know Your Limitations

 Access, for example, doesn't do a great job with data security. It has other strengths. Keeping your data absolutely protected is not one of them. If that's a requirement for your Relational Database Application, find a different tool. Access is, and always be, my favored tool for creating Relational Database Applications. On the other hand, just like I wouldn't haul pigs to market in a Cadillac I am pretty sure there are better tools for a lot of tasks.

Access and I are going to be dancing partners for a long time to come, I hope.We just have to avoid stepping on each others toes.