Results 1 to 7 of 7
  1. #1
    xilogica is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    Angry Designing a multi user/account login system - pulling my hair out :(

    Hi,
    First of all this is my first time posting so apologies if this is not the best place for my question. I am new to database design and using Microsoft Access to build a working proof of concept before developing an SQL server back end database for my project.

    I will explain the purpose of my database first if that is OK.

    I will have a page on my website where a user can register a company they work for. That person would then become the company administrator for that company (these details are stored in my companyAccount table).

    The company administrator can then log into their account online with their password, and they can then register individual user accounts. These are people who work for that company, and the details of each user go into the UserAccounts table.

    You will see I have an authentication table for both the Company account and the User accounts, and the reason for this is that the Company Administrator can log in online using their password to create, delete and modify user accounts, however, each user account can only log into the system using their assigned smart card.

    I am still not sure that this is the best way to do this as after lots of research I am seeing the role based set up more and more, but I struggled to implement this based on my design and requirements.

    Also, once a Company Administrator has registered one or more user accounts, he needs to be able to assign 1x smart card to each user from the available pool of smart cards.

    This is the next part I am really struggling with.

    As the database owner, I would like to create a number of smart card entries, these will go into the smart card table and are identified by a unique serial number (e.g 1234 1234 1234 123) and I somehow need to assign a bunch of these to a "company account" such that when the company administrator is logged in, he can assign smart cards based on the serial number, only if the smart card has been assigned to his company account - hope that makes sense.

    I have tried a few different designs and just when I feel I am getting there I run into issues when I create forms to populate data, create users, and assign smart cards etc.

    Click image for larger version. 

Name:	schema.JPG 
Views:	23 
Size:	68.5 KB 
ID:	37024



    The above image shows what I have so far, however, as my last design failed and I have started from scratch it is unfinished.

    So just to summarise what I am trying to achieve.

    1. I create a number of smart cards and can later assign 1 or more to a company account.
    2. A person can register a company account, and then log in to create 1 or more user accounts.
    3. The company account administrator can assign 1x smart card to each user account, only from a pool of cards assigned to that company account.

    The forms I would like to create in Access are.

    1. Form for myself to create smart card entries, and later assign them to company accounts.
    2. A new company registration form where a person registers a new company and can then create multiple users.

    One last thing. I feel that by having contact details in both the company account and user account this is duplicating info and going against relational standards (I think) however, when I had everything seperated out it just became a mess, and I feel that although it is not the best approach, it seems to make sense to keep the company administrator separate from his/her list of user accounts.

    Sorry if any of this is not clear, I have tried to explain the best I can and happy to answer any further questions.

    Thank you very much in advance for any help or assistance offered.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    think you need to clarify how your data is organised - you say a user goes to your website to register. Where is you access db located? How do users access your access db once registered?

  3. #3
    xilogica is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Hi. I did say at the beginning of my post that the access database was a proof of concept and the final database will be an sql base on my server. Thanks

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I create a number of smart cards and can later assign 1 or more to a company account.
    You do this the same way you relate multiple user accounts to a company - by having a common field between tables. As I see it, all you're missing for this part is a CompanyID field in your cards table. However, if you wish to create many card records without having a corresponding company id, you might consider a linking table to hold the relational records for company/card. Without the table, it's an update situation on the card table when they're assigned. With the linking table, it's an append situation on the linking table. Not a big deal either way AFAIC.

    Advise not to repeat field names between tables, such as DateAccountCreated. You will find that a) becomes confusing b) causes ambiguity, which can only then be fixed by always having to include the table names whenever referencing such fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    xilogica is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Thanks for the info. So are you saying my design is pretty much ok as is and not too complex?

    One last thing. Should I have just a user account and have the option to assign the user as the company administrator rather than have these details in the company account table?

    Thanks again. Much appreciated

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Other than multiple instances of identical field names (and some very long ones to boot). I also overlooked that the authentication table is useless as is, seeing as how you're still developing. As for issues with developed forms, that would be an example of why I advocate building queries for form/reports first. If they don't do what's needed, having a form that is based on it is a waste of effort, and I seldom base a form on a table. For your db, you might want to consider not bothering with a user login process. There is a way to prevent users who aren't "active" in the user table from gaining access but if wouldn't be the way to go if the pc is shared and people don't routinely log out of it.

    re "one last thing" if the admin could also be a user, I'd have a field for this in user table. Most people would probably use a Y/N field. Some would use a number (integer) where 0 is False and -1 is True. In Access, 0 is always false, any other number would be evaluate to true.

    I find it interesting that you'd want DOB for a company rep. If I were that person, I'd object.

  7. #7
    xilogica is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Thanks. I am not sure what you mean regarding building the queries first?

    Regarding the the auth stuff it is not complete, however I feel it is required as eventually the database and my app will sit on a server. This system will be used by end users running my desktop client software. And all users other than the company admin (the one who registers the company) will log in via smart card.

    Lastly, I capture DOB as that is a requirement for the root CA to issue my end users with digital certificates for the smart cards.

    Thanks again. You have really helped so far.

    Kindest regards
    Stephen

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pulling hair out.....
    By claysea in forum Access
    Replies: 12
    Last Post: 09-26-2013, 05:35 PM
  2. Multi- User Login
    By Tom Lovick in forum Access
    Replies: 1
    Last Post: 02-11-2012, 12:20 PM
  3. Replies: 3
    Last Post: 09-22-2011, 03:35 PM
  4. ^^^Pulling out my hair with a form!!!
    By Casper2012 in forum Forms
    Replies: 7
    Last Post: 08-12-2011, 12:59 PM
  5. access newbie pulling his hair out
    By merchantguy in forum Forms
    Replies: 3
    Last Post: 12-30-2010, 05:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums