I'm wondering about best practise to keep a database as tidy as possible. The database is postgresql accessed by express.js/node. It is for a kids chores app that I'm working on and it has the following schema:
我想知道尽可能保持数据库整洁的最佳实践。数据库是express.js/node访问的postgresql。这是我正在开发的一款儿童家务应用，它有以下模式:
CHILDREN
id
name
points
rate
created_at
updated_at
user_id
TASKS
id
description
value
days (boolean array - eg. [0,0,0,0,0,0,0])
periods (boolean array - eg. [0,0])
created_at
updated_at
user_id
FINISHED TASKS
id
task_id
child_id
completed (boolean)
created_at
updated_at
period (boolean)
day (int (0-6))
For every individual finished task a row is created in the database. With only 400 children doing chores in there, there are already around 800 rows being added each day to the FINISHED TASKS table.
对于每个完成的任务，都在数据库中创建一行。在那里，只有400个孩子在做家务，每天都有大约800行被添加到完成的任务表中。
I have two questions:
我有两个问题:
- Is there a more efficient way of storing
FINISHED TASKSeither for a full day per child or similar? - 是否有一种更有效的方法将完成的任务存储在每个孩子身上一整天或类似的情况下?
- With scale I'm going to end up with potentially tens of thousands of rows per day - is this acceptable for an app like this?
- 有了规模，我每天可能会有成千上万行——这对于这样的应用来说是可以接受的吗?
1 个解决方案
#1
2
Having a child table related to a task table through an intermediate bridge table is the common way of doing this. My experience with large hospital applications is that once tables start to have millions of rows and performance is degrading, the applications typically archive the "finished tasks" into a separate archive table. You would maybe end up with two tables, one called 'active tasks' that contains tasks where 'completed' is false and once the task is finished, the row is moved into the archived 'finished tasks' table.
通过中间桥接表拥有与任务表相关的子表是实现此目的的常用方法。我对大型医院应用程序的经验是，一旦表开始有数百万行和性能下降，应用程序通常将“完成的任务”归档到一个单独的归档表中。您可能会得到两个表，一个叫做“活动任务”，其中包含“已完成”为假的任务，一旦任务完成，行就被移动到归档的“已完成任务”表中。
Depending on how much effort you want to put into future proofing the application, this could be done now to prevent having to revisit this.
根据您希望在将来对应用程序进行验证时付出多少努力，现在可以进行此操作，以避免再次访问该应用程序。
#1
2
Having a child table related to a task table through an intermediate bridge table is the common way of doing this. My experience with large hospital applications is that once tables start to have millions of rows and performance is degrading, the applications typically archive the "finished tasks" into a separate archive table. You would maybe end up with two tables, one called 'active tasks' that contains tasks where 'completed' is false and once the task is finished, the row is moved into the archived 'finished tasks' table.
通过中间桥接表拥有与任务表相关的子表是实现此目的的常用方法。我对大型医院应用程序的经验是，一旦表开始有数百万行和性能下降，应用程序通常将“完成的任务”归档到一个单独的归档表中。您可能会得到两个表，一个叫做“活动任务”，其中包含“已完成”为假的任务，一旦任务完成，行就被移动到归档的“已完成任务”表中。
Depending on how much effort you want to put into future proofing the application, this could be done now to prevent having to revisit this.
根据您希望在将来对应用程序进行验证时付出多少努力，现在可以进行此操作，以避免再次访问该应用程序。