A simple design question

[srajeevkumar]

Hi freinds,

Well I hope its right to ask this question in this forum. Basically I am from database design background and have been asked to design a database which will be driving some asp pages. I am no familiar with asp , but atleast people in this forum atleast knows whats expected from the database for creating such a dynamic page.

The requirement is pretty simple. Its to create

(1) an online directory whcih will be accessed by over 2000 users potentially.
(2)Depending on the profile and credentials of the user they should be able to have acess only to some group of pdf and not the other groups.
(3)Assume that we have some 20 different groups of pdf files.

Now given such a scenario how would you design your application and the database.

The actual requirement is much more complex , but then any pointers to this will help me have an idea on how other professionals think given with similar tasks etc.

Tasks
(1)Now the design task will to design the file structure in holding the pdf files.
(2)The database structure to store and retrieve the rights and permissions for each user.
(3)How to design the ASP pags based on the information retrieved from the database about the logged in user.

I would have put in my thoughts as what i think will be best way to go forward, but then I didnt want to pre empt anyone , so I shall let the thought flow freely before I put in my thoughts too.

Rgds

 

[Tarwn]

I'm going to start with the files, as I think the rest will fall into line after that.
Your biggest concern is gong to be that if a user uses someone elses computer they would have the capability of downloading any files the other user had simply becaus they will still be in the address bar of the browser. The easiest way around this is to create a download page that BinaryRead's the requested file then streams that data to the user with a pdf ContentType. Since all of the documents will be downloaded from this one page, the actual file won't be in the address bar, keeping your security intact. (More on how to implement this later).
Now that you have the capability of controlling file access, you need to implement your security. I would suggest a three table system (or perhaps 4 if group names are important):
UserCreds Table - has any user specific info (name, email, whatever) and a primary key called user_id
UserGroup Table - has a user_id and group_id to tie specific users into specific groups. May want to add a field for allow/deny or you can just assume if they don't have an entry for a certain group then it will be a deny. user_id and group_id are both foreign keys.
FileGroups Table - unique key of group_id, name of group, etc
Files Table - unique key of file_id, foreign key group_id, path to file

Relationships will look like:
UserCreds <-one-to-many-> UserGroup <-many-to-one-> FileGroups <-one-to-many-> Files

One User can have multiple groups they can access, but each access record in UserGroup only connects to a single group in FileGroups. Each entry in FileGroups can have many files it represents.

Web Stuff:

A login page: A login page will allow you to figure out which user is logging in. This can be a simple static html page with a login form.

A verification page: Verify the users entered username/password from the UserCreds table. If they are valid then set a Session or Cookie value to hold onto their user_id. Redirect them to the File Listing page if thy are valid, or redirect them back to login if they were invalid

A file listing page: Build an SQL statement to "SELECT file_id, file_name, group_name FROM ((UserGroup INNER JOIN FileGroups ON UserGroup.group_id = FileGroups.group_id) INNER JOIN Files on FileGroups.group_id = Files.group_id) WHERE UserGroup.user_id = " & TheirUserId & " ORDER BY group_name, file_name"
Now you can output all the files in their groups that this particular user can access. You will want to use the file_id so it can be passed to the downloads page. We actually want the file_id passed in the form so it won't be vsible to the end user, this is something your web person will have to work out.

Downloads page: Accepts a file_id from the file listing page. Queries the Files table for the path that is relevant to that id, uses ADOStreamObject.BinaryRead to read the file and Response.BinaryWrite to write it. You can find exampes of this throughout the ASP forum or google should return you some good examples on a search string like "ASP force downloads" or "ASP downloads page binarywrite"


Basically that should have you covered. You have login, verification, file selection, and downloading. All of your data is in the database, etc.


The one thing to keep in mind anytime you design a database is that the developer doesn't matter. In the future te database your ceating for one web application has the ability to be used by mutliple other applications (web or desktop). So stick to good design principles and the database will be solid not only for the developer but for any future developers or for the next database admin (if you were to get hit by a bus).

Hope this has helped,
-T

 

[srajeevkumar]

Hi Tarwn ,

Thanks very much for your invaluable help, tips and suggestions on this. But I think I need to give you a bit more information at this stage so that we can take it to a bit more closer to reality.

(1) The 2000 odd users will be scatterred all around the world and it will be only those users which we auhorise from a particular business class. Not anyone can join as a user to the directory. So the question of one user seeing another user's address links is not there since they are all geographically scatterred. But then the problem of an user accessing those files which he is not authorised to see can occur, which is quite similar to what you mentioned. Bu then your solutions of file stream reading and writing will prevent showing any directory paths being made visible in the address bar.

(2)Eventhough I called it an online directory. basically when displaying the pages , obviously the look and feel factor needs ample consideration as well. Means maybe when the user logs in it will take them to a page which show them only the links to the file groups they can view in some interface. And when they click on any of the file group link it will take them to all the files which are accessible to them in the form of thumb nails. Now when they click these thumb nails it will allow them to view the actual file onscreen , be it a pdf file or an image file. Now if they want to save the file or print the file its upto the user. But by default it needn't prompt them to download.

(3)I was initially thinking of organising the files in various sub flders etc based on the groupings I wanted to do. But now looking at the database structure you have proposed it seems is best to leave all the files in the same folder and then attach each file to a particular group by means of the files table.

(4) In the database design part , between the Usercreds table and UserGroup table you have mentioned a one to many relationship. But in reality an user can be part of any number of groups and also each group can have as many users as they want. So isnt it a many to many relationship.

Also thanks very much for the requirement of designing the database thoroughly with a view of expandability and use by other applications.

In view of the above any more inputs is highly welcome for a discussion.

Rgds

 

[Tarwn]

(2) & (3) You can easily introduce an intermediary page between login and file listing by doing a SELECT on only the user id from UserGroups and Groups to get the groups they are part of. From this page you would want to pass te group id to the file listing page then display all the file icons from the db for that one group. You could even set up an extra field in the file records for the file type to more easily choose which icon to place next to the file. Another thing you may want to do is allow the person who is entering file names into the db to enter both a path AND a display name. That way they can provide a more descriptive name to be displayed to the end user and won't have to worry about the user seeing SomeFinancialFile.pdf(2) in the list (this is just a prettiness thing really).
As far as viewing on the screen, most browsers will automatically display filetypes for which they hav a plugin or the capability to view them. It's actually a little harder to force a download, usually people start with it opening by itself and then fight with the system until it forces a download instead of displaying by default.

(4) With the database design I used, the UserGroups table is actually what breaks up the Many-to-Many relationship between users and groups. The table basically defines the relationship between the UserCred and FileGroup tables so that you wouldn't have to place repetitve information in either.
Example:
Suppose we have two users (Bob, Sue) and 3 Groups (A, B, C)
Bob and Sue would each have one record in UserCreds
Groups A, B, and C would each have a single record in FileGroups
Now it is entirely possible that Bob can be allowed to access multiple groups and also possible that a single group could have multiple users allowed to access it, this is the many-to-many relationship you pointed out.
But, with the addition of the UserGroups table in the middle we change the many-to-many relationship into two one-to-many relationships.
One User record can reference multiple UserGroup records, but one UserGroup record cannot reference multiple UserCreds. One FileGroup record can be linked to multiple UserGroup records, but one UserGroup record can not link to multiple FileGroups records.

Back to our Example:
Say Bob has user_id 2 and Sue has user_id 4
Group A has group_id 1, Group B has group_id 2, Group C has group_id 3
If Bob is allowed to look at A and B and Sue is allowed to look at B and C then your UserGroups table will look like:
user_id group_id
2 1
2 2
4 2
4 3

---

So the middle table (UserGroups) serves as an intermediary in a many-to-many relationship and reduces the duplication of data we would be risking were it not there. Technically it can still be referred to as a Many-to-Many relationship but I prefer to look at it as two One-to-Many relationships, especially considering tat the UserGroups table may have it's own individual informaiton in each record (such as an access granted date, or an access counter, etc). Since UserGroup records can hold meaningful, unique information, I prefer not to just see it as a link table.

-T