Database design users, supervisors and teams

hello people, how do you advice i design a database where i have users and any other user can be the supervisor of any user and not all users have a supervisor. We have teams and users might or might not belong to a team,if they do, every team has a supervisor who is also a user in the database. What is the best way to structure a database to accommodate this

tbid int(11) NOT NULL AUTO_INCREMENT,
pckgUID varchar(255) DEFAULT NULL,
teamUID varchar(255) DEFAULT NULL,
usrUID varchar(255) DEFAULT NULL,
tbDate date DEFAULT NULL,
tbStatus varchar(2) DEFAULT ‘0’,
tbRole varchar(2) DEFAULT ‘0’,
tbSummary text DEFAULT NULL,
crtdByusrUID varchar(255) DEFAULT NULL,
PRIMARY KEY (tbid)

pckgUID - Maybe the Subscription ID.
teamUID - A unique Team ID
usrUID - The Users unique ID
tbStatus - 1/0 etc
tbDate - Created
tbStatus - 1/0 etc
tbRole - 9/8/7 etc (each role depicted by a unique string)
tbSummary - A descritpion of the Role
crtdByusrUID - The creator of the Team

You could add to this with a Start and End date if you wanted to restrict their input to a date range for example…

1 Like

thanks Dave, what is relationship of the status, role with userID of a normal user and a supervisor. everybody is regisgistered as a user, some end up becoming a supervisor of others but they are still users on the platform

Hi Jayd,
The Status allows you to set if the User is registered with the Team. It’s a little unnecessary but in some circumstances Roles come and go and you may wish to enable the User Status accordingly. The Role allows you to set a Role within the Team to define your Supervisors etc, ie, Supervisor could be Role 1337, and an underling Role 8008. This allows you to set Roles outside of the standard Role of the ‘Users on the Platform’.

Further. You could then use Show/Hide to display regions of the pages to certain Roles, or re-direction based on the Role itself, to another area of the application.

Remember this is a new Table outside of your Registration Table. You will use a Join to query it. The results of those queries will determine Show/hide regions or re-direction. Just wanted to clarify that, and apologise if I you already understood.

:wink:

Oh dave, i am the one that is grateful to you for spending time answering my question, you should not be apologizing to me, it should be the other way round,i am the student trying to learn.

Then again, how i initially tried to solve it is that i have a supervisor table with teamID, and userID as foreign keys(the userID is the supervisor for that team)

What do you think?

1 Like

No worries Jayd am happy to help.

I think you were on to the right track with your approach. The great thing about assigning an actual Role is that it can be adjusted/upgraded/downgraded etc. If you stick to just numeric representations of the Roles they aid in query performance (rather than using Supervisor, Manager, etc). Just add in the Role column and you are all good to go.

1 Like