CODEWITHSUNDEEP
databasedatabase-design
bendikrrr
bendikrrr

Reputation: 58

Database design: Join table with extra column or, two join tables

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. Add extra column to the existing join table: 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.

Upvotes: 3

Views: 812

Answers (4)

Pankaj
Pankaj

Reputation: 10105

You should go with below.

Add another join table: 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

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

Third Normal Form (3NF)

  1. Meet all the requirements of the second normal form.
  2. Meet all the requirements of the First normal form.
  3. Remove columns that are not dependent upon the primary key.

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...

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

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

Pros (vs option 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.

Cons:

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

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

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

Upvotes: 3

wintersolutions
wintersolutions

Reputation: 5273

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)

Upvotes: 2

gbn
gbn

Reputation: 432271

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

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
  • ...

Upvotes: 3

Related Questions