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.
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.