更改数据库中记录状态的最佳实践是什么 [英] What is the best practice for changing status of a record in database

问题描述

我的应用程序类似于订单跟踪应用程序。

My application is something like order tracking application.

订单保存在订单表中，可容纳300件商品。如果我们从第一步开始处理整个项目，那么我可以放置一个状态字段，告诉该订单在第一步中，以跟踪订单并将其用于后续步骤。

An order is saved in a order table for 300 items. If we process whole items from 1st step then I can place a field of status telling that this order is in 1st step to track the orders and make them available for next steps.

但是，如果第一步的机器仅能处理150个项目，那么我必须指出，该订单中的150个项目在步骤2中，而150个项目在步骤1中。为此，我正计划有每个步骤的表和将数据保存到相应的表中。

But If machine of step one has capacity of processing 150 items only then I have to mention that 150 items of that order are in step 2 and 150 items are in step 1. And for this I am planning to have tables of each step and save data to respective table.

订单表：订单= 001，项目= 300 ...

Order Table: order = 001, items=300 ...

步骤1表：订单= 001，项目= 150 ...

Step 1 table: order = 001, items = 150 ...

步骤2表：订单= 001，项目= 150 ...

Step 2 table: order = 001, items = 150 ...

以此类推

我的困惑仅在于，无论我做对与否。许多用户可能会遇到这种情况，他们如何处理？我是对了还是只是超载了数据库。

My confusion is only this that whether I am doing it right or not. This could be case with many users and how they handle that? Am I doing it right or just overloading the database.

表：
下面是我制作的表结构。 Lot，LotOrder和lotorderdetail表与订单相关，而WindingMain和Dyeing是步骤。

Tables: Below is my tables structure, that I have made. Lot, LotOrder and lotorderdetail tables are related to orders and WindingMain and Dyeing are steps.

推荐答案

重新操作

订单表：订单= 001，商品= 300 ...

步骤1表：订单= 001，项目= 150 ...

步骤2表：订单= 001，项目= 150 ...

Order Table: order = 001, items=300 ...
Step 1 table: order = 001, items = 150 ...
Step 2 table: order = 001, items = 150 ...

您当前的设计以某些方式代表了某些情况。但是用一张桌子将步骤作为一列是没有问题的：

Your current design represents certain situations in certain ways. But there is no problem having one table with step as a column:

// order [order] is for [items] items ...
Order(order,items,...)

// order [order] has [items] items in step [step]
Step(order,items,step...)

这样做的好处是，您的查询现在不仅限于使用文字步骤编号或使用元数据处理多个步骤。

This has the benefit that now your queries are not limited to either using literal step numbers or using metadata to deal with multiple steps.

重新使用用户

如果用户下订单，则应添加另一列：

If orders are placed by users then you should add another column:

// order [order] placed by user [user] is for [items] items ...
Order(user,order,items,...)

// order [order] placed by user [user] has [items] items in step [step]
Step(user,order,items,step,...)

通过多列或多行，不会重载 DBMS。那是他们的目的。

There's no "overloading" a DBMS by having many columns or many rows. That is what they are designed for. If anything, most have more have space and time limitations for a large number of tables rather than columns or rows.

PS重新查找和存储；如果有的话，大多数都对大量表而不是列或行有空间和时间的限制。判断设计

每个表的意思是谓词：由列参数化的语句名称。例如上面的代码注释。行加谓词给出了一个命题：一条语句。构成真实命题的行会出现在表格中。我们必须找到足够的谓词，每个谓词都有一个基表，以表达与应用程序情况相关的所有内容。

Every table has a meaning that is a predicate: a statement parameterized by column names. Eg the code comments above. A row plus a predicate gives a proposition: a statement. The rows that make a true proposition go in the table. We must find sufficient predicates each with a base table to express everything relevant about an application situation.

• 如果多个谓词/表的区别仅在于公用值，则可以添加一个参数/列并使用单个谓词/表


• 如果可以使用更简单的谓词的AND / JOIN来更清楚地表示复杂的谓词/表，则可以您可以拆分谓词/表


• 如果您有一个或多个包含相同表达式的谓词，则可以定义另一个谓词来更简单地表达原始谓词


• 您可以定义视图和计算列，以避免冗余和相互矛盾的数据

• if multiple predicates/tables differ only by a common value then you can add a parameter/column and use a single predicate/table
• if a complex predicate/table can be more clearly expressed in terms of the AND/JOIN of simpler ones then you can split up the predicates/tables
• if you have one or more predicates containing the same expression then you can define another predicate to express the originals more simply
• you can define views and computed columns to avoid redundant and contradictory data

让我们集中注意订单，项目和条件。请注意，仍然很难查询已一步或未处理（或已完成）的零件：

Let's focus on just order, items and condition. Note that it is still difficult to query about parts that are either in a step or unprocessed (or finished):

//    order [order] has [items] items in step [condition]
   OR order [order] has [items] items unprocessed AND [condition] = unprocessed
Condition(order,items,condition)

或者能够在所有条件下进行算术运算，而不仅是步骤：

Or to be able to do arithmetic on all conditions, not just steps:

//     order [order] has [items] items in step [state]
   OR order [order] has [items] items unprocessed AND [state] = 0
State(order,items,state)

您可以做的另一件事是定义状态在条件和步骤方面的含义：

Another thing you can do is define what state means in terms of conditions and steps:

//  item [item] is in state [state] means
        item [item] is in step [state]
    OR item [item] is unprocessed AND [state] = 0

// order [order] has [items] items in state [state]
State(order,items,state)

您还可以通过视图定义状态。它的定义查询表达式用于一个表，该表的谓词恰好是上述定义的含义。

You can also define State by a view. Its defining query expression is for a table whose predicate is exactly the meaning of the definition above.

权衡：您可以为未处理项与处理中项建立单独的表，其中查询条件比较困难。或者，您可以为未处理和处理中的项目创建一个表，但定义更为复杂。

The tradeoff: You can have separate tables for unprocessed vs in-process items where querying about condition is more difficult. Or you can have one table for unprocessed and in-process items but the definitions are more complicated.

这篇关于更改数据库中记录状态的最佳实践是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！