Any suggestions for a client/server-ish application design?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Any suggestions for a client/server-ish application design?

  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Smile Any suggestions for a client/server-ish application design?

    Hi guys
    I developed a database application using VB6 for the Adult Education Department at my University, using advice from this thread http://forums.devx.com/showthread.ph...639#post470639

    The staff was previously using MS Excel and MS Word when I came along and they seem pretty pleased with the automated system. Now, they want me to extend it a handful of users with various access levels and permissions. So here I am. Now before I go on with my question, a little about the system:

    Basically there will be two databases - currents and archives. Since at any time the currents database will contain a maximum of several thousand records, I assume I can stick with MS Access (can I really? )
    The archives obviously is huge, holding records from as long ago as 1992 so I used MySQL here. The VB6 front-end interface is the same for both the databases meaning I only use the MySQL connection for the less-frequent querying and back-up operations. The currents database in MS Access meanwhile is accessed through an ADODB connection.

    Now I am looking at providing various user access levels in the system. The authorisation that is required is actually on rows in a table (in the currents database only), rather than on add/update/delete operations. That is, a course officer can only work with courses that are assigned to him/her. Meanwhile the system admin has access to all courses. Authorisation is only required on the currents database.

    And very briefly on the currents database: users are categorised based on the records in the Courses table (Joe is the course officer for Addiction Studies and Psychology, John is the course officer for Family Studies, etc). Tables like Students, Assessments, Course Weighting, etc are linked to the Courses table and hence must be appropriately made available to the authorised users.

    How would I provide authorisation in such a case? I can use views, then I have to move the currents database from Access to MySQL. Is this the only way? I'm also looking for suggestions and advice on implementing concurrency in both the databases. There will of course only ever be less than 10 simultaneous users logged in.

    Any thoughts on this? Cheers guys

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    1. I'd give serious consideration to moving the Access database over to MySQL as well.

    2. Sounds like a perfect time for a UDT. Something along the lines of
    Code:
    Public Type Access_Level
        UserName As String
        Family_Studies As Boolean
        Addiction_Studies As Boolean
        'On and On until you have them all covered
    End Type
    
    Public Access As Access_Level
    Now, you create a couple of tables in your database. One table will have a field for Task_ID, Task_Description, whatever else you might need.

    Entires would be like:
    01 Family Studies
    02 Psychology
    03 History
    04 Sociology
    etc etc

    The next table would be use to actually assign people to these tasks. Fields in this table would be Task_ID, User_Name, whatever else you might need.

    Example of entries:

    02 Hack
    03 weedandhappy
    04 weedandhappy
    etc etc

    Now, when you system starts, you grab the name of logged in user, and query this table. If you found a match for Hack for Family Studies, you would set Access.Family_Studies = True

    Now, in your code, you check the surround all access to all tables in fields in an IF statement. If True, they can run the query or do whatever. If False, they can't....

    It is a bit of work, but you will have complete control over who can do what, when they can do it, and how they can do it, and that control is all managed by one change in one database table field (once, of course, all the code is in place)

  3. #3
    Join Date
    Jan 2007
    Posts
    13

    Smile

    Wow, I never knew I could use user-defined types in VB. Thanks very much Hack

    I had a much basic plan in mind.
    The database would reside on a networked server (could be a PC or a dedicated server). Each course officer would have the front-end installed on their PC.
    At the start of the application, a user login is required. I'd then accordingly create views on the back-end for each course officer that logs in (this can happen at simultaneous times). In the VB6 application I'd create a boolean flag that will indicate the user status: course officer or superuser. Based on this flag the MDI menu options (back-up, etc) will be enabled or disabled.
    So the idea is that each course officer works on his view. While the superuser can in theory work with all the courses, practically he is only required at the end of the academic year to back-up the currents database, or simply to add/modify course officer details. So no hassle there for concurrency control.

    But I guess that means I have to use if statements for every menu option and every join operation, to see what user is logged in. This also of course means moving the currents database from MS Access to MySQL. But I'm still not convinced because say if I do use MySQL and create the course officer views, I'm not quite sure how the joins will be affected. Some of them are quite deep, linking two or more tables, but nothing complex.

    Could you tell me, how it is done commercially? Like would you know if the course officer and superuser applications are two different VB projects? And can I get away with using MS Access for the currents database using your UDT plan?

    Cheers Hack.

  4. #4
    Join Date
    Jan 2007
    Posts
    13
    Sorry if I sound very confused. Guess I'll have to take a closer look at the architecture design, application design and the relevant database choice; in that order?

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by weedandhappy
    But I guess that means I have to use if statements for every menu option and every join operation, to see what user is logged in.
    Menu Options very potentially yes, but I'm not sure what kind of impact it would have on SQL JOINS.
    Quote Originally Posted by weedandhappy
    his also of course means moving the currents database from MS Access to MySQL. But I'm still not convinced because say if I do use MySQL and create the course officer views, I'm not quite sure how the joins will be affected. Some of them are quite deep, linking two or more tables, but nothing complex.
    If the table names and structures in MySQL mirror what you have in Access, then I fail to see why any coding changes would be required.
    Quote Originally Posted by weedandhappy
    Could you tell me, how it is done commercially? Like would you know if the course officer and superuser applications are two different VB projects?
    Any programmer that made them different applications would be required to hand in their commerical programmer's card. There is less than zero reasons why this should be more than one project.
    Quote Originally Posted by weedandhappy
    And can I get away with using MS Access for the currents database using your UDT plan?
    UDTs are an aspect of your front end development language, i.e., Visual Basic....they have nothing whatsoever to do with your database, regardless of what that database might be.

  6. #6
    Join Date
    Jan 2007
    Posts
    13

    Thumbs up

    Hi there Hack,
    Thanks very much for the all your help (in this post and specially in all my other posts until now)

    I tried to use UDT's like you suggested, but it's taking far longer than I expected. While I was working away at the if conditions I got thinking, what is the affect of writing your sql queries in the front-end and what will be different if they sat in the back-end along with the database, and the front-end simply invokes them as necessary. For example, can I have queries for each user's needs in the database and then depending on the user name, invoke those queries in VB. Would you know how it works?

    Thank you.

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Yes...they are called Stored Procedures.

    Have you ever written a Stored Procedure? (Incidentially, MS Access does not support Stored Procedures)

  8. #8
    Join Date
    Jan 2007
    Posts
    13
    Yeah, stored procedures, I remember! Thats another compelling reason to migrate to a proper database system.

    Although for now, I'm going to pretend that Access is sufficient for my meagre needs

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Well, if you aren't going to use stored procedures (and if you are using Access, you aren't goint to use them) at least use parametized queries. That will help with security.

Similar Threads

  1. Replies: 1
    Last Post: 07-28-2007, 09:52 AM
  2. Replies: 3
    Last Post: 01-23-2007, 06:44 AM
  3. Best practices in ASP application design
    By Chris Hansen in forum ASP.NET
    Replies: 0
    Last Post: 02-19-2003, 12:33 AM
  4. Replies: 4
    Last Post: 11-30-2001, 05:13 AM
  5. Web application design techniques
    By Craig in forum ASP.NET
    Replies: 2
    Last Post: 08-25-2000, 03:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center