Database Structure Question

T

tuffgong

[H]ard|Gawd
Joined
Jan 19, 2005
Messages
1,696
I'm setting up a site with three user levels (admin, privileged user and regular user). All three need to have access to data, but the regular users should only have access to data assigned to them.

For example, regular user uploads a photo. Admin and privileged users can see it, and so can the regular user who uploaded it, but not other regular users.

I was thinking of having one SQL table with users and a # 1-3 to specify the type of user. However when setting up the table that holds the data, how would I go about linking it to the user table?
 
Oversimplified: You'll need to add a FK (Foreign Key) column to the Pictures table which is the value of the PK (Primary Key) of the Users table. Then based on the user's level (1 or 2,3) if user's level is1, you can simply call the SQL with a "and fkUserID=" in the where clause.

You PK can be an AutoNum or the user's ID if you allow for distinct IDs. Based on the DB backend used.
 
I'd go with a more powerful privileging system. One limitation of the suggestion provided above is that only 1 normal user can admin an album of photos. If you want to give multiple users write access, that becomes a little bit complicated.

My suggestion: have a separate "access" table for determining who has what kind of access to each album. Something similar to: (albumId, userId, access), value. Each row can be keyed on by the id of the album, the user's unique id, the type of access, and whether or not they have it. With this, you will be able to specify the following information:
for album #500, user #73 has read access => (500, 73, R, true)
but not write access => (500, 73, W, false)
etc.

If you want to make it more complicated, you can add the concept of "classType" and "classId". classType specifies whether this record corresponds to "everyone" vs "user type" vs "unique user". classId would be the id for that particular type. So if you have a classType = 1, classId = 3, that would mean that this particular priv corresponds to user type = 1 (which is admin users). Its a more complicated, but its much more flexible as you can potentially have user defined groups and such.
 
You use what's known as a "joining table", or a "merging table", which is essentially a table that takes two tables of unrelated data and somehow relates them.

Your program logic will have to define what the levels "regular user", "priv user" and "admin user" are, since a database can't do that for you. If someone is a "priv user" or "admin user" you don't give any additional options for allowing access--you simply accept this as-is when needed. For a "regular user" you access a third table, which is a concatenated primary key of "userid" and "photoid". This gives you extensibility down the line if you want to add multiple "regular user" folks to a single photo.

With JUST the example you provided, you would save the "userid" field as a foreign key with the "Photo" table. Then, if the user is a regular user you only query for photos where the "userid" is appropriate; else, you query for all photos, regardless of "userid".

202276
 
Tytalus said:
You use what's known as a "joining table", or a "merging table", which is essentially a table that takes two tables of unrelated data and somehow relates them.
Click to expand...
I can't find many relevant hits on those terms when searching the webberpipes. I'd call it a "relationship" or "relationship table".

Either way, I'm not sure one is necessary. If a user type always has the same rights over all objects, then the user type is a part of the user record as it is invariant for each user.

If the user type varies per item, then a many-to-many relationship table is necessary to record which users have which rights over which objects. Each user potentially has a different right for different objects, so you need a row for each combination. It seems odd to want this, but it's possible; but it's a very expensive way to implement things. If you have 10,000 objects and 1,000 users, then you have 10 million rows... unless you can rely on a common default that you don't record in the database. That is, if the row isn't found, you assume it's "no access", or something.
 
mikeblas said:
I can't find many relevant hits on those terms when searching the webberpipes. I'd call it a "relationship" or "relationship table".

Either way, I'm not sure one is necessary. If a user type always has the same rights over all objects, then the user type is a part of the user record as it is invariant for each user.

If the user type varies per item, then a many-to-many relationship table is necessary to record which users have which rights over which objects. Each user potentially has a different right for different objects, so you need a row for each combination. It seems odd to want this, but it's possible; but it's a very expensive way to implement things. If you have 10,000 objects and 1,000 users, then you have 10 million rows... unless you can rely on a common default that you don't record in the database. That is, if the row isn't found, you assume it's "no access", or something.
Click to expand...

For the system I'm developing right now we assume no rights unless rights are explicitly written to the "relationship table". We've also abstracted things in to groups, which can simplify the access to specific items (i.e. these 50 widgets are part of that widgetgroup, which is then assigned to these 3 people.) Gives us group permissions and individual permissions where the administrator deems necessary, which is pretty flexible.

Back to the original discussion, I did mention (both explicitly and implicitly through example) that a relationship table wouldn't be necessary for the specific example, as stated. If there'll only ever be one and only one owner, then a foreign key for each item inserted which relates it to the person who inserted it will be sufficient, since a "common default" is that anyone with access higher than "regular user" has access to all items.

202276
 
You must log in or register to reply here.
Back
Top