What is the best practise for relational database tables in mysql?

Question

Hi,

I know, there is a lot of info on mysql out there. But I was not really able to find an answer to this specific and actually simple question:

Let's say I have two tables:

USERS (with many fields, e.g. name, street, email, etc.) and GROUPS (also with many fields)

The relation is (I guess?) 1:n, that is ONE user can be a member of MANY groups.

What I dis, is create another table, named USERS_GROUPS_REL. This table has only two fields:

us_id (unique key of table USERS) and gr_id (unique key of table GROUPS)

In PHP I do a query with join.

Is this "best practice" or is there a better way?

Thankful for any hint!

---

Hi all,

thanks for your quick and helpful support. Knowing that I was on the right way builds up my mysql-self-confidence a little. :-)

As many commented, my example is not 1:n but many to many. Just as a quick sql-lesson: :-)

Are these the right terms? 1:n one to many n:1 many to one n:n many to many?

Answer 1

It's quite the best case :) Creating a compound predicate primary key in the relation table is the optimal solution:

ALTER TABLE USERS_GROUPS_REL ADD PRIMARY KEY(us_id, gr_id)

Answer 2

That would be the best practice.

You have two tables on either side of the join and a mapping table (or linker table, or whatever other terminology is out there) in the middle which handles the Many-to-Many relationship (A Group has many Users and a User can belong to many Groups).

To increase performance, you should make a composite key for the mapping table out of us_id and gr_id. You can also create an index on the inverse so that if you need to sort by gr_id, you get the performance benefit there too.

Answer 3

One user can belong to many groups, and one group contains many users, so it is a many-to-many relationship.

The way you describe it is a typical and correct one. A many-to-many relationship is often mapped with an association class or relation table.

Sometimes the relation table has more columns. For example, a user may be the administrator of a particular group. The users_group_rel table would then also have a field administrator.

Answer 4

You have described a many-to-many relationship. Using that relationship, many USERs can be members of many GROUPS. For your purposes, this is indeed the correct way to go.

One-to-many and one-to-one relationships do not require the link or cross-reference table (USERS_GROUPS_REL).

You might find this tutorial useful:

http://www.tonymarston.net/php-mysql/many-to-many.html