数据库设计：使用额外列或两个连接表连接表

时间：2022-09-15 16:42:36

I have two entity classes, Student and Committee.

我有两个实体课程,学生和委员会。

I can not post images, but image of current db design can be found here: current db design

我无法发布图像,但可以在此处找到当前数据库设计的图像:当前数据库设计

or in text:

或在文本中:

**Committee**
id
name

**students_committees**
stud_id
com_id

**students**
name
gender
grade

students can be in many committees, and a committee has many students.

学生可以在许多委员会,一个委员会有很多学生。

Now I want to add a leader to committee (a committee can have more than 1 leader). What is the best way to do this?

现在我想在委员会中增加一名*(一个委员会可以有一名以上的领导者)。做这个的最好方式是什么?

  1. Add another join table: committee_leaders(stud_id, com_id)

    2. 添加另一个连接表:committee_leaders(stud_id,com_id)

  2. Add extra column to the existing join table: students_committees(stud_id, com_id, is_leader)

    3. 将额外列添加到现有连接表:students_committees(stud_id,com_id,is_leader)

  3. Something else?

I would assume an extra join table is the best choice. We would not need to traverse the entire current join table to find the leaders for a committee. My team does not agree.

我认为额外的连接表是最好的选择。我们不需要遍历整个当前的连接表来找到委员会的领导者。我的团队不同意。

4 个解决方案

#1

3  

My choice would be option 1 if the points 2 and 3 below matter in your case. If not, option 2 is simpler.

如果您的案例中下面的第2点和第3点很重要,我的选择将是选项1。如果没有,选项2更简单。

Pros (vs option 2):

优点(vs选项2):

  1. (as you pointed) you would not need to traverse the entire current join table to find the leaders for a committee. You'll just join committee with committee_leaders. (not much difference though, you'll be just searching a smaller index, the difference may be negligible.)

    (正如你所指出的那样)你不需要遍历整个当前的连接表来找到委员会的领导者。你只需加入委员会委员会。 (虽然差别不大,但您只是搜索较小的索引,差异可能微不足道。)

  2. you can easily enforce the constraint that a committee has no more than one leader.

    你可以轻松地强制执行一个委员会只有一个领导者的约束。

  3. if you want to add other attributes for the leaders, like when the leader was chosen (elected), what other title they gain, etc, you'll not be filling the studens_committees table with columns that will be NULL almost everywhere.

    如果你想为领导者添加其他属性,比如当领导者被选中(当选),他们获得了什么标题等时,你就不会在studens_committees表中填充几乎无处不在的列。

Cons:

  1. leaving the committe would require DELETEs on 2 tables (or 1 with cascading delete in effect).

    离开委托将需要2个表上的DELETE(或1个有效的级联删除)。

  2. inserting a new student as leader will require two INSERTs in 2 tables.

    插入新学生作为领导者需要在2个表中进行两次INSERT。

  3. changing the leader in a committee will require one DELETE and one INSERT vs 2 UPDATEs (possibly in one statement).

    更改委员会中的领导者将需要一个DELETE和一个INSERT vs 2 UPDATE(可能在一个语句中)。

#2

3  

I'd use "option 2": the extra column.

我会使用“选项2”:额外的列。

This means you don't have to worry about

这意味着您不必担心

  • a student being in both tables (as member and leader)

    • 一个学生在两个桌子上(作为成员和领导者)

  • leaving the committe would require DELETEs on 2 tables

    • 离开委托会需要2个表上的DELETE

  • ...

#3

2  

I'm in favor of adding the attribute to the existing join table too, because:

我也赞成将属性添加到现有的连接表中,因为:

  • The join table already describes the relation between the two tables, the leader's is just another aspect of this relation

    • 连接表已经描述了两个表之间的关系,领导者只是这种关系的另一个方面

  • You don't have to mess with additional data consistency questions (What happens on Update, Delete)

    • 您不必处理其他数据一致性问题(更新,删除时会发生什么)

  • Performance is better in the case that you need all informations, because you don't have to join three tables (Joins are expensive)

    • 在您需要所有信息的情况下性能更好,因为您不必连接三个表(连接很昂贵)

#4

2  

You should go with below.

你应该去下面。

Add another join table: committee_leaders(stud_id, com_id)

添加另一个连接表:committee_leaders(stud_id,com_id)

Otherwise you will not be able to add details of committee leader...+ Point 1 will give you more flexibility as told by other users

否则你将无法添加委员会负责人的详细信息...... +第1点将为其他用户提供更多灵活性

When moving toward point 2, This may violate 3 Normal Form

当移向第2点时,这可能违反3范式

Third Normal Form (3NF)

  1. Meet all the requirements of the second normal form.

    2. 满足第二范式的所有要求。

  2. Meet all the requirements of the First normal form.

    3. 满足第一范式的所有要求。

  3. Remove columns that are not dependent upon the primary key.

    4. 删除不依赖主键的列。

Reference

http://databases.about.com/od/specificproducts/a/normalization.htm

You can use cascade delete so deletion of record from parent table will automatically delete the entry from child table...

您可以使用级联删除,因此从父表删除记录将自动删除子表中的条目...

#1

3  

My choice would be option 1 if the points 2 and 3 below matter in your case. If not, option 2 is simpler.

如果您的案例中下面的第2点和第3点很重要,我的选择将是选项1。如果没有,选项2更简单。

Pros (vs option 2):

优点(vs选项2):

  1. (as you pointed) you would not need to traverse the entire current join table to find the leaders for a committee. You'll just join committee with committee_leaders. (not much difference though, you'll be just searching a smaller index, the difference may be negligible.)

    (正如你所指出的那样)你不需要遍历整个当前的连接表来找到委员会的领导者。你只需加入委员会委员会。 (虽然差别不大,但您只是搜索较小的索引,差异可能微不足道。)

  2. you can easily enforce the constraint that a committee has no more than one leader.

    你可以轻松地强制执行一个委员会只有一个领导者的约束。

  3. if you want to add other attributes for the leaders, like when the leader was chosen (elected), what other title they gain, etc, you'll not be filling the studens_committees table with columns that will be NULL almost everywhere.

    如果你想为领导者添加其他属性,比如当领导者被选中(当选),他们获得了什么标题等时,你就不会在studens_committees表中填充几乎无处不在的列。

Cons:

  1. leaving the committe would require DELETEs on 2 tables (or 1 with cascading delete in effect).

    离开委托将需要2个表上的DELETE(或1个有效的级联删除)。

  2. inserting a new student as leader will require two INSERTs in 2 tables.

    插入新学生作为领导者需要在2个表中进行两次INSERT。

  3. changing the leader in a committee will require one DELETE and one INSERT vs 2 UPDATEs (possibly in one statement).

    更改委员会中的领导者将需要一个DELETE和一个INSERT vs 2 UPDATE(可能在一个语句中)。

#2

3  

I'd use "option 2": the extra column.

我会使用“选项2”:额外的列。

This means you don't have to worry about

这意味着您不必担心

  • a student being in both tables (as member and leader)

    • 一个学生在两个桌子上(作为成员和领导者)

  • leaving the committe would require DELETEs on 2 tables

    • 离开委托会需要2个表上的DELETE

  • ...

#3

2  

I'm in favor of adding the attribute to the existing join table too, because:

我也赞成将属性添加到现有的连接表中,因为:

  • The join table already describes the relation between the two tables, the leader's is just another aspect of this relation

    • 连接表已经描述了两个表之间的关系,领导者只是这种关系的另一个方面

  • You don't have to mess with additional data consistency questions (What happens on Update, Delete)

    • 您不必处理其他数据一致性问题(更新,删除时会发生什么)

  • Performance is better in the case that you need all informations, because you don't have to join three tables (Joins are expensive)

    • 在您需要所有信息的情况下性能更好,因为您不必连接三个表(连接很昂贵)

#4

2  

You should go with below.

你应该去下面。

Add another join table: committee_leaders(stud_id, com_id)

添加另一个连接表:committee_leaders(stud_id,com_id)

Otherwise you will not be able to add details of committee leader...+ Point 1 will give you more flexibility as told by other users

否则你将无法添加委员会负责人的详细信息...... +第1点将为其他用户提供更多灵活性

When moving toward point 2, This may violate 3 Normal Form

当移向第2点时,这可能违反3范式

Third Normal Form (3NF)

  1. Meet all the requirements of the second normal form.

    2. 满足第二范式的所有要求。

  2. Meet all the requirements of the First normal form.

    3. 满足第一范式的所有要求。

  3. Remove columns that are not dependent upon the primary key.

    4. 删除不依赖主键的列。

Reference

http://databases.about.com/od/specificproducts/a/normalization.htm

You can use cascade delete so deletion of record from parent table will automatically delete the entry from child table...

您可以使用级联删除,因此从父表删除记录将自动删除子表中的条目...

标签：

相关文章