设计数据库时最重要的考虑因素是什么?[英] What are the most important considerations when designing a database?






1)您是在寻找像繁忙的网站这样的高交易率,还是像小型公司HR System


2)是安全性的一个大问题 - 您是处理个人详细信息还是财务数据.还是只是产品目录





7)您是否需要查看Hot Fail Over的企业集群,或者只是正常托管

8)如何对数据库进行招标,在大多数数据库项目中,95%的工作都为用户及其应用程序开发,DB Admin被忘记了

9)DB Admin,以前包括备份,对其他系统的更改,集成到其他系统,数据加载






要用一些牙齿给您一个答案,让我们以"车辆"实体为例.车辆有多个轮子.您有一个关键的决定,让他们知道车辆将附有多个车轮.您有2个选择 - 您可以将"车轮"制作为单独的实体,也可以使"车轮数"成为"车辆"实体中的整数字段.








  • 主键都以idtableName开头
  • 桌子名称是pascal
  • 外键都是tableNameId
  • ext ...






I would like to know from the experienced programmers what they consider to be the most important considerations when designing a new database.


First off and most important learn and understand the business domain.

1) Are you looking at a high transaction rate like a busy web site, or low use like a a small company HR system

2) Is security a big issue - are you handling personal details, or financial data. Or is it just a product catalogue

3) Will your users be doing many updates/inserts or is it mainly read only

4) How many users, what are the usage patterns (peak load or evenly distributed)

5) Do you need 24x7, 16x5 or other uptime, 24x7 is much harder to do as you have no down time for maintenance

6) How big is the DB going to go? If it's really big you'll have to design your tables to take account of that and/or partition

7) Do you need to look at enterprise cluster with hot fail over, or just normal hosting

8) How will the DB be adminstered, in most DB projects 95% of the effort is spent developing for the users and their applications, DB admin is forgotten

9) DB Admin, from previous includes backups, changes to other systems, integration to other systems, data loading

10) Actually Data loading and using existing data is another big issue in its own right.

That's it for a start


The database is secondary to your business process design and should cleanly support your business process in a direct and simple way. You will gain far more benefit from a well-formed, clean, entity model than you will from an index here and there. So once your process is defined, you take it and split it up into "entities" as cleanly as possible with relations that make sense. Once you know your entities, they translate into database tables.

One of the most important things to do is to not overarchitect.

To give you an answer with some teeth, let's take a "vehicle" entity as an example. A vehicle has multiple wheels. You have a critical decision to make knowing that there will be multiple wheels attached to the vehicle. You have 2 choices to make - You can make "wheels" a separate entity, or you can make "number of wheels" an integer field in the "Vehicle" entity.

If you absolutely know that you will need to store lots of changing information about each wheel, then create a "Wheel" entity. You now have a relationship between entities (the car and the wheel).

If not, a simple field will do just fine.

Thinking through these critical decisions and making things as simple as possible is by far the most important thing for me when designing a database. It can make the difference between things being really easy and really difficult when you build the next layer(s) of your application.


1 - Consistency

Over time your database will change and other people will need to work with it. Do yourself and them a favor and make sure that the structures are named in such a way that any reasonable person with basic domain knowledge will be able to anticipate the contents of the table. Take the time to write down (could be a simple as notepad) some basic constructs that you use.


  • Primary keys all start with IdTableName
  • Casing of table names is Pascal
  • Foreign keys are all TableNameId
  • ext...

Whether you choose to use underscores or not (substitute any other conversion for underscores) doesn't really matter at the end of the day as long as you are consistent in the way that you use or don't use them.

Your database is the last line of defense for data integrity. Do all of your data access through stored procedures and enforce the integrity of the data by using check constraints, foreign keys and so on. Type the data correctly, don't use VARCHAR(50) when CHAR(5) is more specific and accurate.

Someone else mentioned something about keeping it simple. Last but not least don't build something because you "think" you will need it next month. Things change quickly and you will end up doing more maintenance on stuff you "thought" you were going to use rather than things that you are using if you fill your database will stuff that serves no purpose.