The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

A Database design question

Hello,
I want to design a software using SQL server 2000. I am new to database design.

I have a table with unique USER_ID. Each User must be given permission to read one or more specific documents in my site.

For e.g. user 'A' should be allowed to read articles C1, C2 and C5. And user 'B' can see C2, C3, and user 'C' can see all.

So we have 'n' users, and 'X' articles where each user can be allowed to read one or more of the 'X' articles. When I pull up a "add new reader" page, I must be then able to assign the readable articles.

My question is how do you do data design to allow such a possibility (the most effecient way in SQL server 2000). I don't have a good knowledge of DB's - the issue I see here is if there are large number of readers and articles, then how do we map each user to the allowed list of articles?

(Someone may now scream this is a bad system - let's ignore that for now, OK :) I'm throwing an idea for something else but the fundamental principle/problem remains the same)
Anonymouse Database Coward
Tuesday, March 08, 2005
 
 
If I understand your post correctly, one way of implementing what you describe is the following three tables:

User
===============
UserID [PK]
UserName
UserDetails


Article
===============
ArticleID [PK]
ArticleDetails


UserArticle
===============
UserID [PK]
ArticleID [PK]

Hope this helps...
+= Send private email
Tuesday, March 08, 2005
 
 
I'm not a DB expert, but I'd have thought if you want such fine grained detail the obvious solution is a table with

User_ID Article_ID
U1 C1
U1 C4
U1 C5
U2 C2
U2 C3
...

and do a join in a view with the article table. I'd be quite happy to be educated otherwise by a DB expert.
el
Tuesday, March 08, 2005
 
 
You might consider the use of an override at the user_id level to allow a user to just read every article, if that's a valid business case.

You might consider assigning users to roles(eg. ADMIN, STUDENT, MANAGER etc), and then giving document permission to the roles so that for a new document you don't have to give permission to 100 users individually, you can just give it to a couple of roles. Again, that's just if there's a business case of course
David Aldridge Send private email
Tuesday, March 08, 2005
 
 
David,
I did think of it - set certain pre-defined levels and flow them down to each user. But my guess is, at this point, I need a fairly granular level of assignment.
Anonymous Database Coward
Tuesday, March 08, 2005
 
 
For a truly klunky, brute-force solution, you could assign a string for each page.  Then give each user a table entry with the User_ID, and a 'Pages' String containing a comma-delimited list of allowed pages.

Use a regex against the returned string for the page you want to find if it's in the list.

Drawback: If you have a LOT of pages, that could be a LONG string, which then has to get loaded every time you load a page.  Also, you have to assign a unique string for each page (but you have to do that anyway).

Nicer solution: the User_To_Page cross-reference table suggested above, with the appropriate SELECT statement against it.
AllanL5
Tuesday, March 08, 2005
 
 
Oh, and I once participated in a system that TRIED to define the 'Roles' above -- it ALWAYS got bogged down in what should be assigned to which roles.  If the roles exist beforehand, and are a natural fit to the pages, it can work well.

If what you really need is arbitrary permissions, don't bother defining the roles, they just become yet another arbitrary level of detail to be managed.
AllanL5
Tuesday, March 08, 2005
 
 
Everyone,

Thank you for the suggestions. I think +=/el's suggestions are probably the best. A table with user/article mapping should do for my case. I agree with AllanL5's comment on getting bogged down in assigning reader policies :)
Anonymous Database Coward
Tuesday, March 08, 2005
 
 
I'd go with "roles" and users are assigned to a role. This would make it far easier to handle changes in what articles they may view depending on promotion/transfer of responsibility/change in subscription.

take +='s ideas but instead of "user article table"
have:

User
===============
UserID [PK]
RoleID {FK}
UserName
UserDetails

Role (or UserGroup)
===============
RoleID [PK]
RoleDetails

UserArticle
===============
RoleID [PK]
ArticleID [PK]

Otherwise the administration will become a nightmare. Things like "set up Susan with the same rights as Bob" followed a month later by "how come Bob can see this article and I can't" will come back to haunt you.

If you think you will have lots of articles, you might want to group the articles by theme. Think of it as a role for articles. One of the sites we manage has a couple thousand users, 6 roles, several dozen themes, a couple thousand articles. And about 60 new articles each month viewable by some subset of the roles. Some of the articles have "expiration" dates. Some have "can't see before mm/dd/yyyy" dates.
Peter
Tuesday, March 08, 2005
 
 
A comment on role names. "Admin" is a bad name for a role in a business environment. From an IT point of view, "Admin" is usually the most powerful user, someone who controls the system. On the other hand, from a business POV, "Admin" is a level or department (as distinct from "Management" and "Production" etc. An "admin" user may well be a data entry clerk.
LC
Tuesday, March 08, 2005
 
 
Peter,

Good point, though if there's a possibility a user will be in more than one role, we'd need another lookup table.

---

ADC,

If you're looking for a good, short primer on database theory (which includes the same smart answer as you've gone with here), check out Paul Litwin's article "The Fundamentals of Relational Database Design". I've got a copy of it on an old website of mine (with his permission, of course). It's in the Developers' section at http://www.abcdataworks.com. It's fairly Access-centric, but the theory's the same no matter what database you're using.
JeremyNYC Send private email
Wednesday, March 09, 2005
 
 
I looked at that paper too, very nice. Thank you!

And everyone, once again, thank you all for the valuable inputs. I'm getting ahead now, and I have some good ideas that I think will work for my situation.
Anonymous Database Coward
Thursday, March 10, 2005
 
 
This question has been coming up for 25 years, and no one ever gets it right. You describe a many-to-many relation between user and article. For each user there are zero, one, or more records in the user-article table. If a user can read an article there is a record. If the user cannot read the article there is no record. Application programs (ie, SQL statements, C or other language programs) can easily find (in an indexed table) all instances of the user's articles; if the return value is 0 (no record found) then the user has no rights to read any article. No dates, no overrides, no brute force. You are trying to design a PROCESS when you should be designing a table. Do the table, then write the process.
Steve124
Saturday, March 19, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz