如何轻松设计DB表/架构？

Is there a simple method to decide on what fields and indexes are needed for each table in an app you design?

是否有一种简单的方法来确定您设计的应用程序中每个表需要哪些字段和索引?

For example, if it is a webapp that simply lets people create lists (any number of lists, and users can create "things to do" list or "shopping" list), and the user can assign other users to edit the list, and whether the list is viewable publicly or to only certain users, how can the tables be design so that it is very accurate and designed quickly? What about the indexes?

例如,如果它是一个webapp,它只是让人们创建列表(任意数量的列表,用户可以创建“要做的事情”列表或“购物”列表),并且用户可以指定其他用户编辑列表,以及无论是公开查看列表还是仅查看某些用户,如何设计表格以使其非常准确并快速设计?索引怎么样?

I did that in college and then revisited the question some time ago and have a method, but would like to find out if there are standard and good ways to do it out in the field.

我在大学时就这样做了,然后在前一段时间重新审视了这个问题并提出了一个方法,但是想知道是否有标准和好的方法可以在现场完成。

7 个解决方案

#1

Database design is hard ...

数据库设计很难......

As with many things in life, it's a series of tradeoffs. The first thing you need to decide is what DBMS you will use, (MySQL, SQL Server, Oracle, PostgreSQL, one of the "Object-oriented" databases, etc.

与生活中的许多事情一样,这是一系列的权衡。您需要决定的第一件事是您将使用的DBMS(MySQL,SQL Server,Oracle,PostgreSQL,“面向对象”数据库之一等)。

Then you need to decide on normalization v. insane numbers of JOINs to get to your data. Questions like "how much logic will I implement in triggers, stored procedures, in app code, etc" need to be addressed.

然后你需要决定标准化和疯狂的JOIN数量来获取你的数据。需要解决诸如“我将在触发器,存储过程,应用程序代码中实现多少逻辑”等问题。

There is no "Quick'n'Easy" way to design anything but the most trivial of databases.

除了最简单的数据库之外,没有“Quick'n'Easy”方式来设计任何东西。

'Course, that's just my experience. YMWV.

“当然,这只是我的经验。 YMWV。

#2

it is beyond the scope of this answer to fully explain database design

完全解释数据库设计超出了这个答案的范围

I generally break my design into three parts (part 1 and 2 happen up front, while 3 is usually near the project end)
1) create the tables based on relationships (parent/child/etc)
2) create fields based on content (parent has x atributes, etc)
3) create indexes last based on how you select data from your tables

我通常将我的设计分为三个部分(第1部分和第2部分在前面发生,而3部分通常在项目结束附近)1)根据关系创建表格(父/子/等)2)根据内容创建字段(父级有x个属性等)3)根据你从表中选择数据的方式最后创建索引

#3

Haven't heard of any formal approaches to this problem but there are rules of thumb. All nouns and business objects become tables, normalized of course. And I'd think the attributes sort of speak for themselves. I guess?

没有听说过这个问题的任何正式方法,但有经验法则。所有名词和业务对象都成为表格,当然也是标准化的。我认为这些属性可以说明一切。我猜?

As for indexes, it just comes with working with the data. Any column that's joined off of deserves an index (maybe even clustered). It's very... depends. But there are patterns. But other than optimizing for joins, many indexes are directly related to how the data is used, and this isn't something that can be provided by rule of thumb. Like if you look up users by pk and elsewhere by last_name, last_name deserves an index.

至于索引,它只是处理数据。任何加入的列都应该有一个索引(甚至可能是聚集的)。这非常......取决于。但有模式。但除了优化连接之外,许多索引与数据的使用方式直接相关,而且这不是可以通过经验法则提供的。就像你通过pk和其他地方的last_name查找用户一样,last_name值得一个索引。

#4

I think the solution is a subjective one. When I have to design tables I look at the Java object that will represent that particular data model and go from there. You'll find a lot of frameworks (Django, CakePHP, RoR) have you develop the model and the frameworks will build the corresponding tables.

我认为解决方案是主观的。当我必须设计表时,我会查看代表该特定数据模型的Java对象并从那里开始。你会发现很多框架(Django,CakePHP,RoR)你开发模型,框架将构建相应的表。

So I would suggest evaluating what functionality and data you need to store and develop your tables from that. Also look into whether the tool set you have at your disposal offers to generate the tables for you from the object structure.

因此,我建议您评估从中存储和开发表所需的功能和数据。还可以查看您所拥有的工具集是否可以从对象结构中为您生成表格。

#5

I would go for the straightforward (almost) normalized design:

我会选择简单(几乎)标准化的设计:

CREATE TABLE lists (
                    listid serial, 
                    name varchar, 
                    ownerid int references users(userid)
                   )

CREATE TABLE list_items (
                         listid int references lists(listid), 
                         value varchar, 
                         date datetime
                        ) 

CREATE TABLE permissions (
                          permissionid serial, 
                          description varchar,
                         )

CREATE TABLE list_permissions (
                               listid int references lists(listid),
                               permissionid int references permissions(permissionid)
                               userid int references users(userid)
                              )
CREATE TABLE users (
                     userid serial,
                     name varchar
                   )

Which indexes to create would depend on what are the actual most used queries and how are they performing. For instance, if you query a lot on the lists and list_items (likely) you'd want an index on listid and on name, if you'll be searching by name.

要创建哪些索引取决于实际使用最多的查询以及它们的执行情况。例如,如果您在列表和list_items上查询很多(可能),那么您希望在listid和名称上使用索引,如果您要按名称搜索。

Just some ideas. Hope they're helpful.

只是一些想法。希望他们有所帮助。

#6

I'd try not to lock yourself in if you're still trying to see what works.

如果你还在试图看看哪些有效,我会尽量不锁定自己。

Just from your description, you'd want a table for your users' information, as well as:

仅从您的描述中,您需要一个表格来显示用户的信息,以及:

tbl_lists:
    ID_list (primary key)
    UserID (foreign key to list owner)
    ListName

tbl_listItems:
    ID_listItem (primary key)
    ListID (foreign key to list)
    ItemDescription

tbl_permissions:
    ID_permission (primary key)
    ListID
    UserID (foreign key to user you're granting permission to)
    PermissionTypeID (what kind of permission)

tbl_permissionTypes:
    ID_permissionType (primary key)
    Description ("can view", "can edit", etc.)

The more flexible you can make things while you're designing, the better. You can optimize later.

在设计时,您可以更灵活地制作东西,效果更好。您可以稍后进行优化。

#7

If you want to keep things very simple and are not too concerned with normalizing. You could create one big table that stores the main object your webapp is based around, ex: lists, and have other smaller supporting tables link to the big table, ex: tbl_listType, tbl_permission, tbl_list_items).

如果你想让事情变得非常简单并且不太关心规范化。您可以创建一个存储webapp所基于的主要对象的大表,例如:列表,并将其他较小的支持表链接到大表,例如:tbl_listType,tbl_permission,tbl_list_items)。

Then when you write queries, you almost certainly include the main table and you can link in other supporting tables for more granular details.

然后,当您编写查询时,您几乎肯定会包含主表,您可以链接到其他支持表中以获得更详细的信息。

#1