设计数据库的正确方法(The right way in designing a database)

设计数据库的正确方法(The right way in designing a database)

我开始设计ERD，逻辑和物理图表的第一个MySQL项目。

我的一个朋友和我做了同样的项目。 我通过制作ERD然后规范化来开始我的数据库计划。

但是，他使用关系数据库图表，他在制作ERD之前首先设计接口和其他部分。 例如，他只将“堆栈”写入列phonenumbers，而不是制作“帮助表”。他说最好先制作接口，然后制作ERD。

在您看来，我们中哪一个更好地执行此计划？

I started my first MySQL project designing the ERD, logical and physical diagrams.

A friend of mine is making the same project as me. I started the plan of my databases by making an ERD and then normalizing.

However, he uses a relational database diagrams where he designs interfaces and other parts first before making the ERD. He for example writes "stack" only to the column phonenumbers, instead of making a "help-table".He says that it is best to make the interfaces first and then make the ERD.

Which one of us is doing the plan in your opinion better?

最满意答案

有人可以和很多人一起写过这本书。

然而，概括我通常会做的事情

分析您的数据并将其降低到第3范式。 这应该是非常公式化的。 根据数据的可能业务使用情况，决定数据是否应该非规范化以及在何处进行非规范化。 通常情况下，大多数数据库绝大多数都处于正常状态，只有少数关键例外。 这部分是经验和工艺的来源。 鉴于上述情况，创建可能需要的任何其他索引，或修改现有的主索引（应在阶段1中分配）。 根据需要为用户访问创建视图。 您需要的数字可能从none（如在简单的嵌入式应用程序中）到许多（因为没有允许的表的直接数据访问）。 创建您需要的任何程序，并可能触发（通常最好避免但适合审计目的）。

在实践中，当然这个过程要迭代得多，但是从数据到界面的一般设计路径都是正确的。 此外，在设计数据库时要记住，您希望稍后更改它，并尽可能尝试使其成为一项相当简单的任务，这也是一个好主意。

One could, and many people have, written a book on this.

However to generalise what I would generally do is

Analyse your data and reduce it down to 3rd normal form. This should be pretty formulaic to accomplish. In light of likely business use of the data decide if and where data should be denormalized. Typically most databases will be overwhelmingly in 3rd normal with a few critical exceptions. This part is where experience and craft come in. In light of the above create any additional indexes that may be necessary, or modify existing primary indexes (which should have been assigned in phase 1). Create views for user access as necessary. The number you require may vary from none (as in simple embedded application) to many (as in no direct data access to tables allowed). Create any procedures you need, and possibly triggers (generally best avoided but appropriate for audit purposes).

In practice of course the process is considerably more iterative, but the general design path from data to interface holds true. Also it's a good idea when designing a database to keep in mind that you will want to change it later and try if possible to make that a reasonably straightforward task.