How do you model multiple user types in db? 4

[dragonwell]

We have two distinct user-types, say Employers and Workers. Being both types will not be supported. Both log in using a username/password account. I am sure I want a "Log-In" table that contains the usernames and passwords, as the logic for creating the account and loggin in is going to be the same in both types.

I also know I will need separate Employer and Worker tables, both with a foreign key on the Log-In table.

I am wondering if this sounds correct. Should the Log-In know if the account is for a Employer or Worker? If so, how?

I figure I could:
1. Use a user-tpyes table, and have a FK column in the Log in table.
2. Have a bit field in the log-in table - 1 employer, 0 worker.
3. Have IsEmployer and IsWorker bit fields in the log-in table.

Option 3 is what I plan to do. Any opinions on this?

Thanks,

-d

 

[stefanwagner]

If you have different data to store, you make have a table 'worker' and a table 'employer' and a table 'login' (<- better name to be found).
Since workers are persons and employers too, both have a FK 'login' to the 'login - id'.
Table login has 'id, name, password'.

This might be extended. Customer might get an login, where to get the third bit?
A new column 'customer' could be created, and a person, which isn't an employee, not an customer, needs to be a worker.
But if a worker is a customer too?

But never, never store the data twice - in worker-table you may find the worker, if you're interested. No need for a bit in login.
If you change the state, you shouldn't need to change it twice. You loose atomicy and get trouble.
Data may more easily corrupt.
A single information should be saved once and only once.
(oaoo-pattern).

Solution 0 is correct - if sometimes you need the e/w info at login, you may define an appopriate query.

 

[LesleyW]

Stefan is absolutely right, I'd like to add my thoughts.....

A worker and an employer (and potentially customer) have more in common than just logins. For example, they all have names (firstname, surname, initials) and addresses. They probably all get paid.

Therefore I would tackle this by having a "Person" or "Party" (party allows you to cater for organisations as well as individuals) with data loginName, password, firstname, surname etc, and a method "login". This is a base class from which there are currently 2 specialisations (classes which inherit from the base class), Worker and Employer. I would expect to see different data on the Worker and Employer classes. You can implement methods on the Worker and Employer classes (e.g. a "Promote" method might only be appropriate for Worker), have them inherit methods (such as login) straight from the Party class, override the Party methods (e.g. you may define a method "give bonus" which is calculated differently for workers and employers).

 

[stevexff]

Agree with Stefan and Lesley, but suggest that the person or party can have a collection of roles.

This allows people to wear multiple 'hats'. By all means use polymorphism to specialise the roles to factor out common methods into a superclass. Then you can easily add new roles later as your system grows, and you don't have to solve the problem of what happens if someone changes from being a worker, to being an employer.

 

[jfrost10]

Agree with Stefan, Lesley, and Steve...and my thoughts...

They've tackled the code implementation, I thought I'd comment on the database implementation.

I'm doing somethign similar, where I have various "types" of people, which all share information but also have distinct information based on their type.

The solution I came up with was to have one common "People" table (name, address, etc.), and tables for the various types that I have (so in your case, workers, employees, etc.). The PK from People would be a foreign key in the other tables, so that if I wanted to see employee specific information on someone, I could get that, as well as any worker information.

hth

D'Arcy

 

[dragonwell]

I should have explained the difference between a worker and employer better I guess. Actually I don't see too much difference in the suggestions compared to my model. So what you are saying is:

TABLE Party(
PartyID, --PK
Username,
Password
)

TABLE Worker(
WorkerID, --PK
PartyID, --FK
FristName, LastName, etc.
)

TABLE Employer(
EmployerID, --PK
PartyID, --FK
CompanyName, etc
)

The log-in account is the ONLY thing the two classes have in common.

>>if sometimes you need the e/w info at login, you may define an appopriate query.

I'll need to know, at least at the object level, the TYPE of party I have. So do I use a subquery to figure that out when I create the party object?
IF PartyID in (SELECT PartyID FROM Worker) THEN
PartyType = 'Worker'

***OR****

Don't use table inheritance at all and just put the username and password fields into both the Employer and Worker entities, since they are so different anyway. (not use a base class) ?

Thanks for your patience with this OO newbie!

Regards,

-d











I should have elaborated on my example in more detail. Employer has nothing in common with worker, EXCEPT the username and password. Employer is an organization and worker is one person.

 

[LesleyW]

If
1) You are absolutely sure that Employer and Worker will never have more in common than logon and password

2) You are sure that you will never need another type of party e.g. customer - or that if you do they won't have any more in common than logon and password

3) You know when you are making the call whether an Employer or Worker is trying to log in e.g. they do it from different URLs or different forms

then I'd say it's acceptable to have 2 tables with the username and password on them and not use a base class. No this is not pure O-O but if you have the conditions above it's a more pragmatic approach.

Another clue as to whether this approach is acceptable or not is to examine your own mental model of what you are doing - if you "see" login as one thing in your mind then you should probably use a base class. If you see it as "EmployerLogin" and "WorkerLogin" then the 2-class, 2-table approach is fine. You need to be totally honest with yourself when examining your mental model. Always helpful to bounce it off someone else who understands the domain - do you work alone or are there other O-O design bods working with you?

Lesley

 

[dragonwell]

There are others I can bounce these ideas, but I'm the OO guy here (scary, yes). I guess it all comes back to the way the program is going to look, as you suggest. Ideally I want ONE log-in form that can figure out if it's a worker or employer logging in, and go from there to the appropriate place. Is that asking for trouble? Should there be a separate form for each type to log in?

thanks for the tips!

-d

 

[stefanwagner]

I would at least build a field 'typ char(1)' with 'w' or 'e' - value.
Uses small amount of memory, and is more flexible than 'boolean worker'.
And makes developing the login more easy, than querying two tables.

But perhaps a sql-query could do it too:

SELECT login.name
        , login.pass
        , worker.'w'
        FROM login, worker
        WHERE worker.login = login.id
UNION 
SELECT login.name
        , login.pass
        , employee.'e'
        FROM login, employee
        WHERE employee.login = login.id;

should give

Kernighan foo 'w'
or 
Ritchie   bar 'e'

Not sure about sql-syntax, but something in this way...