Ok SO, I have a user table and want to define groups 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?
Looks like a fairly standard role based user model to me. This is fine.
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
...
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.
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.