PHP/MySQL website with users table, need to add usergroup functionality

Question

I have working website in PHP with a MySQL backend which has several tables for different purposes. This site is based on different parts or 'environments' like a bugtracker, project management, etc. There is one central 'users' database which has all the users with the associated details in them.

In each of previously mentioned 'environments', which all have their own set of tables, it is possible to specify a user ID in certain fields. e.g. the bugtracker table has a column called AssignedTo which contains the user ID's of users to whom bugs have been assigned.

The field type of these 'user ID' columns (like the AssignedTo example) is the same as the UserId field in the central users table, which is an unsigned, zerofilled INT(5) field.

Now I have a request from the users of this site to also allow to specify user groups in certain of these user ID fields.

So again reffering to the AssignedTo example, it should now be possible to also assigned a bug to a user group, instead of a specific user.

What's the best way to do this regarding the PHP scripting and the database layout? Now I have all these fields set to the same type as the UserId of the central users table, which is INT(5). However my UserGroupId field in the UserGroups table, is of a different format, INT(3).

I could make this field also into an INT(5) field, but that would not solve the 2nd issue I'm having: how to see whether the AssignedTo value is reffering to a specific user, or to a usergroup.

I was thinking about make the UserGroupId field start from 99999 and counting downwards, while the UserId field is starting from 00001 and counting upwards, thus assuming that if the AssignedTo starts with 9, it's reffering to a usergroup. But this doesn't seem like a clean solution to me...

Any better ideas? Thanks!

Answer 1

If I am understanding correctly, my solution would be to instead of having an AssignedTo column pointing to either a user or a user group, I would create two columns. One pointing to the user id and another pointing to a user group id.

Answer 2

Actually a colleague at work came up with the following solution which I really like: Change the UserId and UserGroupId field types from INT(5) to INT(4). And leave the different fields like AssignedTo set to INT(5). Now in the PHP code I can add a prefix number to either the 'UserId' or 'UserGroupId' value, this prefix number can be used to determine if the value is reffering to a UserId or a UserGroupId value. So if the AssignedTo field is '10005' it means it's a 'user' with 'id: 0005'. Also to prevent having to update all existing records, values which have a '0' at the first position will be considered users

The advantage over using positive/negative values here is that in both the Users and UserGroups tables I can still use a positive 'Id' field which can be left to autoincrement. As far as I know auto-incrementing is not possible with negative values

Answer 3

I think I understand what you are trying to say. I have a question. Can a user be in multiple UserGroups?

I would probably add a column in the bug table that says whether the AssignedTo value refers to a UserID or a UserGroupID.

Create a separate table for UserGroups.

If Users can belong to multiple groups, create an association table like: AssociationID, UserID, UserGroupID.

Otherwise if each user can only belong to one group, just add a UserGroupID column to the Users table