需要数据库设计指导

发布于 2024-09-10 08:26:17 字数 1298 浏览 5 评论 0原文

一位奶农兼兼职漫画家，拥有几群奶牛。他将每头牛分配到一个特定的牛群。在每个牛群中，农民都有一头他最喜欢的牛——这头牛通常出现在漫画中。每个牛群中都有一些不满者，主要是那些认为自己应该出现在漫画中的人，不同意农民选择最喜欢的牛，他们轻蔑地将其称为神圣的牛。结果，现在每个牛群都选出了一个牛群领袖。

这就是我认为表格应该看起来的样子，你能告诉我是否可以做得更好吗？到目前为止，我正在使用最喜欢的表作为中间进行多对多操作，这是最好的解决方案，也不需要 SQL 语句，这只是出于设计目的。

预先感谢您

Table Herd           Table Favorite               Table Cartoon   Table Cow
PK herdID          Intermediate Table             PK cartoonID     PK cowID
   herdname                                          cartoonTitle     cowName
   herdleader                                        cartoonType
                                                     cartoonDate

编辑的图像 @ 3:01pmEST 这是正确的吗？

cowErd http://img838.imageshack.us/img838/1268/capture3h.png

添加了新图片 @ 8:57am 7/20/2010 请有人批评一下这个 ERD Erd2 http://img37.imageshack.us/img37/5794/capture3fc.png

添加了新图片 @ 12:47pm 7/20/2010 除非有任何反对意见，这是根据 Mark 的解释的最终草案标记 ERD http://img651.imageshack.us/img651/691/capture4b.png< /a>

原文

A dairy farmer, who is also a part-time cartoonist, has several herds of cows. He has assigned each cow to a particular herd. In each herd, the farmer has one cow that is his favorite - often that cow is featured in a cartoon. A few malcontents in each herd, mainly those who feel they should have appeared in the cartoon, disagree with the farmer's choice of a favorite cow, whom they disparagingly refer to as the sacred cow. As a result, each herd now has elected a herd leader.

This is what I think the tables should look like can you let me know if it can be done better? So far I'm doing a many to many using the favorite table as the intermediate is this the best possible solution also no SQL statements are needed this is just for design purposes.

Thank you in advance

Table Herd           Table Favorite               Table Cartoon   Table Cow
PK herdID          Intermediate Table             PK cartoonID     PK cowID
   herdname                                          cartoonTitle     cowName
   herdleader                                        cartoonType
                                                     cartoonDate

edited image @ 3:01pmEST is this correct?

cowErd http://img838.imageshack.us/img838/1268/capture3h.png

added new image @ 8:57am 7/20/2010 can some one critique this ERD please
Erd2 http://img37.imageshack.us/img37/5794/capture3fc.png

added new image @ 12:47pm 7/20/2010 unless there's any objections this is the final draft per Mark's explanation
mark ERD http://img651.imageshack.us/img651/691/capture4b.png

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

没有心的人 2024-09-17 08:26:17

迈克尔：

问题陈述中有哪些名词？有多少个？

Farmer  - There is one farmer
Cow     - There are many cows
Herd    - There are many herds
Cartoon - There are many cartoons

由于只有一名农民，因此将他排除在未来的讨论之外。这些是你的基础
实体。

每个实体有什么属性？

Cow     - each cow has a name
        - each cow is a member of a herd
Herd    - each herd has a name
        - each herd has a cow that is the sacred cow
        - each herd has a cow that is the herd leader
Cartoon - each cartoon has a name
        - each cartoon may have a cow that appears in it 
             (not specified definitively)

因此，其中一些属性引用其他实体，而另一些则不引用。不引用其他基本实体的属性很简单。其他的则需要更多的考虑。

Can a cow be a member of more than one herd?
Must a cow be a member of a herd?
Can a herd have more than one cow that is the sacred cow?
Must each herd have a cow that is the sacred cow?
Can a herd have more than one cow that is the herd leader?
Must each herd have a cow that is the herd leader?

这些问题有助于概述实体之间的关系是强制性的还是可选的，以及关系是一对多还是多对多。

Michael:

What are the nouns in the problem statement, and how many of them are there?

Farmer  - There is one farmer
Cow     - There are many cows
Herd    - There are many herds
Cartoon - There are many cartoons

As there is only one farmer, leave him out of future discussions. These are your base
entities.

What attributes does each entity have?

Cow     - each cow has a name
        - each cow is a member of a herd
Herd    - each herd has a name
        - each herd has a cow that is the sacred cow
        - each herd has a cow that is the herd leader
Cartoon - each cartoon has a name
        - each cartoon may have a cow that appears in it 
             (not specified definitively)

So some of these attributes reference other entites, and some do not. The attributes that do not reference other base entities are simple. The other ones require more consideration.

Can a cow be a member of more than one herd?
Must a cow be a member of a herd?
Can a herd have more than one cow that is the sacred cow?
Must each herd have a cow that is the sacred cow?
Can a herd have more than one cow that is the herd leader?
Must each herd have a cow that is the herd leader?

These questions help outline whether or not the relationships between the entities are mandatory or optional, and whether the relationships are one-to-many or many-to-many.

回复收藏 0 原文

我一向站在原地 2024-09-17 08:26:17

一个讨论项目，所以我将其设为社区 Wiki。

关系模型做得不好的一件事是强制在 Herd 级别持有的 SacredCow 和 HerdLeader 实际上指向属于该 Herd 成员的奶牛。

假设你的牛群是星形和十字形。 “星”兽群的详细信息可能会将 Rigel 指定为圣牛，将 Castor 指定为兽群领袖，但“牛”表可能将 Castor 显示为“十字”兽群的成员。实际上，当创建一个新的牛群时，当您的牛群没有牛（因此没有 HerdLeader/SacredCow）或牛没有牛群时，您将面临先有鸡还是先有蛋的情况。

另一种模型将具有“Cow”表，指示特定的牛是否是其牛群的 HerdLeader 和/或 SacredCow。 [在物理实现中，可以通过一个独特的约束强制每个 Herd 只有一头牛是 SacredCow 和一头牛是 HerdLeader。]。“Herd”表不会有 SacredCow 或 HerdLeader 。该模型无法强制每个牛群都有一个 HerdLeader 和一个 SacredCow。

两人都是模特。两者都有缺陷。在逻辑层面上，我可能会选择前者，因为它更加规范化。在物理上，我会考虑哪种不一致会更麻烦并且更有可能发生，并且我会选择最能防止这种情况的模型。

回复收藏 0 原文

两个我 2024-09-17 08:26:17

我喜欢你实际上尝试自己做这件事。
事实上你已经快到了，所以你的工作一点也不差。

让我们从对象/实体的角度来看这个问题。

这些实体包括以下内容：

Cows
Herds
CowHerds（您必须将一头牛与一个听音关联起来）
CowCartoon（其中一头奶牛是您最喜欢的，您始终可以通过使用 coid 沿着链向上引用这头牛的听音）

Cows

CowID（主要）键）
CowName (varchar,
这可能是关键，但是
牛可能有相同的名字，对吧？）

Herds

HerdID（主键）
HerdName（这也可能是主键，
最终这是你的决定）
CowID （外键，记住每个牛群都有一个牛首领，你甚至可以将其单独放在另一个表中，但这确实不值得）

CowHerds

CowID （fK to 牛）
（fk tohears）

HerdID 上述字段的组合作为主键

CowCartoonCartoonID

（漫画的主键）
CowID（“最喜欢的”牛，引用农民将要写漫画的牛）
Published Date（发布日期）
Title
....

正如评论中提到的，您还可以摆脱 CowHerds 表并直接在 Cows 表中引用 HerdID。

回复收藏 0 原文

若水微香 2024-09-17 08:26:17

假设一头牛一次只能属于一个牛群，并且假设同一幅漫画中可以出现不止一头牛（显然来自不同的牛群），我的建议是：

Table Herd

Herd ID (PK)
Herd Name

Table Cow

Cow ID (PK)
Herd ID (FK)
Cow Name
Is Leader (Boolean)
Is Sacred (Boolean)

桌子卡通

Cartoon ID (PK)
Cartoon Title
Cartoon Type
Cartoon Date

桌子外观

Cow ID (PK)
Cartoon ID (PK)

Assuming a Cow can only belong to one herd at a time and assuming that more than one cow (from different herds, obviously) can appear in the same cartoon, my suggestion:

Table Herd

Herd ID (PK)
Herd Name

Table Cow

Cow ID (PK)
Herd ID (FK)
Cow Name
Is Leader (Boolean)
Is Sacred (Boolean)

Table Cartoon

Cartoon ID (PK)
Cartoon Title
Cartoon Type
Cartoon Date

Table Appearance

Cow ID (PK)
Cartoon ID (PK)

回复收藏 0 原文

爱的十字路口 2024-09-17 08:26:17

第 1 部分。

如果满足以下条件：

each Cow must be in exactly one Herd
a Herd must have a sacredCow and a herdLeader
a sacredCow for a Herd must be a Cow in that Herd
a herdLeader for a Herd must be a different Cow in that Herd

那么您可以使用此部分模型实现这些规则：

Cow (cowID, herdID) (all mandatory columns)
- primary key (cowID)
- unique (herdID, cowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader) (all mandatory columns)
- primary key (herdID)
- foreign key (herdID, sacredCow) references Cow (herdID, cowID)
- foreign key (herdID, herdLeader) references Cow (herdID, cowID)
- constraint (sacredCow != herdLeader)

请注意 FK 关系如何包含 herdID，而不仅仅是owID。这确保了只有牛群中的那些牛才可以成为该牛群的神圣牛或牛群领导者。

这种设计使得实施起来有点棘手，但并非不可能。 Herd 上的外键在像 Oracle 这样的数据库中必须是可延迟的，因为我们需要能够先为 Herd 插入行，然后才能为奶牛插入行，而一个 Herd 至少需要两头奶牛（圣牛和牧群领袖）。

第 2 部分。

下一个挑战是实现以下约束：

only a Sacred Cow may be featured in a Cartoon

实现此目的的一种方法可能是将 Cows 拆分为两个独立的关系：SacredCows 和 NonSacredCows。

SacredCow (sacredCowID, herdID) (all mandatory columns)
- primary key (sacredCowID)
- unique (herdID, sacredCowID)
- foreign key (herdID) references Herd (herdID)

NonSacredCow (nonSacredCowID, herdID) (all mandatory columns)
- primary key (nonSacredCowID)
- unique (herdID, nonSacredCowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader)
- primary key (herdID)
- foreign key (herdID, sacredCow) references SacredCow (herdID, sacredCowID)
- foreign key (herdID, herdLeader) references NonSacredCow (herdID, nonSacredCowID)

Cartoon (cartoonID, featuredCow) (all mandatory columns)
- primary key (cartoonID)
- forign key (featuredID) references SacredCow (sacredCowID)

（在此设计中，不再需要约束 (sacredCow != herdLeader)，因为根据定义它们现在是不同的奶牛。）

Part 1.

If the following are true:

each Cow must be in exactly one Herd
a Herd must have a sacredCow and a herdLeader
a sacredCow for a Herd must be a Cow in that Herd
a herdLeader for a Herd must be a different Cow in that Herd

Then you could implement these rules with this partial model:

Cow (cowID, herdID) (all mandatory columns)
- primary key (cowID)
- unique (herdID, cowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader) (all mandatory columns)
- primary key (herdID)
- foreign key (herdID, sacredCow) references Cow (herdID, cowID)
- foreign key (herdID, herdLeader) references Cow (herdID, cowID)
- constraint (sacredCow != herdLeader)

Notice how the FK relationships include the herdID, not just the cowID. This ensures that only those Cows in a Herd may be made the sacredCow or herdLeader for that Herd.

This design makes things a little tricky to implement but not impossible. The foreign keys on Herd would have to be made deferrable in a database like Oracle, since we need to be able to insert the rows for a Herd before we can insert the rows for the Cows, and a Herd requires at least two Cows (the sacredCow and the herdLeader).

Part 2.

The next challenge is to implement the following constraint:

only a Sacred Cow may be featured in a Cartoon

One way to do this may be to split Cows into two separate relations: SacredCows and NonSacredCows.

SacredCow (sacredCowID, herdID) (all mandatory columns)
- primary key (sacredCowID)
- unique (herdID, sacredCowID)
- foreign key (herdID) references Herd (herdID)

NonSacredCow (nonSacredCowID, herdID) (all mandatory columns)
- primary key (nonSacredCowID)
- unique (herdID, nonSacredCowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader)
- primary key (herdID)
- foreign key (herdID, sacredCow) references SacredCow (herdID, sacredCowID)
- foreign key (herdID, herdLeader) references NonSacredCow (herdID, nonSacredCowID)

Cartoon (cartoonID, featuredCow) (all mandatory columns)
- primary key (cartoonID)
- forign key (featuredID) references SacredCow (sacredCowID)

(In this design, the constraint (sacredCow != herdLeader) is no longer required because they are now different cows by definition.)

回复收藏 0 原文

~没有更多了~