NZ_Kiwis wrote:To explain that further, let's say I have Function A through G. User 1, might have access to functions A, B and G while user 2 has B, D and E, user 3 has A, E and G and so forth.
At some point, I may also get Function H, I and J.
In other words, you have a User entity and a Function entity, and there is a many-to-many relationship between them. Therefore, representing User by a table and Function by a table, you should have a third table to serve as the join table to represent this many-to-many relationship.
NZ_Kiwis wrote:I don't want to have another table for each 'function' then a sperate table linking each privilege and each function to each user (which seems the most straightforward approach but also means having 3 tables at a minimum to allow users access).
This straightforward approach is typically the correct one where a many-to-many relationship exusts. That you have a third table is no big deal because the table would essentially consist of two integer columns, i.e., the primary keys of the tables to be joined. You might have a third column for this table's own primary key, and sometimes there may be other columns for ancillary data, but essentially it is just two indexed integer columns.
Of course, if you insist, you don't have to have this third table. You could have a function column in the User table that is a many-valued field, and then you have to do extra parsing in PHP to figure out what functions a user has, and if you want to know the functions associated with a user, you would have to run an additional query, whereas with three tables you would just have needed one query with joins. Or you could have N function columns in the User table, and then have to cope with this rather awkward situation of what is essentially the same field being repeated... and who knows how big N should be?
(Then again, if you're concerned about three tables, why have two? You actually only need one table! :p )