i have three tables to store details of different types of uses: customers, suppliers, staff
here are the structures of them,
customer(id,f_name,....)
supplier(id, name, address....)
employee(id, name, job_title....)
now i need those to log-in to the system , the log-in details stored in separate table,
user(user_id, password, role, ref_id)
how i planed to work this is, when registering some one, firstly insert the record to customer, supplier or employee according to the person, then insert a record in to the USER table in which the "ref_id" is the id of the previous table. the user is provided the "user_id" which can not be changed and they can change their password themselves.
when log-in, check the user_id, password combination, if ok then takes the ref_id and type, the appropriate table can be determined by the type which may be customer, supplier or employee....
the reason i done this in above way is, customer, supplier and employee table has many different attributes except few like id, name...so can not maintain all the data in one table. in this situation if we use ids of customer, supplier and employee..would provide duplicate ids because they are separate tables!
so i need to know,
- Is it correct the way i have implemented the authentication ?
- if it isn't what is the correct way? (please mentioned that the details of the three parties should be handled separately)
i need to define relationship between supplier, customer, employee --> with user table. so is it ok to define three relationship as follows or another solution, how if the user table keep alone without relationship? is it violate the relational database concept?
customer (id-pk) ---->user (ref_id-fk) supplier (id-pk) ---->user (ref_id-fk) employee (id-pk) ---->user (ref_id-fk)
