Implementation Idea for login structure (login id with two different tables)

Question

I am creating a sample web application for a business and I am stuck at the login implementation. There is supposed to be two different users (login types?). I will have employees and customers. I have a table for employees and a table for customers set up already. Let me caveat that I understand the following is not SQL, just the notes I've taken so far in a .txt file.

I have my employees

EMPLOYEES
    Employee_ID  CHAR(5) - 'EM###' - NOT NULL //PK
    ...(columns unimportant to the scope of this question) 
    Web_Login_ID

I have my customers

CUSTOMERS
    Customer_ID //PK
    ...(more columns)
    Web_Login_ID

And here is where I get stuck at the web login

WEB_USER_LOGIN
    Web_Login_ID
    ???

I've read other methods to create a table for login information and they referenced a LEFT JOIN in SQL, but that seems like it would only be good if I was referencing 1 other table and not two different tables. How can I pull EMPLOYEE information if the Login ID entered is from the EMPLOYEE tables and vice versa for customers?

Answer 1

First of all, every thing depends on business logic you have. If employees and customers it's fine to use 2 diferrent tables, to connect them with the table WEB_USER_LOGIN I would just append and field like 'usertype'.

If your database is Oracle you can have object tables to solve this problem, if its PostgreSQL you can hava inherited tables.

If its mysql I would use just one table with both fields, but if you prefer to use the left join it's ok but I would create a VIEW for it.

Answer 2

I made the login table independent of customer and employees. The login table holds a foreign key to a categories table that will hold types of user logins (either customer or employee for now). By keeping it separate, it won't be a requirement for any person to have a web access account. Business rules can dictate if the employee requires an account, it can be created with their initial addition into the server.