How could I create two levels of authentication for registration on my site?

Question

I already have a simple registration system in place using php and mysql. It operates well enough. However, when people visit my site and register, I would like for them to register as part of a particular group. So, I was thinking that registration would happen like this:

Visitor lands on index.php, clicks on "Group Registration" link.
Visitor supplies group name and group password. [A new table is created for that group where all user data will be stored for that particular group]
Visitor then is prompted for typical registration data--name, email, etc.--and that data is stored in the newly created group table.

Any subsequent visitors associated with that group would click on "User Reg"
The visitor would be prompted for group name and password
If correct, then he would be prompted for typical reg data, to be stored in his group's table.

What I don't know how to do is implement the group authentication prior to allowing user registration. Can someone help me with that?

Answer 1

What you want to end up with is a table for your users and another (single) table for your group information. The user table will have a foreign key field to link it to a group. When a user joins a group, you will enter a value in that field. Users not in groups will have a null value in that field. If users can create groups, they will simply be adding a new row to the groups table.

If your users can be in multiple groups, set up your tables like this.

USER
- id
- username
- password
- etc...

GROUP
- id
- name
- password (?)
- etc...

USER_GROUP_CR
- fk_user
- fk_group

The USER_GROUP_CR table is a "cross reference" or "link" table that will allow you to create a many to many relationship. This way you can have users in multiple groups without creating extra tables. When a user joins a group, add a row to the USER_GROUP_CR table with the id of the user and the id of the group. You can query this table to find out which groups a user belongs to, or to find out which users are in a group.

You should not create a new table for every group.

Answer 2

If the visitor is entering a group name and password, then you can authenticate the same way you are doing the users. You just need to first ask yourself if the group name needs to be unique or the group/password combination.

As for your idea to add a new table for each group, that is a bad idea. Imagine if you have 100 groups. Then you will have 100 tables just for groups. If you get up to 1000 groups, then you will have 1000 tables. Try managing that. It will get really frustrating really fast. Instead, what you should do is to first create a "Group" table with all the associated data (group name, password, etc). Then add a field to your User table that will hold the associated id from the Group table. That way, whenever you look up the user, you can easily check what group the user is in simply by joining the two tables rather than trying to figure out what table to look at as in your original plan.