公告栏-数据库优化

第一部分

修订日期：09 Dec 10 01:00 EST

看了看你的DDL。好。我们需要退后一步，并首先组织您的数据库。这将解决您一半的问题（您的SQL将很简单；而且速度很快；索引更少；不需要临时表）。我想了一会儿，哈哈，您的专栏文章必须稳定，但没有机会。从头开始，好。看一下这个实体关系图（在正确确定ER之前，不要在数据模型上工作，即Entities，Relations
和Attributes ），然后检查它是否正确。

为此，请回答以下问题（简短回答即可）。这些问题正在澄清 实体和业务规则 。一般而言，如何理解数据库，尤其是数据是至关重要的。您已经走了很长的路要走，所以我们可以从那里拿走它。
我认为 ▶这篇文章◀ 可能对您有所帮助，以便了解应遵循的正式阶段；这是我们在这里短路。
最重要的是，完全而又完全地忘记了功能和任何编码要求。数据必须独立于应用程序而建模，就像数据一样。功能建模是另一门科学。首先得到一个权利；然后得到另一个权利；和两者一起演奏美妙的曲调。尝试将它们卡在一起；同时完成两个任务，他们甚至都不会组成郊区的车库乐队。

为简洁起见，为了方便阅读，我使用了“封闭和开放”部分；当关闭未清项目（讨论）时，我将使其简洁，然后将其移至“已结束”部分。保持编号，因为有时情况会再次困扰我们。您可能希望这样做，甚至删除您身边的讨论。

漂亮图片的链接位于结尾。

抱歉：无法编辑。子编号不一致

已解决的问题

users.bb_locations_csv是用户和位置之间的多对多关系：
- 这些元素中的每一个都应该是离散列，离散行中的条目
- 一个用户可以有多个位置，而一个位置可以有多个用户是多对多
- 阅读 ▶这篇文章◀ ，讨论如何治疗以及如何治疗
- 正如我所画的，在这个逻辑阶段，这只是一个::: n关系，您现在可以忘记它，当我们进入物理阶段时，它将被简单地提供。
- 相信我，我所提供的代码不会比
```
...WHERe IN ()
```
  您声明的目的复杂。
- 再次想想，如果我断了手指，您的打字速度会变慢，所以我最好不要
- 好的，您的应用是基于浏览器的，页面是动态的（我的建议是需要修改的静态页面）；继续使用复选框。
  。
users.bb_categories_csv是用户和类别之间的多对多关系
- 同上。
  。
已确认：没有用户，公告（bbs）不存在；用户发布公告，并开始整个周期；然后邀请回复和评分。

3.1确认：实际上只有一个布告栏，并且它在数据库中不作为事物存在。

3.2确认：该组织永远不会有多个公告栏，并且类别和类别均由类别表/功能充分处理

已删除。
已确认：公告和答复之间的区别在于，答复依赖于公告的存在，它们没有标题，也没有按位置或类别进行分类，因为它们取决于公告本身的存在。
已删除。
评论指出。解决。

7.1。对于其他用户提交的每个公告，每个用户可以发布多个答复。

7.2。对于用户提交的每个公告，该用户可以发布一个或多个回复。

7.3。已删除。

7.4。已删除。

现在，数据模型允许每个公告的每个用户一个以上的回复；包括提交公告的用户。

。
8.确认：每个用户最多可以在公告中发布一个等级（可以撤消/更改）
。
9.已确认：每个用户最多只能对一个回复发布一个评分（同上）

10.1。给定：用户名来自组织，是标识员工的唯一名称。例如，电子邮件为username@organisation.org-
身份验证是通过ldap完成的，这是连接检索到的有关员工的其他信息所必需的

确认：用户名是一个很好的标识符

10.2。确认：FirstName，LastName … BirthPlace等保留为（传统）列，以确保

People

不重复。
。
11.鉴于：目前，我们可以通过组织内众所周知的临时名称来识别我们的办公室，因为我们只有大约3个主要办公室和许多总部外办事处。
因此，例如华盛顿特区或弗吉尼亚外地办事处。总的来说，我认为我们将尝试将总数保持在20以下。我也想记录每个位置的确切地址，因为这可以用来为用户唯一标识办公室。

提供：
```
StateCode+Town
```
作为PK；
```
IsMainOffice
```
作为布尔值。

。
12.证实：

Description

与

Name

对

Category

是必需的。
。
13.给出：用户将无法发布到某些类别。只有具有足够高权限的用户才有权发布某些类别。

提供：
```
Permission
```
in
```
User, Location, Category
```
是评估此类权利的方法。

。
14.确认：

Location.Administrator

是

UserId

的管理员

Location

。
。
15.假设：永远只需要喜欢或不喜欢。
我认为不需要中立立场，因为这与不投票一样？说实话，喜好似乎与公告的回复有关。即是说，我看到您的回应，而不是写我自己的意见，我只会同意您的看法-
现有的公告板在组织中有点像是社交方面的问题，我认为喜欢和不喜欢/同意和不同意会引起一定程度的争议，从而鼓励人们参与。但是，喜欢或不喜欢公告可能并不总是完全合适的。

15.1提供：

Like

作为

BulletinRating

和中的布尔值

ResponseRating

。这将需要在每次访问时进行解释。
15.2。当它不再是布尔值时，可以将其更改为

RatingCode

，并实现为Lookup表。然后通过Joins确定名称，并消除解释。我在“第一数据模型”中进行了介绍，以便您可以了解我的意思15.3。在第二个数据模型中删除。
。
16.确认：每个用户都有一个房屋

Location

（

Locations

他们感兴趣的列表除外）。
。
17.确认：

Permission

按照（13）。
。
18.确认：根据数据模型，可能需要进一步的许可。

18.1。如果您现在执行此操作，则无需担心组织决定何时阻止

Person

某人发布

Responses

或对

Bulletins

它们进行评级。并希望该功能在昨天实现。

18.2。即使您没有实现它，也要在您实现的值之间留出空白。
。
19证实：一个

Bulletin

是关于一个

Location

。

19.1。确认：没有没有

Bulletins

没有

Location

19.2。确认：没有没有

Bulletins

没有

Location

。

19.3确认：没有

Bulletins

没有

User

（声明性）。但是到目前为止，我们还没有办法限制它

User

;
因此任何人

User

都可

Bulletin

以为任何人插入a

Location

（您可以将其限制在代码中，例如限制于

Locations

each

User Is Interested In

。

19.4确认：没有

BulletinRatings

不带

Bulletin

和的等级

User

。

19.5确认：没有，就

Responses

没有

Bulletin

。

19.4确认：没有

ResponseRatings

不带

Response

和的等级

User

。

19.7。但是，可以独立地有

Users

Locations

, and

Categories`。

。
20.如果您不介意，我将提供命名约定等。它们应该是自解释的，并且只有在您开始编码SQL时才会显示该值。请问，如果没有。对于初学者，所有名称均为单数。混合大小写更易于阅读（您应该将大写字母用于SQL语言）。

20.1。我的经验是table_name而不是tableName是真正的技术表格，用户并不喜欢它们。一致的混合大小写是每个人都喜欢的。这是无法更改的事情之一，因此请谨慎选择。
。
21.对于需要将表分组在一起的做法，这很好，请记住，这是一个物理问题。在逻辑数据模型级别，表具有普通名称，不受物理问题的干扰。想象一下，物理表的前缀是这样的（并且请为此使用大写字母）：
-

REF_

供参考（例如User）和查找表
-

BUL_

对于Bulletin系统
。
我不能用大写字母命名表吗？我不确定为什么。我不知道为什么不能使用大写的表名。它与使用MyIsam数据库表有关吗？

通用约定是SQL语言以大写形式表示。我曾经使用过的每个报告和管理工具都会生成这样的SQL代码。因此，我们不能使用大写。仅小写或混合大小写。因此，选择归结为table_name或TableName；我们需要某种分隔符。由于已经提供的原因，我强烈建议使用大小写混合的大小写，而不是带有大写字母的OO样式。

。
22.

rank

（全部）可以直接从数据库派生（请记住，在数据建模期间不必担心代码）。如果存储它，这是一个规范化错误。重复的栏；必须保持最新状态；这可能与导出的值不同步；这称为更新异常。第五范式消除了更新异常。那是我对规范化的最低要求，这就是从我这里得到的东西。

22.1 我根本不干涉排序顺序或受欢迎程度问题；实际上，从声音上看，您还没有关闭该功能。在标准化过程中，我仅取出多余的数据，即rank 列。

22.2。这是RANK（）运算符的“
快速教程
”（众所周知）。它不是ANSI
SQL。它是Oracle和MS的扩展。但是，如果您了解子查询，则不需要它，这就是Sybase没有它的原因。我怀疑MySQL是否拥有它，因此您需要努力解决它。必须了解标量子查询。Sybase语法，因此请改用分号等。请随时提出具体问题。
。
我从未见过将Rank =（SELECt ....）用作Rank的方法吗？

我为此发布了单独的答案。

。
22.3。需要理解为什么根本没有问题。只有孩子盲目地遵循简单的规则，而您当然不是其中之一。
。
23.确认：

users.total_bulletins

是冗余的；它可以导出。已移除。
。
24.您所有的PK都是ID。您是否已经对代码迷失感到厌倦了？忘记将物

Id

联网PK 贴在移动的所有内容上，让我们了解您的用户
如何识别他们的实体；什么实体是真正的独立实体，而另一个实体是独立实体。

24.1。切勿使用

Id

或任何此类形式。如果是PK，请使用完整表格。

24.2。调用location_id，location_id（无论在哪里），包括PK表。例外是您需要显示角色。这将在数据模型中变得清楚。
。
25.您没有声明性引用完整性，没有 定义的
外键。这是一个坏消息，原因有很多。解决这些问题后，请添加它们。DRI表示，如果不是全部，则在SQL中声明“完整性”。ISO / IEC / ANSI
SQL标准允许这样做，但是市场上的免费软件不提供该标准，并且正在逐步追赶。这意味着，除非父表中存在PK，否则服务器将不允许在FK表中添加行。MySQL最近为外键提供了DRI。对于FK，请参阅
▶本文◀ 。

25.1。对于CHECK约束和规则，您必须在代码中实现。

我的外键就像，users-id（fk）= users.id（pk）我不知道如何添加他们，除了我已经做过的事，但是一旦我知道怎么做就一定会这样做。

那不是将它们添加到您的数据库中。那只是引用
WHERe
数据操作语言中的子句中的列，而不是引用
数据定义语言中的列。添加它们，以便它们在数据库/服务器级别起作用，这意味着按照链接的文章在DDL中声明它们。然后，MySQL将停止将一行插入到父表PK不存在的子表（FK）中。那就是
参照完整性 。如果在DDL中声明，则为 声明性引用完整性 。
除了强制执行RI外，每个人都可以看到定义：用户可以使用报告工具从db访问和报告，而不必让别人编写报告。
是的，据我所知。在 ▶本网站◀ 确认
。我为子查询提供的代码使用DRI，因此我们可以对其进行测试并尽早将其投入使用。您必须检查特定版本的MySQL。

二十五。 评论注释。
我真不是MySQL专家。是的，这些是您必须自己解决的问题。总的来说，从我的角度来看，MySQL是没有腿的。对于任何SQL类的东西，您都需要InnoDB。

但是不要让那阻碍了你。现在使用Engine = MySQL，不使用声明性SQL，并继续使用数据模型和子查询。在后台处理InnoDB。
需要明确的是，我提供的DDL应该适用于MyISAM（并在DRI部门“什么都不做”，直到获得InnoDB）。

。
27.鉴于：我已经重新考虑了公告的排序要求。用户可以按时间顺序进行排序-
很有意义。用户可以按对公告的最新答复日期对公告进行排序。然后，我们可以忘记排名，在最后一次答复时按时间顺序对公告进行排序应该真的很容易吗？你怎么看。

是。这是明智的，而且很普遍，大多数人都了解时间顺序。您将不得不弄乱他们在搜索窗口中选择的过滤器（选择：
Location
或列表；选择：
Category
或列表；选择：我
Bulletins
或全部）。

开放式问题

（零）

资料模型

好的，假设您没有ERD的问题，并且实现了所有已解决的问题，那么我已经对数据进行了建模，并准备了 第五个数据模型10 Dec 09
进行审核。我绝对需要更多关于此的反馈，问题等。我很难接受它已经完成。最好开始为您的问题区域编写真实的代码。

链接

▶链接到IDEF1X符号◀
在阅读数据模型之前，您确实需要阅读并理解这一点。

▶链接到第五公告数据模型◀
的 实体关系图 是在第一页上，其次是 数据模型 。

这些键几乎是直接的IDEF1X（除了我作为对点提供的UserId之外）；这意味着钱包关系钥匙。未增强，未针对物理考虑进行优化。吞并它们之前，请先注意它们，注册并评估它们。当然，我们可以添加
```
Id
```
IOT钥匙，但我们这样做之前，让我们确保我们明白我们将失去。
请注意根据“注释”文档的标识符（实线）。脊柱是系统的椎骨
```
Location ... Bulletin ... Response
```
。
注意，密钥实际上实现了许多业务规则。
请注意我渲染的自然层次结构。看看其中是否对您有任何意义。
动词短语非常重要。看看他们有什么意思。

关于第一个数据模型和响应的评论

我有一个问题是该位置的主键将用于形成子主键？（它们由实线连接）我不太了解该概念

是。
Location
（在线上方）的PK为
(StateCode, Town)
。将两列PK（复合键）一起从F
迁移
Location
到
Bulletin
FK（粗体）。我们还使用它来形成
Bulletin
PK（在行上方）。
如果并且当我们需要代理键时，我们将添加它。目前，我们正在确定标识符。因此要考虑的问题是：

什么是公告的好标识符？ ，您的用户自然会使用什么来识别公告…
“您昨天看过弗吉尼亚州FO的公告吗？”，
“华盛顿的萨利肯定会写出好公告”，等等。

或为什么用户和公告之间不存在这种关系？

好吧，该关系在之间不能存在
User and
Bulletin
，但是在虚线之间存在一个关系，意思
UserId
是FK
Bulletin
（粗体），但没有用它来形成其PK（在线下）。
还是您的意思是：用户是该用户的强大标识符
Bulletin
（因此应使用它来形成
Bulletin
PK，因此该行应该是实心的）？
精细。优秀的。这就是建模标识符的全部意义。这清除了我不喜欢的区域，因为我们有非唯一索引。那也解决了我的问题。

按照上面进一步说明的意图，由于我现在将“等级”显示为表格以及呈现的内容，因此，一旦删除，我将其删除
我认为Permission应该是一个实体。
```
Bulletin
```
PK现在
```
(StateCode, Town, UserId, SequenceNo)
```
。需要说明的
```
SequenceNo
```
是，在以下范围内
```
StateCode, Town, UserId
```
：Sally的第5则公告MO / Billngs FO将为5。
请注意，用户设置
```
BulletinsPerPage
```
等与的比例为1 :: 1
```
User
```
，因此它们位于
```
User
```
；中。子表将不正确。
纠正印刷错误。

关于第二数据模型和响应的评论

两个PKS的
```
Bulletin
```
和
```
Response
```
已经改变，以反映（7）。
```
BulletinNo
```
和
```
ResponseNo
```
已被替换
```
BulletinDate
```
和
```
ResponseDate
```
（曾经是
```
CreatedDate
```
），以便允许每多个回复
```
User
```
每
```
Bulletin
```
。

关于第三数据模型和响应的评论

相信您度过了一个愉快的假期。

至少在30年前（据我所知），行业中的巨头们进行了这场辩论。名称总是单数。表是名词。动词短语是动词。这不仅限于数据库命名约定，它还适用于文档，论文，论文等。您可能在文档末尾有5条结论，但目录或页面顶部的章节标题或章节标题是“结论”。

在通过Uni一直与他们抗争之后，我开始了我的第一份付费编程工作，并看到了规则在现实世界中的重要性，而不是我们在大学时遇到的理论争论，所以我放弃了它是浪费时间。我浪费的所有时间和精力都被释放出来从事生产工作。从那时起，我就不再质疑巨人了。我接受
他们的思想比我的更大。这就像接受标准，或在法律或上帝内行事。我没有做任何非法事情的真的，非常好的理由。

无论如何，这些规则所支持的语言（讨论，SQL，文档）的难易程度无法得到充分的解释。随着您编写越来越多的SQL代码，它就会变得清晰起来。

您随时可以随意使用任何内容。我只提供单数。

我都可以。

但是您需要记住，在确定一个人的唯一性时，通常需要使用已识别顺序中的这两个元素（非PK唯一索引或备用键）。删除它们将导致两件事。首先，您将不再能够跨整个字段标识唯一性

Users

（因此您可能会有重复的行）。其次，AK变得不唯一，即一个反向条目。

要点是（与帖子之一相反），任何与
```
User
```
PK 为1 :: 1的列都应位于中
```
User
```
。所有首选项设置。由于我们清理了
```
InterestedLocations
```
和
```
InterestedCategories
```
，所以我只知道
```
BulletinsPerPage
```
剩下的；但我敢肯定还有其他人。
```
IsPreference2
```
是一个例子。布尔值
```
NumPreference3
```
是一个例子。整数。等等，您可以告诉我真正的偏好设置是什么。

（让我们尝试以复数形式：…与

Users

PK处于1 ::
1的任何列都应驻留在其中

Users

。只是对我不这样做，我迷上了残破的英语，所以我有点宝贵关于我的母语。）

数据模型已更新。

优秀的。让我知道您何时满意，我将为您提供物理模型。

动词短语如何？

评论re 06 Dec 10 20:38 EST（小更新）

.
28. Where there is only one occurrence of PK as an FK, of course, the FK
column name is the same as the PK column name. However, when there is more
than one occ of the FK (take a look at

ResponseRating

), there are three

UserIds

), we need to differentiate them. In IDEF1X terminology this is
called Roles. The Role of the

User

who issued the

Bulletin

Issuer

,
and so on. Obviously it is better to use that name, and keep it consistent
throughout the hierarchy (not

UserId

Bulletin

and then when we get to

Response

, where there are two, and a differentiation is demanded, change it
to

IssuerId

. I thought you might have a problem with that; in the early
stages, the usage is

Issuer.UserId

so that it is absolutely clear the it is

UserId

as an FK, and the Role is

Issuer

; when we get to the physical
model, it gets simplified to

IssuerId

Likewise, we have many DateTime columns (Date for short if you like; otherwise
Dtm), that need to be differentiated.
.
29. Did the IDEF1X Notation doc not make sense ?

The PK for each table is above the line, in the specified order.
Remember we are carrying the PKs of the parent tables anyway, and if there is meaning, using those FKs to form the child PK.
For
```
Bulletin
```
:
- The Location FK
```
(StateCode, Town)
```
  for which it is Issued
- The
```
UserId
```
  of the Issuer
- and DateTime it was Issued, to make it unique.
- therefore (StateCode, Town, IssuerId, BulletinDate)`
- To delete all
```
ResponseRatings
```
  for this
```
Bulletin
```
  , use
```
WHERe =
```
  on those four
```
Bulletin
```
  columns.

.
30. Because

(State, Town)

is the PK of

Location

, carrying wherever. And
it forms part of the

Bulletin

PK, so any dependent tables carry those
columns because they are carrying the

Bulletin

PK.

Look for the coloured Tabs (This version only)
.
32. Those are Verb Phrases. The way to read them is detailed in the
Notation doc. It appears you have a good handle on it. It is really
important to get the table names (and the Verb Phrases) right, because
change is difficult after implementation. If you tell me Office is better
than Location, that’s fine with me.
Read: Office Is Activated By Bulletin
Feel free to supply another Verb Phrase.
AFAIC, the
Office
is dead to the rest of the org, and only comes alive on
their radar (is activated by) the issue of a
Bulletin
.
I realise it sounds silly here, but ignore that for a moment, something
along the lines of “
Office
expresses its aliveness; advertises its
activity, by issuing a
Bulletin
”.
Have a quiz at Mark’s Sensor Data Model, for some nice Verb Phrases.

We had previously identified that

(State, Town)

is the PK, I will leave
that as is Refer to (38) for change.

.
33. Worth discussion. Yes, if you are going to display it when (eg)
displaying

Responses

, and the users understand

UserName

. No, if it is 30
bytes, and there is also an unique 4 byte

UserId

. The idea is to make these
choices consciously, aware of what you are giving up, when you eventually
decide that some 6 column 30-byte key is too cumbersome to migrate to the
children.

I did state at the outset, I would use
```
UserId
```
as a typical
```
Id
```
Pk, because it is carried/migrated to several child tables.
We can leave how that is created for later. But it is a pure Surrogate PK.

.
34. No problem.

Category

already has it. I’ll change

Order

ListOrder

.
35. Sure. based on what I have read and heard, I am quite happy with it. But
I would like more back-and-forth to achieve some confidence, before you write
pre. Alternately, view it as a learning experience, and accept that the model
and pre may change later. Would you like me to produce the Physical now ? If
you give me any and all corrections, I will publish the next version. I am
expecting preferences in

User

. Also, quickly run through the functions and
check that you have all the columns you need.

Do look at some of the other answers, for the purpose of learning, and
interest.

.
36. Joins. You just join on ~~four~~ three columns as opposed to one. SQL is
cumbersome with joins, and the new syntax which was supposed to make it
easier, is actually more cumbersome. My prers never write joins: we save time
and typos. I have a proc that given two or more tables, will generate the pre
with all the columns and joins. I don’t know enough of MySQL to convert that
for you.

Data Model Updated.
.

Comments re 08 Dec 10 20:49, Fourth Data Model and Responses

.
Check the previous section immediately above, there are small updates.

IDEF1X: Your speed is fine.

Note the child always “inherits” the Parent PK, as an FK (either solid or
broken line), otherwise there is no Relation between them. By using these
columns that exist in the child anyway, to form the child PK, we carry the
meaning (and that is the difference between solid and broken). And thus we
do not need to look for an independent Identifier for the child. The
Relational power in this method will become clear later, when you are coding.

The section we are dealing with is about Identifiers : natural vs
unatural; meaningful vs meaningless. Later you will see how we can use the
Relational capability of the engine, when the child PK is formed from the
parent PK. (Isn’t your surname the same as your father’s ?)

It is also important to understand Relational databases and their capability.
That is lost when we approach the database (eg) from an OO perspective, and
treat it as a location to make our classes “persistent”. Therefore, we will
try to learn and use Relational terms. It gets difficult when you go to France
and expect that they speak American, and use the same currency; learn to speak
10 words of French, and they welcome you with open arms, and you’ll have quite
a different experience with the locals.

Anyway, go ahead with implementing the model. Just realise we will probably
make a change at some point. Save all your DDL. Save all your test data as
insert statements or as a table backup or character format export (no idea
what MySQL can/cannot do in this area). .
37.1. Handled, the n::n Relation with

Office

Category

. You will only
“see” that when we get to the Physical Model.

37.2. Done.

37.3 Done.
.
38. Excellent. Shorter as well. Note they will never be able to have two

Offices

in the same Zip Code. NUMERIC(5,0) is good, but I thought the US was
moving towards 7 digits. Doesn’t matter, you can figure it out; it is an
excellent PK for

Office

. Now this column, which was part of

Address

,
probably

ZipCode

, has been elevated to a higher purpose, without
duplication; since we are carrying it in 5 child tables, and we want the PK
name to be clear, as per previously explained conventions, we will call it

OfficeCode

;

OfficeZipCode

might be silly.

We need an Unique Index on

Name

to ensure they do not add two

Offices

with
the same name. Note, for explanation purposes, this is is actually the logical
key of

Office

, replacing

(StateCode, Town)

, and it remains so.

I still think you may need

StateCode

and

Town

as a quick reference (other
than sitting somewhere in

Address

)

Data Model updated, Fifth now available for review. You did not state your
preference, for

...Date

...Dtm

. I am going with the latter, as it is
more spceific, identifying the time component as well. Easy to change.

This Answer has reached maximum length. Continued in “Part II”