Advice on how to restrict what a user sees

[SgtBadass]

I've got a database with two main tables. One is 'users' with approximately 35 records which is not likely to increase much, the other is 'companies' with 7000+ records, more being added every week.

Now, each company record has been either visible or not but when visibile they are available to all users. Suddenly there is a requirement to have some users who can only access some companies. For example, 'user1' can only see 400 of the companies. There are no real rules to this, the admin would like to choose which companies they can see.

I've no problem with the interface, I can handle that, but I'm looking for advice on the best way to handle the database. Do I have a new table to join to with a userID and a companyID? Only problem is with this approach, I can see there being tens of thousands of records in the lookup (it would need to be populated initially with all users seeing all companies).

Or perhaps a comma seperated list of all the companies a user can see in one of the fields of the user table?

As I say, ideas welcome, I'm sure there's a simple way of handling this that I've missed.

 

[jpadie]

yes and no (great answer...)

the basic premise you put forward is right (imo). a join table of company id to user id is the way to go.

but you might want to flex the concept of a user entity a bit. instead of a user being a single unique user, introduce the concept of a group of users as a dummy user entity. create a separate table for joining group to users and an appropriate gui for assigning users to groups (i like drag and drop guis for this). create a default group of USERS_ALL and that means that for generally visible companies you have only one entry into the join table.

there are other more granular ways of permissioning users. you might want to check out the pear classes to see whether any of these are out of the box solutions.

lastly: make sure you inde the join table properly!

 

[SgtBadass]

Groups - good idea, I like it. I'll go work on that idea I think.