I work with an enterprise application and have picked up some tips for DB design
我使用企业应用程序并获得了一些数据库设计技巧
- All tables should have the following fields that helps in audit trail -
LastChangedBy,LastChanged,LastChangedPage - 所有表都应具有以下有助于审计跟踪的字段 - LastChangedBy，LastChanged，LastChangedPage
- All your stored procedures that have dynamic SQL should have the @
bDebugparameter. By default it's set to 0. If it's set to 1, print out the dynamic SQL statement and this will be really helpful in debugging. - 具有动态SQL的所有存储过程都应具有@bDebug参数。默认情况下，它设置为0.如果设置为1，则打印出动态SQL语句，这对调试非常有用。
- For CRUD SPs, have a way of partially updating the table. If your table has 10 fields and in one of the SP, you care about updating only 5 fields, have a layer of abstraction to do this.
- 对于CRUD SP，有一种部分更新表的方法。如果你的表有10个字段并且在一个SP中，你只关心更新5个字段，有一个抽象层来做到这一点。
Any other useful tips you can think of?
你能想到的任何其他有用的提示？
EDIT: Thanks for all the answers. I am still looking for an answer that can provide a link to tips/tricks/strategies for DB Design.
编辑：谢谢你的所有答案。我仍在寻找能够提供DB Design提示/技巧/策略链接的答案。
6 个解决方案
#1
4
For #1: Move to SQL Server 2008 and just turn on Change Data Capture. If you really need to keep detailed audit trails, this feature alone will justify the cost.
对于＃1：转到SQL Server 2008，然后启用“更改数据捕获”。如果您确实需要保留详细的审计跟踪，仅此功能就可以证明成本合理。
For #2: Any stored procedure with dynamic sql should automatically be put on double secret probation (ie: it's allowed, but has to go through multiple levels of code review to make sure there's not a better way to do it).
对于＃2：任何带有动态sql的存储过程都应该自动进行双重秘密试用（即：它是允许的，但必须经过多级代码审查才能确保没有更好的方法来执行此操作）。
#2
1
When it comes to the power of the web, its better never to delete anything. Therefore having a deletedOn date that you can just exclude those objects which have been "deleted" from your searches. This also helps frantic customers who accidently deleted their account. Obviously this shouldn't be used in every field.
谈到网络的力量，最好永远不要删除任何东西。因此，拥有一个已删除的日期，您可以排除那些已从搜索中“删除”的对象。这也有助于疯狂的客户意外删除他们的帐户。显然，这不应该用在每个领域。
#3
1
LastChangedBy etc. fields are pretty useless. If you really need an audit trail, you need separate audit tables that detail the changes and maintain an audit history. If you don't need an audit trail, the LastChangedBy etc. fields are just added work for no business value.
LastChangedBy等字段非常无用。如果您确实需要审计跟踪，则需要单独的审计表来详细说明更改并维护审计历史记录。如果您不需要审计跟踪，则LastChangedBy等字段只是添加了工作，没有业务价值。
#4
1
A couple of thoughts that immediately spring to mind when working with very large database (VLDB):
在使用超大型数据库（VLDB）时，会立即想到几个想法：
Should you implement table partitioning?
你应该实现表分区吗？
Large database tables, with millions of records, may benefit from table partitioning.
具有数百万条记录的大型数据库表可能受益于表分区。
- The availability of this SQL Server Feature is restricted to using the Enterprise Edition.
- 此SQL Server功能的可用性仅限于使用Enterprise Edition。
- The applicability is dependent on your platform hardware and the availability of an appropriate partitioning key within the table data.
- 适用性取决于您的平台硬件以及表数据中适当分区键的可用性。
What are my most frequently accessed tables?
我最常访问的表格是什么？
Consider separation by Filegroup i.e. place Customer table on one Filegroup and Transaction table on another. This permits SQL Server to create multiple threads for file access creating the possibility of sequential I/O.
考虑按文件组分离，即将Customer表放在另一个Filegroup和Transaction表上。这允许SQL Server为文件访问创建多个线程，从而创建顺序I / O的可能性。
Then subsequently consider the underlying physical disk structure, i.e. a separate LUN for each Filegroup.
然后考虑底层物理磁盘结构，即每个文件组的单独LUN。
Devise a Flexible Indexing Strategy
设计灵活的索引策略
You will no doubt already have an indexing strategy in mind however for VLDB platforms this should be frequently reviewed. As data volumes increase and data distribution changes so to may the execution plans for your data access queries. You should plan for the need to regularly review your indexing strategy.
毫无疑问，您已经考虑了索引策略，但对于VLDB平台，应经常对其进行审核。随着数据量的增加和数据分布的变化，可能会为您的数据访问查询执行计划。您应该计划定期检查索引策略的需求。
#5
0
In my opinion one would require CreatedBy and Created fields.
在我看来，需要CreatedBy和Created字段。
#6
0
Dates should be stored in Utc format and converted to local time at the client.
日期应以Utc格式存储，并在客户端转换为本地时间。
#1
4
For #1: Move to SQL Server 2008 and just turn on Change Data Capture. If you really need to keep detailed audit trails, this feature alone will justify the cost.
对于＃1：转到SQL Server 2008，然后启用“更改数据捕获”。如果您确实需要保留详细的审计跟踪，仅此功能就可以证明成本合理。
For #2: Any stored procedure with dynamic sql should automatically be put on double secret probation (ie: it's allowed, but has to go through multiple levels of code review to make sure there's not a better way to do it).
对于＃2：任何带有动态sql的存储过程都应该自动进行双重秘密试用（即：它是允许的，但必须经过多级代码审查才能确保没有更好的方法来执行此操作）。
#2
1
When it comes to the power of the web, its better never to delete anything. Therefore having a deletedOn date that you can just exclude those objects which have been "deleted" from your searches. This also helps frantic customers who accidently deleted their account. Obviously this shouldn't be used in every field.
谈到网络的力量，最好永远不要删除任何东西。因此，拥有一个已删除的日期，您可以排除那些已从搜索中“删除”的对象。这也有助于疯狂的客户意外删除他们的帐户。显然，这不应该用在每个领域。
#3
1
LastChangedBy etc. fields are pretty useless. If you really need an audit trail, you need separate audit tables that detail the changes and maintain an audit history. If you don't need an audit trail, the LastChangedBy etc. fields are just added work for no business value.
LastChangedBy等字段非常无用。如果您确实需要审计跟踪，则需要单独的审计表来详细说明更改并维护审计历史记录。如果您不需要审计跟踪，则LastChangedBy等字段只是添加了工作，没有业务价值。
#4
1
A couple of thoughts that immediately spring to mind when working with very large database (VLDB):
在使用超大型数据库（VLDB）时，会立即想到几个想法：
Should you implement table partitioning?
你应该实现表分区吗？
Large database tables, with millions of records, may benefit from table partitioning.
具有数百万条记录的大型数据库表可能受益于表分区。
- The availability of this SQL Server Feature is restricted to using the Enterprise Edition.
- 此SQL Server功能的可用性仅限于使用Enterprise Edition。
- The applicability is dependent on your platform hardware and the availability of an appropriate partitioning key within the table data.
- 适用性取决于您的平台硬件以及表数据中适当分区键的可用性。
What are my most frequently accessed tables?
我最常访问的表格是什么？
Consider separation by Filegroup i.e. place Customer table on one Filegroup and Transaction table on another. This permits SQL Server to create multiple threads for file access creating the possibility of sequential I/O.
考虑按文件组分离，即将Customer表放在另一个Filegroup和Transaction表上。这允许SQL Server为文件访问创建多个线程，从而创建顺序I / O的可能性。
Then subsequently consider the underlying physical disk structure, i.e. a separate LUN for each Filegroup.
然后考虑底层物理磁盘结构，即每个文件组的单独LUN。
Devise a Flexible Indexing Strategy
设计灵活的索引策略
You will no doubt already have an indexing strategy in mind however for VLDB platforms this should be frequently reviewed. As data volumes increase and data distribution changes so to may the execution plans for your data access queries. You should plan for the need to regularly review your indexing strategy.
毫无疑问，您已经考虑了索引策略，但对于VLDB平台，应经常对其进行审核。随着数据量的增加和数据分布的变化，可能会为您的数据访问查询执行计划。您应该计划定期检查索引策略的需求。
#5
0
In my opinion one would require CreatedBy and Created fields.
在我看来，需要CreatedBy和Created字段。
#6
0
Dates should be stored in Utc format and converted to local time at the client.
日期应以Utc格式存储，并在客户端转换为本地时间。