4

As far as I know, the single responsibility principle concerns functions, classes and modules. Now I am designing a database and I wonder if this principle should be applied to relational tables.

Concrete example: I see lot of similar design cases as the following table shows:

CREATE TABLE Units (
  UnitId TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  UnitName VARCHAR(10) NOT NULL,
  DateEntered DATETIME NOT NULL,
  DateUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (UnitId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

My question is about that DateEntered column: is it Ok to have it in Units table or not? I mean does not this tell us somehow that an instance (row) of the Units table should be aware of the date where it entered to the shop? To put it bluntly: should we apply SRP on the physical relational tables level too? Otherwise, does SRP affect the relational tables in anyway?

|improve this question
  • 1
    Data doesn't have responsibilities. The code that handles it does. I would say no, although something similar probably should be applied to database design. But not the way you think about it here. – Kilves Feb 27 '19 at 14:25
  • 1
    I'd say not directly (not in the details design of the DB itself). SRP is about code, about structuring it to accommodate change over time. What a "single responsibility" (or "doing one thing") is depends on the domain and on how the software evolves, so it's not something you are necessarily going to get right initially - you have to course-correct as you develop and as your understanding of the domain and change patterns grows. So as you restructure code, sometimes you may decide that the current structure of pers. data doesn't support the business needs well. There is an interplay of sorts. – Filip Milovanović Feb 27 '19 at 17:56
10

The single responsibility principle is a general design heuristic, but relational database theory has something better: Normal Forms. Entity-Relationship Modelling can be used to develop these data models.

There are many normal forms with different guarantees and (mathematical) properties. In general, higher normal forms deduplicate data and spread it across multiple tables. This increases flexibility and can be useful if the data frequently changes, but is less convenient to query. Therefore, it is common in practice to deliberately use denormalized data in some circumstances (OLAP, data warehousing, NoSQL).

The table definition you have shown does not seem problematic. Because every Unit has exactly one DateEntered (there is a functional dependency UnitId → DateEntered) it is fine to keep it in the same table. Anything else (such as having two tables that both use the same UnitID as primary key) would be more complicated.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.