When need to store the recorded statistics as a column?

All we need is an easy explanation of the problem, so here it is.

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply