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,
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,
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,
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?
ASKER
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.
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.
ASKER
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
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
ASKER
So, If I allow the column to be NULL, will this provide a usable sturcture?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 .....