Inefficient way?

Anon

Hi there,

Currently i'm working on an application for a company it has to be some sort of a filesharing system. There are at +/- 100 users in the database, including a few admin-users. The admin users are allowed to create groups and to put the rest of the users in one or more groups. One user can occur in more than one group. Here was my solution:

Groupname User_ids
"group_1" 1,4,6,21,31,54
"group_2" 1,4,23,31,64

So... user 1, 4, 31 are in both groups. Users can upload files and say which groups are allowed to read that file. So I build a new table:

Filename Group_id
"test.doc" 1, 2
"test2.doc" 2

Okay... everything clear so far? Here's my problem. When a user logs in he gets a list of files that he can read. So I have to check if the user is in the group that can read the file, but i think that my solution as I described above isn't very efficient.
Because now i first have to "explode" the users in the group and then compare these id's with the id of the logged-in user.

Is this the only way to do this, or does someone have a better idea?
Thanks alot!!

Greetz,
Daniel

[deleted]

Indeed that method is not good.
What you should do is ad a new table to hold the actual ids of users who have a certain right.

GROUPS:
groupid | groupname
1 | firstgroup
2 | secondgroup

USERS_IN_GROUPS:
groupid | userid
1 | 6
1 | 8
1 | 100
2 | 56
1 | 9

now, users 1 8 and 9 belong to group 1.
You can get all users per group by selecting all userids from the second table where groupid is whatever group you are looking for.

You can do the same with the file rights:

FILE_RIGHTS
fileid | groupid
1 | 2
1 | 7

Now file "/foo/bar/text.txt" has ID=1, and it can be accessed by groups 2 and 7. (2 must be in there, because the uploader is in that group.)

Anon

Thanx for your help, but I still have a question.
When the admin users adds a new group with 10 users, then there have to be 10 query's executed. Isn't that bad?

Greetz,
Daniel

lxghak

Not really. Just think to yourself how often an admin will be adding a group versus the number of time someone will be reading a file. 10 queries is nothing compared to something that will be serving 100 different users at any given time. You want to make THAT part extremely efficient; don't worry too much about making events efficient that only occur once in a while.

Granted, the best program plan will be totally efficient in every respect; however, we are coding here... not making a senior project in college.

[deleted]

Quite correct.

Performance is more than just speed, it is also flexibility and reliability.
A normalized database design does take more effort to fill, but much less to read, and it is much more flexible and reliable.