Link to home
Start Free TrialLog in
Avatar of tcknudson
tcknudson

asked on

Relating User/Login Tables to seperate logical entities

I have four different types of entities that all require authentication in a web application. I want to use a single user login table for authentication. The user table would look like:

PkUser
Login
Password

One of the entities would look like:

PkCustomer
FirstName
LastName
FkUser -> References User Record

My Question: How does the application refer back to the customer when I also have four other tables refering to a user record?

I have altered the User table as follows:

PkUser
Login
Password
FkCustomer ->References Customer Table
FkEmployee - References Employee Table

The records in the reference columns would only be placed in the column that was valid such as customer.

Is there a better way to use a single login/user table with many different types of entities?

Thanks,
Avatar of Clever_Bob
Clever_Bob
Flag of Australia image

I think you are asking a couple of questions in there. One about the most efficient structure of your database tables which stores users and another about how to reference the information?

To answer your first question, your proposed data structure looks pretty good, normalized and logical.

Re: Second question: Given your relatively (at least) knowledge of database design I'm sure you already know this so perhaps I'm missing the point but anyway ....

To reference your data, you'll need a series of 'joins' e.g. select * from usertable where PkUser = '00001' Inner Join .....
Avatar of tcknudson
tcknudson

ASKER

So, having two tables refernece eachothers primary keys using foreign keys is acceptable?

User -> Customer via Fk_User_Customer
Customer -> User via Fk_Customer_User

Thanks,
What is the web application written in? PHP? Are you using mysql for the database?
PHP/Apache -> MS SQL Server
Well you can do one of three things for authentication:

1. A self contained submit where the form submits to itself then runs a php script to authenticate agains a query on the mysql database.
2. A 2nd .php page to handle the processing which is simular to the above.
3. A .htaccess authentecation.
My question is whether or not the table stucture of two tables referencing eachother is a good design or not. The authentication portion is not an issue.
your first design is the best & common practice.
i.e : The user table
PkUser
Login
Password

One of the entities :
PkCustomer
FirstName
LastName
FkUser -> References User Record

the bi-directional fkey....
User -> Customer via Fk_User_Customer
Customer -> User via Fk_Customer_User

IS NOT VALID AND NOT USABLE(if FK columns are 'NOT NULL'). coz, it will always break refferential integrity if either of two table have a new record requiring new FK.


regards,
arshad
So, If I allow the column to be NULL, will this provide a usable sturcture?
ASKER CERTIFIED SOLUTION
Avatar of raahgeer
raahgeer
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial