Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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)
)
share|improve this answer
    
Just curious, whats the "[ ]" around the CREATE TABLE [User] –  AlexMorley-Finch Jan 12 '12 at 8:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.