DB design; what is wrong?
-
Hi!
I'm building a (king of) HelpDesk aplication -- ASP & SQL;
I have an db-design challenge:
Bellow is an image of te structure of the tables and their relationships...
=BACKGROUND ISSUES=
There are 4 access levels for users (wich is 4 diferent kind of users), lets say:
"Admin - Keeps core tables funcional & clean, grant previleges to users... "
"HelpDesk - All the Request are posted in the 1º place, to this Users"
"Depts. Users - If HelpDesk Users cant handle the request, then REDIRECT it to all the needed Departments, for resolution"
"Coporate Users - Everybody that post Requests in the corporation"
=Things to be aware of=
There are 2 kinds of STATUS -- the Request Status [OPEN; PENDING; CLOSED...]
and the Awsers Status [OPEN; CLOSED] -- the main goal is to provide some automatism: when ALL the Awsers to a given Request are closed, then the Request is automaticly (pre)CLOSED, therefore, all Dept. Uses must close their Awsers (it defaults to OPEN when inserting new...)
Another drawback is that, depending of User Role, the main view of the aplication must filter the Request, i.e, HelpDesk Users can view ALL Requests; Dept. Users can only see Request assigned to their Dept; and Coporate Users only see Request posted by themselves.
=Example flow=
"A Coporate User posts a new request = 'My Printer wont work!'; it will be received by a HelpDesk User; if HelpDesk cant solve, will redirect to Tech. Dept. AND Assit. Dept. At this point the Status of the Request is PENDING; "
Dept. Users assigned to that Dept. will follow the Request by posting Awsers, like "Searching on the net for the drivers..." = status OPEN; when they solve is part of the problem, they close theirs Awsers; if ALL the Awsers, for ALL Depts. are closed them the Request is auto-closed.)
My problem arises at the DB design level:
I've bring up the design show in the GIF, but i think that is not correct at all... is there a better way to acomplish this?????
Thanks for your time 🙂
@Rodrigues
Click here to open pic. on Browser
(Sorry for the BIG picture 🙂
@Rodrigues
LabDev
labdev@iol.pt -
Hi,
Difficult to grasp the ideas behind your design completely. I guess your biggest problem is the 'multiple answers for one request'.
If I understand it correctly, you add records to the MultiDistribution table when a problem is redirected.
If that's the case, I would add a link between Answer and MultiDistribution. That's the easiest way of knowing which departments have already posted an answer.
But, if you do that, you might rethink the direct link between request and answer. Remove that and always add a 'default' distribution to only the helpdesk department.
And something else, from previous experience, I know that in this kind of apps, it is very often necessary to have some kind of link between requests (descendants, follow-ups, same problem, ...). You might want to add that too.
I hope I gave you some ideas to think about. Please bear in mind that it is really difficult to grasp your state of mind when designing the DB. I probably overlooked tons of features and requirements that you incorporated in the design.
-
First of all: thanks a lot for the quick response 🙂
Weel, i know that it is very dificult to someone actually "think" the way i do, but you seem to get the point 🙂
And yes, i populate MultiDistribution table when a new Request arrives, even if it is NOT redirected (default Redirection is actually HelDesk);
Must test: i'll add a link between Answer and MultiDistribution, and reove the direct link (Requests -> Awsers).
It occured to me the same thing: will they want in a short future that kind of stuff??? (links between requests)
My problem here, is not really the optimal design, but trying to produce a design that can work this way (without become too heavy, or slow):
- Main app. screen is a common table-list-type of ASP (paged, 18 recs. per page)
This screen will be heavy-used! Users must be able to Filter Requests (4 kinds of filter parameters), sort by, and must suport some king of Advanced Search.
Aditionally, information displayed must be most complete as possible, i.e, must include this fields: RequestNr, DatePosted, WhoPosted, Subject, Distribution, NrOfAwsers_Already_Posted.
At the moment, i look at the db-design and start to think:
Well, for each Request record i must (multiple) JOIN tables, starting with MultiDist, to get to Depts, to Get to Users, to get to Awsers!
Wouldnt the queries become to heavy ???
(And yes, i read all about good use of Indexes!)
Other features have been requested, but i can handle them, except one:
I must limit the operations whitin the application (modifications, redirections, filters, and so on -- browser based, ASP) based on Users Role...
For now, i've been doing so with Login pages, and checking for Session("UserRole") var. But i know that there must be a much more efecient method for this, right???
Thanks all, folks!
You're great 🙂
@Rodrigues
LabDev
labdev@iol.pt -
Maybe if you can "visualize" the main screen of the App. it would help 🙂
Get it here:
http://www.sgce.pt/comum/HD1.gif
@Rodrigues
LabDev
labdev@iol.pt -
App looks good.
You shouldn't worry about the performance of the queries. One of the apps we have built (in ASP) is using a 'filter' function on at least 15 fields. We have constructed a view that is a union of three queries, each joining at least 7 tables.
-
For your question with user permissions, I am not sure how you should solve that.
Your solution using the session object is one possibility (probably the simplest).
Another possibility I can think of is using stored procedures for all database access. Inside each, you can check security rights.
A third one, could be to use the SQL security model, just assigning the proper rights to the different objects in your database.
But again, you should consult someone else for an optimal solution.
-
Ok, thank you all!
The screen you see is the current beta work (it will go into production next week)... Until today, it was been tested with 20-40 people posting and redirecting Requests, and it works fine...
My goal now, is (i've been reading Code-centric: Stored Procedures and triggers... http://www.amazon.com, excelent book!) to take all the inline SQL code and parse it all on SP's, thus separating app-logic from design 😉
I'm sure it will boost somehow the performance -- this app. will be used by 1.500+ people (estimated concurrent db access's = 100+);
Thats why i'm worryied about performance!
Best regards,
@Rodrigues
LabDev
labdev@iol.pt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply