In our DB (on SQL Server 2005) we have a "Customers" table, whose primary key is Client Code, a surrogate, bigint IDENTITY(1,1) key; the table is referenced by a number of other tables in our DB thru a foreign key.
在我们的数据库(在SQL Server 2005上),我们有一个“客户”表,其主键是客户端代码,代理,bigint IDENTITY(1,1)键;该表由我们的DB中的许多其他表通过外键引用。
A new CR implementation we are estimating would require us to change ID column type to varchar, Client Code generation algorithm being shifted from a simple numeric progression to a strict 2-char representation, with codes ranging from 01 to 99, then progressing like this:
我们估计的新CR实现将要求我们将ID列类型更改为varchar,客户端代码生成算法从简单的数字级数转换为严格的2-char表示,代码范围从01到99,然后进行如下所示:
1A -> 2A -> ... -> 9A -> 1B -> ... 9Z
I'm fairly new to database design, but I smell some serious problems here. First of all, what about this client code generation algorithm? What if I need a Client Code to go beyond 9Z code limit?
我对数据库设计还不熟悉,但我在这里闻到了一些严重的问题。首先,这个客户端代码生成算法怎么样?如果我需要客户端代码超出9Z代码限制怎么办?
The I have some question: would this change be feasible, the table being already filled with a fair amount of data, and referenced by multiple entities? If so, how would you approach this problem, and how would you implement Client Code generation?
我有一些问题:这种变化是否可行,表格已经填充了大量数据,并被多个实体引用?如果是这样,您将如何解决此问题,以及如何实现客户端代码生成?
4 个解决方案
#1
2
I would leave the primary key as it is and would create another key (unique) on the client code generated. I would do that anyway. It's always better to have a short number primary key instead of long char keys. In some situation you might prefer a GUID (for replication purposes) but a number int/bigint is alway preferable. You can read more here and here.
我会保留主键,并在生成的客户端代码上创建另一个键(唯一)。无论如何我会这样做。拥有一个短号主键而不是长char键总是更好。在某些情况下,您可能更喜欢GUID(出于复制目的),但总是优选int / bigint。你可以在这里和这里阅读更多。
#2
1
My biggest concern with what you are proposing is that you will be limited to 360 primary records. That seems like a small number.
我对您提出的建议最为关注的是,您将被限制在360个主要记录中。这似乎是一个小数字。
Performing the change is a multi-step operation. You need to create the new field in the core table and all its related tables.
执行更改是一个多步操作。您需要在核心表及其所有相关表中创建新字段。
To do an in-place update, you need to generate the code in the core table. Then you need to update all the related tables to have the code based on the old id. Then you need to add the foreign key constraint to all the related tables. Then you need to remove the old key field from all the related tables.
要进行就地更新,您需要在核心表中生成代码。然后,您需要更新所有相关表,以使代码基于旧ID。然后,您需要将外键约束添加到所有相关表。然后,您需要从所有相关表中删除旧的键字段。
We only did that in our development server. When we upgraded the live databases, we created a new database for each and copied the data over using a python script that queried the old database and inserted into the new database. I now update that script for every software upgrade so the core engine stays the same, but I can specify different tables or data modifications. I get the bonus of having a complete backup of the original database if something unexpected happens when upgrading production.
我们只在我们的开发服务器中做到了。当我们升级实时数据库时,我们为每个数据库创建了一个新数据库,并使用python脚本复制数据,该脚本查询旧数据库并插入新数据库。我现在为每个软件升级更新该脚本,因此核心引擎保持不变,但我可以指定不同的表或数据修改。如果在升级生产时出现意外情况,我会获得原始数据库的完整备份。
One strong argument in favor of a non-identity/guid code is that you want a human readable/memorable code and you need to be able to move records between two systems.
支持非身份/ guid代码的一个有力论据是,您需要一个人类可读/令人难忘的代码,并且您需要能够在两个系统之间移动记录。
Performance is not necessarily a concern in SQL Server 2005 and 2008. We recently went through a change where we moved from int ids everywhere to 7 or 8 character "friendly" record codes. We expected to see some kind of performance hit, but we in fact saw a performance improvement.
性能不一定是SQL Server 2005和2008中的一个问题。我们最近进行了一次更改,我们从int int移动到7或8个字符“友好”记录代码。我们预计会出现某种性能影响,但实际上我们看到了性能提升。
We also found that we needed a way to quickly generate a code. Our codes have two parts, a 3 character alpha prefix and a 4 or 5 digit suffix. Once we had a large number of codes (15000-20000) we were finding it to slow to parse the code into prefix and suffix and find the lowest unused code (it took several seconds). Because of this, we also store the prefix and the suffix separately (in the primary key table) so that we can quickly find the next available lowest code with a particular prefix. The cached prefix and suffix made the search almost fee.
我们还发现我们需要一种快速生成代码的方法。我们的代码有两部分,一个3个字符的alpha前缀和一个4或5位数的后缀。一旦我们有了大量代码(15000-20000),我们发现它很慢将代码解析为前缀和后缀,并找到最低的未使用代码(需要几秒钟)。因此,我们还单独存储前缀和后缀(在主键表中),以便我们可以快速找到具有特定前缀的下一个可用的最低代码。缓存的前缀和后缀几乎使搜索费用。
We allow changing of the codes and they changed values propagate by cascade update rules on the foreign key relationship. We keep an identity key on the core code table to simplify the update of the code.
我们允许更改代码,并且它们通过级联更新规则在外键关系上传播更改的值。我们在核心代码表上保留一个标识密钥,以简化代码的更新。
We don't use an ORM, so I don't know what specific things to be aware of with that. We also have on the order of 60,000 primary keys in our biggest instance, but have hundreds of tables related and tables with millions of related values to the code table.
我们不使用ORM,因此我不知道具体的事情需要注意什么。在我们最大的实例中,我们还有大约60,000个主键,但是有数百个表相关,表中包含数百万个与代码表相关的值。
One big advantage that we got was, in many cases, we did not need to do a join to perform operations. Everywhere in the software the user references things by friendly code. We don't have to do a lookup of the int ID (or a join) to perform certain operations.
我们得到的一大优势是,在许多情况下,我们不需要进行连接来执行操作。软件中的任何地方都通过友好代码引用事物。我们不必查找int ID(或连接)来执行某些操作。
#3
0
The new code generation algorithm isn't worth thinking about. You can write a program to generate all possible codes in just a few lines of code. Put them in a table, and you're practically done. You just need to write a function to return the smallest one not yet used. Here's a Ruby program that will give you all the possible codes.
新的代码生成算法不值得考虑。您可以编写一个程序,只需几行代码即可生成所有可能的代码。把它们放在一张桌子里,你几乎已经完成了。您只需要编写一个函数来返回尚未使用的最小函数。这是一个Ruby程序,它将为您提供所有可能的代码。
# test.rb -- generate a peculiar sequence of two-character codes.
i = 1
('A'..'Z').each do |c|
(1..9).each do |n|
printf("'%d%s', %d\n", n, c, i)
i += 1
end
end
The program will create a CSV file that you should be able to import easily into a table. You need two columns to control the sort order. The new values don't naturally sort the way your requirements specify.
该程序将创建一个CSV文件,您应该可以轻松导入到表中。您需要两列来控制排序顺序。新值不会按照您的要求指定的方式自然排序。
I'd be more concerned about the range than the algorithm. If you're right about the requirement, you're limited to 234 client codes. If you're wrong, and the range extends from "1A" to "ZZ", you're limited to less than a thousand.
我会更关心范围而不是算法。如果您对该要求是正确的,则您只能使用234个客户端代码。如果你错了,范围从“1A”延伸到“ZZ”,你的数量不到一千。
To implement this requirement in an existing table, you need to follow a careful procedure. I'd try it several times in a test environment before trying it on a production table. (This is just a sketch. There are a lot of details.)
要在现有表中实现此要求,您需要遵循一个谨慎的过程。在生产台上尝试之前,我会在测试环境中多次尝试。 (这只是一个草图。有很多细节。)
- Create and populate a two-column table to map existing bigints to the new CHAR(2).
- Create new CHAR(2) columns in all the tables that need them.
- Update all the new CHAR(2) columns.
- Create new
NOT NULL UNIQUEorPRIMARY KEYconstraints and newFOREIGN KEYconstraints on the new CHAR(2) columns. - Rewrite user interface code (?) to target the new columns. (Might not be necessary if you rename the new CHAR(2) and old BIGINT columns.)
- Set a target date to drop the old BIGINT columns and constraints.
- And so on.
创建并填充两列表以将现有bigint映射到新CHAR(2)。
在需要它们的所有表中创建新的CHAR(2)列。
更新所有新的CHAR(2)列。
在新的CHAR(2)列上创建新的NOT NULL UNIQUE或PRIMARY KEY约束和新的FOREIGN KEY约束。
重写用户界面代码(?)以定位新列。 (如果重命名新的CHAR(2)和旧的BIGINT列,则可能没有必要。)
设置目标日期以删除旧的BIGINT列和约束。
等等。
#4
0
Not really addressing whether this is a good idea or not, but you can change your foreign keys to cascade the updates. What will happen once you're done doing that is that when you update the primary key in the parent table, the corresponding key in the child table will be updated accordingly.
没有真正解决这是否是一个好主意,但您可以更改外键以级联更新。完成此操作后将会发生的情况是,当您更新父表中的主键时,子表中的相应键将相应更新。
#1
2
I would leave the primary key as it is and would create another key (unique) on the client code generated. I would do that anyway. It's always better to have a short number primary key instead of long char keys. In some situation you might prefer a GUID (for replication purposes) but a number int/bigint is alway preferable. You can read more here and here.
我会保留主键,并在生成的客户端代码上创建另一个键(唯一)。无论如何我会这样做。拥有一个短号主键而不是长char键总是更好。在某些情况下,您可能更喜欢GUID(出于复制目的),但总是优选int / bigint。你可以在这里和这里阅读更多。
#2
1
My biggest concern with what you are proposing is that you will be limited to 360 primary records. That seems like a small number.
我对您提出的建议最为关注的是,您将被限制在360个主要记录中。这似乎是一个小数字。
Performing the change is a multi-step operation. You need to create the new field in the core table and all its related tables.
执行更改是一个多步操作。您需要在核心表及其所有相关表中创建新字段。
To do an in-place update, you need to generate the code in the core table. Then you need to update all the related tables to have the code based on the old id. Then you need to add the foreign key constraint to all the related tables. Then you need to remove the old key field from all the related tables.
要进行就地更新,您需要在核心表中生成代码。然后,您需要更新所有相关表,以使代码基于旧ID。然后,您需要将外键约束添加到所有相关表。然后,您需要从所有相关表中删除旧的键字段。
We only did that in our development server. When we upgraded the live databases, we created a new database for each and copied the data over using a python script that queried the old database and inserted into the new database. I now update that script for every software upgrade so the core engine stays the same, but I can specify different tables or data modifications. I get the bonus of having a complete backup of the original database if something unexpected happens when upgrading production.
我们只在我们的开发服务器中做到了。当我们升级实时数据库时,我们为每个数据库创建了一个新数据库,并使用python脚本复制数据,该脚本查询旧数据库并插入新数据库。我现在为每个软件升级更新该脚本,因此核心引擎保持不变,但我可以指定不同的表或数据修改。如果在升级生产时出现意外情况,我会获得原始数据库的完整备份。
One strong argument in favor of a non-identity/guid code is that you want a human readable/memorable code and you need to be able to move records between two systems.
支持非身份/ guid代码的一个有力论据是,您需要一个人类可读/令人难忘的代码,并且您需要能够在两个系统之间移动记录。
Performance is not necessarily a concern in SQL Server 2005 and 2008. We recently went through a change where we moved from int ids everywhere to 7 or 8 character "friendly" record codes. We expected to see some kind of performance hit, but we in fact saw a performance improvement.
性能不一定是SQL Server 2005和2008中的一个问题。我们最近进行了一次更改,我们从int int移动到7或8个字符“友好”记录代码。我们预计会出现某种性能影响,但实际上我们看到了性能提升。
We also found that we needed a way to quickly generate a code. Our codes have two parts, a 3 character alpha prefix and a 4 or 5 digit suffix. Once we had a large number of codes (15000-20000) we were finding it to slow to parse the code into prefix and suffix and find the lowest unused code (it took several seconds). Because of this, we also store the prefix and the suffix separately (in the primary key table) so that we can quickly find the next available lowest code with a particular prefix. The cached prefix and suffix made the search almost fee.
我们还发现我们需要一种快速生成代码的方法。我们的代码有两部分,一个3个字符的alpha前缀和一个4或5位数的后缀。一旦我们有了大量代码(15000-20000),我们发现它很慢将代码解析为前缀和后缀,并找到最低的未使用代码(需要几秒钟)。因此,我们还单独存储前缀和后缀(在主键表中),以便我们可以快速找到具有特定前缀的下一个可用的最低代码。缓存的前缀和后缀几乎使搜索费用。
We allow changing of the codes and they changed values propagate by cascade update rules on the foreign key relationship. We keep an identity key on the core code table to simplify the update of the code.
我们允许更改代码,并且它们通过级联更新规则在外键关系上传播更改的值。我们在核心代码表上保留一个标识密钥,以简化代码的更新。
We don't use an ORM, so I don't know what specific things to be aware of with that. We also have on the order of 60,000 primary keys in our biggest instance, but have hundreds of tables related and tables with millions of related values to the code table.
我们不使用ORM,因此我不知道具体的事情需要注意什么。在我们最大的实例中,我们还有大约60,000个主键,但是有数百个表相关,表中包含数百万个与代码表相关的值。
One big advantage that we got was, in many cases, we did not need to do a join to perform operations. Everywhere in the software the user references things by friendly code. We don't have to do a lookup of the int ID (or a join) to perform certain operations.
我们得到的一大优势是,在许多情况下,我们不需要进行连接来执行操作。软件中的任何地方都通过友好代码引用事物。我们不必查找int ID(或连接)来执行某些操作。
#3
0
The new code generation algorithm isn't worth thinking about. You can write a program to generate all possible codes in just a few lines of code. Put them in a table, and you're practically done. You just need to write a function to return the smallest one not yet used. Here's a Ruby program that will give you all the possible codes.
新的代码生成算法不值得考虑。您可以编写一个程序,只需几行代码即可生成所有可能的代码。把它们放在一张桌子里,你几乎已经完成了。您只需要编写一个函数来返回尚未使用的最小函数。这是一个Ruby程序,它将为您提供所有可能的代码。
# test.rb -- generate a peculiar sequence of two-character codes.
i = 1
('A'..'Z').each do |c|
(1..9).each do |n|
printf("'%d%s', %d\n", n, c, i)
i += 1
end
end
The program will create a CSV file that you should be able to import easily into a table. You need two columns to control the sort order. The new values don't naturally sort the way your requirements specify.
该程序将创建一个CSV文件,您应该可以轻松导入到表中。您需要两列来控制排序顺序。新值不会按照您的要求指定的方式自然排序。
I'd be more concerned about the range than the algorithm. If you're right about the requirement, you're limited to 234 client codes. If you're wrong, and the range extends from "1A" to "ZZ", you're limited to less than a thousand.
我会更关心范围而不是算法。如果您对该要求是正确的,则您只能使用234个客户端代码。如果你错了,范围从“1A”延伸到“ZZ”,你的数量不到一千。
To implement this requirement in an existing table, you need to follow a careful procedure. I'd try it several times in a test environment before trying it on a production table. (This is just a sketch. There are a lot of details.)
要在现有表中实现此要求,您需要遵循一个谨慎的过程。在生产台上尝试之前,我会在测试环境中多次尝试。 (这只是一个草图。有很多细节。)
- Create and populate a two-column table to map existing bigints to the new CHAR(2).
- Create new CHAR(2) columns in all the tables that need them.
- Update all the new CHAR(2) columns.
- Create new
NOT NULL UNIQUEorPRIMARY KEYconstraints and newFOREIGN KEYconstraints on the new CHAR(2) columns. - Rewrite user interface code (?) to target the new columns. (Might not be necessary if you rename the new CHAR(2) and old BIGINT columns.)
- Set a target date to drop the old BIGINT columns and constraints.
- And so on.
创建并填充两列表以将现有bigint映射到新CHAR(2)。
在需要它们的所有表中创建新的CHAR(2)列。
更新所有新的CHAR(2)列。
在新的CHAR(2)列上创建新的NOT NULL UNIQUE或PRIMARY KEY约束和新的FOREIGN KEY约束。
重写用户界面代码(?)以定位新列。 (如果重命名新的CHAR(2)和旧的BIGINT列,则可能没有必要。)
设置目标日期以删除旧的BIGINT列和约束。
等等。
#4
0
Not really addressing whether this is a good idea or not, but you can change your foreign keys to cascade the updates. What will happen once you're done doing that is that when you update the primary key in the parent table, the corresponding key in the child table will be updated accordingly.
没有真正解决这是否是一个好主意,但您可以更改外键以级联更新。完成此操作后将会发生的情况是,当您更新父表中的主键时,子表中的相应键将相应更新。