CODEWITHSUNDEEP
mysqlsql
DaveR
DaveR

Reputation: 2483

Members and Groups table

I'm thinking about a database design that will have two tables Members and Groups. Each member will belong to at least one group - but members might have several groups. Is the best way to reflect this to create an array of ids for the group memberships and store this in the member's record as a foreign key?

Upvotes: 1

Views: 124

Answers (2)

germi
germi

Reputation: 4658

N:M relationships are commonly modelled as an extra table in your database. So you would have a Member-table, a Group-table and a MemberInGroup-table where you would just store the both primary keys.

Like this:

    +----+----------------+      +----+---------------+      +-----+----+----+
    |m_id|member          |      |g_id|group          |      |mg_id|m_id|g_id|
    +----+----------------+      +----+---------------+      +-----+----+----+
    |1   |tom             |      |1   |groupA         |      |1    |1   |1   |
    |2   |tim             |      |2   |groupB         |      |2    |1   |2   |
    |3   |michael         |      +----+---------------+      |3    |2   |1   |
    +----+----------------+                                  +-----+----+----+

Your idea - storing multi-value attributes in your Member-table conflicts with the first normal form (http://en.wikipedia.org/wiki/Database_normalization#Normal_forms) which states that each attribute has to be of a single value.

Upvotes: 4

Bruce
Bruce

Reputation: 1542

This is a classic many to many relationship.

A Junction table is required, this will contain the primary keys from the two tables.

You would need 3 tables - members, groups and new junction table which you could call members_groups.

Upvotes: 1

Related Questions