• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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,
0
tcknudson
Asked:
tcknudson
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Clever_BobCommented:
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 .....
0
 
tcknudsonAuthor Commented:
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,
0
 
thomaswrightCommented:
What is the web application written in? PHP? Are you using mysql for the database?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
tcknudsonAuthor Commented:
PHP/Apache -> MS SQL Server
0
 
thomaswrightCommented:
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.
0
 
tcknudsonAuthor Commented:
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.
0
 
raahgeerCommented:
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
0
 
tcknudsonAuthor Commented:
So, If I allow the column to be NULL, will this provide a usable sturcture?
0
 
raahgeerCommented:
Depends upon your program requirement. what I understood is, user to customer has 1 to many relationship. if so, then your structure is perfect. you'll first have a user table with users. then customer table have a FKuser column which reflects, for every customer row there will be a refference of user. a user row cannot contain multiple custome id (in your case PkCustomer), right?

if you create another table which needs to have user refference, u should also create a FKuser into the new table.

if you create bi-directional refference like ..
User -> Customer via Fk_User_Customer
Customer -> User via Fk_Customer_User

and you need to drop the table somehow (may be for maintanance/debugging etc) which one you're first going to drop? any table you try to drop will generate exception that other table depends on it. so, normally no one uses bi-directional refference.

allowing null is totally depends upon business logic so you should take the decision.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now