How do I design this link table?

Ok SO, I have a user table and want to define group开发者_开发知识库s of users together. The best solution I have for this is to create three database tables as follows:

UserTable

user_id
user_name

UserGroupLink

group_id
member_id

GroupInfo

group_id
group_name

This method keeps the member and group information separate. This is just my way of thinking. Is there a better way to do this? Also, what is a good naming convention for tables that link two other tables?

---

It depends.

If a member can only belong to one group, then your solution is overkill (and not properly optimized). In that case it would be enough to get rid of the UserGroupLink table and just add a group_id column to UserTable.

---

Looks, good, I would go with:

Group
-----
GroupID (PK)
Name
CreatedDate

GroupUser
---------
GroupUserID (PK)
GroupID (FK)
UserID (FK)
CreatedDate

User
----
UserID (PK)
Firstname
Lastname
CreatedDate
...

---

Looks like a fairly standard role based user model to me. This is fine.

---

The important thing is to choose a convention and stick with it.

Here's how I would name these tables (the structure is fine, BTW):

Users
-----
id
name

UserGroups
----------
user_id
group_id

Groups
------  
id
name

---

Your table layout is appropriate, IMHO. There are various naming conventions for the join table - Ruby on Rails uses both table names joined with an underscore, in alphabetical order: "group_member" in your case.

---

In addition to the answers, the decision in creating tables does not rely on ones opinion. There is what we call database normalization; a rule to follow in database design.