When need to store the recorded statistics as a column?



  • 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)



  • 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2