CODEWITHSUNDEEP
mysqlphpmyadminregistration
Timothy
Timothy

Reputation: 33

Multiple Entries to Mysql

I have a registration page where a user can register him/herself along with 20 other people. Now I can store all those values in fine, the question I have is how to identify in the table. So let's say John registers himself and 20 of his friends. In the table, I would label him and his group with an ID number of 1, but I would label his ID specifically with a star so I know that he's the one who registered everyone else.

That's the idea I'm trying to get at, but I don't know how to do that in practice. What is the best way and how do I really go about doing this? I think I can write the code but I just need some concepts to work off of.

Upvotes: 0

Views: 103

Answers (4)

John Woo
John Woo

Reputation: 263833

You can create three tables for this. The UserList which contains all the records of all users. The GroupList which contains all the groups and the owner of the group. and Lastly PersonGroup which contains the user_id and the group which a certain user belongs.

CREATE TABLE UserList
(
    ID INT AUTO_INCREMENT,
    FullName VARCHAR(50),
    -- ... other columns if you want ...
    CONSTRAINT ul_pk PRIMARY KEY (ID),
    CONSTRAINT ul_uq UNIQUE (FullName)
);

CREATE TABLE GroupList
(
    ID INT AUTO_INCREMENT,
    GroupName VARCHAR(25),
    OwnerID INT,
    -- ... other columns if you want ...
    CONSTRAINT gl_PK PRIMARY KEY (ID),
    CONSTRAINT gl_UQ UNIQUE(GroupName),
    CONSTRAINT gl_FK FOREIGN KEY (OwnerID) REFERENCES UserList(ID)
);

CREATE TABLE PersonGroup
(
    RecordID INT AUTO_INCREMENT,
    User_ID INT,
    GROUP_ID INT,
    CONSTRAINT pg_PK PRIMARY KEY (RecordID),
    CONSTRAINT pg_uq UNIQUE (User_ID, GROUP_ID),
    CONSTRAINT pg_FK1 FOREIGN KEY (User_ID) REFERENCES UserList(ID),
    CONSTRAINT pg_FK2 FOREIGN KEY (GROUP_ID) REFERENCES GroupList(ID)
);

Upvotes: 1

Alain Collins
Alain Collins

Reputation: 16362

This might be a dup of what @Adrian was saying, but I didn't really understand his, so...

When John registers, he gets a user_id, right?

Add a column to the table for a registrar_id.

For the other 20 user records, put John's user_id in the registrar_id column.

Upvotes: 0

Adrian Cornish
Adrian Cornish

Reputation: 23886

What about adding a RegisteredByID column if it is NULL they registered and then it is easy to find the group also.

Upvotes: 0

firestream
firestream

Reputation: 400

A little hard to be totally accurate, but maybe log the ip used to register and then you'll know generally how many accounts came in from that ip.

Upvotes: 0

Related Questions