I am designing a back end database that will handle registration and login. I need to be able to deal with different account and authentication types.

The main issue here, that is different from other database design for user login is that a user could have a password to log in, a smart card, both or a combination of other, and I am unsure about null values etc.

Imagine a user registers their company online (on my website), the company details go into my company table, and their details go into my user table.

They then get the "account type" as company administrator (account type table).

That user (company administrator) can now log into their company account online, and register multiple users (in their company).

The issues I have are below, and hopefully my design solves these but I am not sure if I am on the right track.

Each user could have either a password, or a smart card to log in. I have the relationship for password and smart card tables but this means there will be many null objects. Either a user will have a password or a smart card.

Just to clarify my needs. When a user registers the company, they become a company administrator, but they could also set up other administrators and/or users, so that why I have them all in different tables.

I have added further tables as each user must have one or more forms of ID checked, along with being provided one or more access codes that will provide a range of access levels (physical and computer resource based)

I am mainly concerned that I have the right type of database normalization AND relationship design.

Lastly, the Smart Card table will have multiple smart card entries that I create, and I need to be able to assign 1 or more to a company, then, the company administrator can assign one or more of these to a user belonging to that company.

Are my relations OK for this? What about null values until I have the smart cards assigned, is this OK? Any help and guidance would be great.

And just to confirm, this will be a back end SQL database on my server, accessed by online web app AND end user desktop applications written in C#enter image description here

  • This looks a little over-engineered, consider that it will get more complex over time with number of requirements later on. From feeling, LoginType and AccountStatus should be managed in a class in the program instead of in the database. Also, I don't understand why Accesscode and UserAccessCodes are separate, are you expecting the accesscode table to carry more columns later on? – Harry Jan 26 '19 at 1:09
  • Thanks for this. The access code and user access codes are separate as they are a many to many relationship. An access code can belong to multiple users, but a user has multiple access codes. My main concern is how to handle login that could be either a password, smart card or both? Thanks again – Stephen D Jan 26 '19 at 12:25
  • Also I felt user type and status should be in the database, as the login type is for example company admin, admin, user etc. And the stays is weather the account is active or blocked for example. My code would not know this. Thanks. – Stephen D Jan 26 '19 at 12:26
  • 1
    All tables have a column named ID: in a join this will become a mess. user.ID should be user.UserID and so on. Moreover because you name it so when used as a relation in a foreign table: imo is safer if the same field with the same content/meaning always have the same name. – Paolo Jan 27 '19 at 8:33
  • Both table looks like lookup: if you change the lookup list is easier to update a table than recompile a c# client and redeploy it to you-hope-few client machines... – Paolo Jan 27 '19 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.