Database Management Systems

Single Instance Exception in Entity-Relationship Diagram Design

Entities in the ER (Entity Relationship) model are typically nouns, existing physically, digitally, or conceptually. It is often suggested that entities should only exist (particularly in regards to the design phase) if they will contain more than one instance. For example an Employees entity would conceivably have multiple employees. If it is known that there would only ever be one employee, and that the employee would never change, then logically any information about that employee could be kept in the Application domain, outside of the Database altogether.

The idea behind this concept is that executing queries on data which is static in nature is a resource waste.

However, I’ve observed that there is a fairly common and real-world exception to this multiple instance rule in ERD design, development, and final builds.That would be any database where the application is meant to be able to deploy variations of itself. An example would be something like a CMS Blog (e.g. WordPress, Joomla) or Wiki (e.g. XWiki, MediaWiki) or anything where the program instantiates differentiated versions of itself. There is nothing preventing other types of systems from utilizing such an entity, however I will only cover this specific (and pervasive) example.

So what is the Single Entity? In this instance, the one containing the major variables / properties of the deployed variation. Using a Blog as an example, this single entity would contain attributes such as Name of Blog, Timezone, Site Language, etc. You see this is an entity that will absolutely exist during and after implementation. So it should be accounted for in the Entity Relationship Diagram.

ERD Single Instance Entity
Part of ERD showing a Single Instance Entity ‘Settings’

While this is a case of the Application-environment driving the Database-environment, and some might see that as digital taboo, it really just changes the systems architecture. There’s nothing inherently bad about doing this, unless its implemented poorly.

This isn’t theory. This is actually how many of the aforementioned web-application frameworks operate. You can look at these databases right now and see that these settings are stored in the database, and not as settings in file (e.g. .cfg) Even once a deployment has occurred, there are plenty of reasons why some of these seemingly static/constant variables may need to be changed. While the site language may not change, there are certainly plenty of reasons for why the name of the site could change (even multiple times over the course of years). My own site has undergone such changes, and will likely continue to do so. The time zone may even require changing if the owners or the main operations of the organization were to move.

So respectfully, I conclude that an exception to not having entities exist in the ERD domain which can only exist as a single instance, can be made.

Leave a Reply