determining user types upon authentication

Question

I've got 3 different types of users (entities) that will log into a website in developing for a client.

"FITTER", "CLIENT", "ADMIN"

the first method I came up with was this.

CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id              | id           | id
client          | title        | firstname
office          | firstname    | surname
address_ordinal | surname      | username
postcode        | username     | password
username        | password     | 
password        |              | 

a separate table for each user with the usernames and password inside.

This would require the user to select the type of user they want to log in as via a drop down box.

I thought that was rubbish, I just want a form with a username and password box and the application will do all the logic.

---

so i came up with this:

AUTHENTICATE | CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id           | id              | id           | id
username     | client          | title        | firstname
password     | office          | firstname    | surname
type         | address_ordinal | surname      | 
type_link    | postcode        |              | 

Now there is one table with ALL the usernames and passwords.

The Authenticate.type record would either be a string of "FITTER", "ADMIN" or "CLIENT"

the Authenticate.type_link would be the ID of the table that is in authenticate.type!

So this record...

AUTHENTICATE
id | username | password     | type   | type_link
-------------------------------------------------
3  | john     | BF45DE4192DF | FITTER | 4

will authorize whoever's ID is 4 in the FITTER TABLE.

---

My question is...

Is there a better way to do this?

There must be right?

because this method essentially uses authenticate.type and authenticate.type_link as a composite key.

Answer 1

For the entities and attributes you've described so far and assuming I've understood the domain, I can't see a need for anything more than the tables below. If you there are attributes specific to a Fitter or Admin, add distinct tables for those attributes.

CREATE TABLE UserType
(
    UserTypeId INT
    , UserType NVARCHAR(50)
    , CONSTRAINT PK_UserType PRIMARY KEY (UserTypeId)
)

CREATE TABLE Address
(
    AddressId INT
    , Line1 NVARCHAR(50)
    , Line2 NVARCHAR(50)
    , Line3 NVARCHAR(50)
    , Line4 NVARCHAR(50)
    , Line5 NVARCHAR(50)
    , Postcode NVARCHAR(20)
    , Country NVARCHAR(50)
    , CONSTRAINT PK_Address PRIMARY KEY (AddressId)
)

CREATE TABLE [User]
(
    UserId INT
    , UserTypeId INT
    , UserName NVARCHAR(50)
    , Password NVARCHAR(50)
    , Title NVARCHAR(50)
    , Forename NVARCHAR(50)
    , Surname NVARCHAR(50)
    , CONSTRAINT PK_User PRIMARY KEY (UserId)
    , CONSTRAINT FK_User_UserType FOREIGN KEY (UserTypeId) REFERENCES UserType(UserTypeId)
)

CREATE TABLE Client
(
    ClientId INT
    , Name NVARCHAR(50)
    , AddressId INT
    , CONSTRAINT PK_Client PRIMARY KEY (ClientId)
    , CONSTRAINT FK_Client_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId)
)

CREATE TABLE ClientUser
(
    ClientId INT
    , UserId INT
    , CONSTRAINT PK_ClientUser PRIMARY KEY (ClientId, UserId)
    , CONSTRAINT FK_ClientUser_Client FOREIGN KEY (ClientId) REFERENCES Client(ClientId)
    , CONSTRAINT FK_ClientUser_User FOREIGN KEY (UserId) REFERENCES [User](UserId)
)