Best way to set up this relationship?

Question

Best way to set up this relationship?

webskater

SSCarpal Tunnel

Points: 4214
More actions
July 4, 2011 at 12:03 pm

#243760

I have tblUsers with UserID as Identity(1,1) NOT NULL - and set as Primary key. I also have a Group table and each user has a GroupID as a Foreign key.
Let's say UserIDs 1,4,5 and 6 are in the same Group.
In the front end I need to allow users to be able to say - 'I'm User 1 - at the moment just show me data relating to Users 4 and 5.' After viewing that data they might want to change to, for example, just viewing the data for their own record and User 6. I want to store their (changing) preferences.
So I envisage a table with two columns.
CREATE TABLE [dbo].[tblUser_WhichUsers](
[UserID] [int],
[WhichUserID] [int])
So, in the first situation this might contain:
UserID_____WhichUserID
1__________4
1__________5
(so, UserID 1 can 'see' UserIDs 4 and 5)
And, in the second situation.
UserID_____WhichUserID
1__________1
1__________6
(so, UserID 1 can 'see' his own record and User 6)
What is the best way to handle this?
My instinct is just to store the data I need in tblUser_WhichUsers and, whenever the User's requirements change, delete their rows and create the new rows to reflect their current wishes.
Or, should I have a third (bit) column in tblUser_WhichUsers and populate the table with every permutation. So (for example) with User 1 in the UserID column and UserIDs, 4,5 and 6 in the WhichUserID columns ...
1 / 1 / yes|no
1 / 4 / yes|no
1 / 5 / yes|no
1 / 6 / yes|no
and then
4 / 4 / yes|no
4 / 1 / yes|no
4 / 5 / yes|no
4 / 6 / yes|no
etc.
If I do this, every time the data changes I'll probably end up passing in data to a stored procedure that might say .... UserID 1 wants to see UserIDs 4,5 ... so I'll then have to run a fairly complicated routine to determine whether the existing records of 1/1, 1/4, 1/5, 1/6 match up with the 1/4 and 1/5 passed in - and set the Yes/No field accordingly.
It seems a lot easier to me to just delete the rows relating to User 1 and then just add 1/4 and 1/5 back in (without the need for the yes/no field) and without the need for any conditions when joining to this table.
How would you do this?
If I go down the 'delete and re-create' route - how should I index the two columns?
Sorry if I haven't been very clear - and thanks for any pointers.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I would create a join table between the Users table and the Groups table (call it Membership). It would be set up just like any other junction table - create a unique index/primary key on the (UserID, GroupID) combination. Then you can move users in and out of groups using inserts/deletes. That way at least is really straightforward.

purushoth.Chennai SSChasing Mays Points: 627 More actions · Answer 2

Hi Sku,

In this scenario what you are following is the best and simple way... if u want to maintain any history pls add timestamp column.. otherwise ur approch will give the simple and good solution...

For data retrival too its a good solution...

Thanks

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Thanks to all for your replies.

What sort of index should I have on my 'join' table.

Like this?

ALTER TABLE tblUser_WhichUsers

ADD CONSTRAINT [PK_tblUser_WhichUsers] PRIMARY KEY CLUSTERED (UserID, WhichUserID)

Is a Clustered Index required? I can't foresee any situation where I will want to Select from the table. All I will ever do is join to it so I determine, for example, that User 1 currently wants to 'see' the date for Users 4 and 5 by joining to User 1 in the 'join' table.

So, is a clustered index required? As I understand it a clustered index means the data in the table is 'saved' sorted by the clustered index. People will be writing to and reading from the join table virtually continuously - is there any point forcing SQL Server to index the data?