When need to store the recorded statistics as a column?

The question:

I have a table department

create table department(
  id int UNSIGNED NOT_NULL AUTO_INCREMENT,
  name varchar(30),
  person_num int UNSIGNED,
  PRIMARY KEY(id)
)

each deparment has some group

create table group(
  id int UNSIGNED NOT_NULL AUTO_INCREMENT,
  name varchar(30),
  dept_id int UNSIGNED comment 'the department this group belong to',
  person_num int UNSIGNED,
  PRIMARY KEY(id)
)

each group has many employee

create table employee(
  id int UNSIGNED NOT_NULL AUTO_INCREMENT,
  fullname varchar(50),
  group_id int UNSIGNED,
  dpet_id int UNSIGNED,
  PRIMARY KEY(id)
)

Departments and teams have a person_num column, however this can be queried by employee.

My question is, under what circumstances do not use the number of employees as the person_num column, but use a table query to get the number of employees

(especially when I only want to see department or group information, and display the number of employee at the same time)

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

If you have a million employees in the department and/or the number of employees is changing many times an hour, then it may be worth it to have the count stored in the table or some other table.

Otherwise, having redundant information in a database is a no-no. The problem is with keeping it “correct”. Triggers and generated columns are semi-acceptable ways to bury the redundancy in a semi-safe way. I don’t like triggers because when you forget about the trigger, the queries become un-understandable. Generated columns don’t apply in your example.

Stored procedures are another technique, but you have to make sure no one sneaks around them to modify the table without modifying the derived information (person_num).

Performance becomes mandatory in huge tables. Data Warehousing would not survive without Summary Tables.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment