July 15, 2015 at 10:07 am
In my project I have user and group defined as 1 to 1 relationship, meaning one user can have only one group assigned.
I just received a change request to enable any user could have more than one groups assigned, so I am thinking how to handle this request and want to hear options from this forum, here is my preliminary thought:
1. add a new field (let's say byGroup) in User table, this byGroup is a varchar and value like 2,3,9 indicates the user has privilege to group 2,3,9
2. when doing user related info retrieval function, I need to convert the string 2,3,9 to array and then loop the array to get the right info
How do you think this solution? Any suggestion is welcome and appreciated.
Thanks.
July 15, 2015 at 10:20 am
The standard way to handle a many-to-many relationship in an OLTP database is a bridge table, because that is the most efficient. Create a table for group memberships that holds (at least) the primary keys for the person and group tables. You may want to add other fields such as from_date and to_date.
If this is for a data warehouse, there are other options you could consider, but only if number of your groups is relatively small (say under 5).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2015 at 10:28 am
Also, a 1-1 relationship means that not only can the person only have one group, but the group can only have one person. Groups, by definition, allow for multiple entities, so it's unlikely that you really have a 1-1 relationship. You're most likely starting with a many-to-one relationship where many people can be members of any one group, but each person can only be a member of one group.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2015 at 10:33 am
drew.allen (7/15/2015)
Also, a 1-1 relationship means that not only can the person only have one group, but the group can only have one person. Groups, by definition, allow for multiple entities, so it's unlikely that you really have a 1-1 relationship. You're most likely starting with a many-to-one relationship where many people can be members of any one group, but each person can only be a member of one group.Drew
Yes, it is NOT a 1-1 relationship, thanks for the correction.
July 15, 2015 at 10:39 am
drew.allen (7/15/2015)
The standard way to handle a many-to-many relationship in an OLTP database is a bridge table, because that is the most efficient. Create a table for group memberships that holds (at least) the primary keys for the person and group tables. You may want to add other fields such as from_date and to_date.If this is for a data warehouse, there are other options you could consider, but only if number of your groups is relatively small (say under 5).
Drew
I see your point but am not sure if it would be a good one:
I have currently 20 groups and each group's members (all saved in User table) number is usually less than 5.
so currently the user table is like:
id, login, name, groupid, roleid, ...
Group table is like:
id, groupname...
In your suggestion, a "bridge" table is to be created say table bridge, I just don't see how it could be used in my case, can you elaborate? Thanks
July 15, 2015 at 10:44 am
Firstly, do not use concatenated values in a column, just going to be a pain!
😎
Here is a simple ERD to explain the concept
USER HAS MANY GROUPS
+------+ +------------+ +-------+
| USER |-+----+<| USER_GROUP |>+-----+-| GROUP |
+------+(1) (n)+------------+(n) (1)+-------+
MANY USERS IN EACH GROUP
+------+ +-------+
| USER |>+-----+-| GROUP |
+------+ (n) (1)+-------+
July 15, 2015 at 10:55 am
A User table, a Group table and a GroupUser -- or UserGroup -- table.
User ( User_Id, ...other_user_columns... )
Group ( Group_Id, ...other_group_columns... )
GroupUser ( Group_Id, User_Id, ...other_columns_related_only_to_the_COMBINATION_of_Group_and_User only] )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 15, 2015 at 11:22 am
As a concrete example,
DECLARE @group_members TABLE (
user_id INT NOT NULL,
group_id INT NOT NULL,
role_id INT NULL,
from_date DATE DEFAULT SYSDATETIME(),
to_date DATE DEFAULT '9999-12-30'
)
INSERT @group_members(
user_id,
group_id
)
VALUES(1, 2 )
,(1, 5)
,(1, 9)
,(2, 5)
,(2, 8)
,(2, 10)
SELECT *
FROM @group_members
Here user 1 is a member of groups 2, 5, and 9; and user 2 is a member of groups 5, 8, and 10.
A typical query would include
SELECT *
FROM users AS u
INNER JOIN group_memberships AS gm
ON u.user_id = gm.user_id
INNER JOIN groups AS g
ON gm.group_id = g.group_id
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2015 at 11:26 am
Thanks Drew, Eirikur, Scott
I have decided to take your suggestion and create another bridge to avoid using the painful concatenated values in a column.
July 15, 2015 at 11:33 am
halifaxdal (7/15/2015)
Thanks Drew, Eirikur, ScottI have decided to take your suggestion and create another bridge to avoid using the painful concatenated values in a column.
Glad you made the right decision, encountered far too many of the others:pinch: and of course, happy to help.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply