Tables for "client" - "login and password&quo

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Tables for "client" - "login and password&quo

Post by mikebr »

Im new to MySQl and I have a question about tables that I just can't seem to get my head around, I have a couple of books that show tables for a database where the user login and password is stored in a separate table which makes me think that that is the accepted or advised way of structuring the database, as in this rough layout:

users - login, ID and password
client - client details
sales - sales info
rentals - rentals info

so if there is one login and password used per client does it not make more sence to have the users columns "login and password" in the clients table as below rather than having them stored as a separate table as above?

client - client details, login, ID and password
sales - sales info
rentals - rentals info

then there is less chance of say the clients details getting logged in one table and maybe not their login and password in the other, also no join is needed when accessing the client info from the user and password, am I misunderstanding this or maybe being misguided in the fact that these tables should be separate rather than combined?

Thanks
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

If the relation between Users and Client is one-to-one then yes you can have them in the same table. Database design is more of an art than a science.

My question to you is this - Can a client have more than one user?
less chance of say the clients details getting logged in one table and maybe not their login and password in the other
This is where your application logic would come in to ensure referential integrity of your data. You would need to check both those tables for successful inserts before commiting the transaction, if one or other was unsuccessful then rollback the transaction. I use the terms commit and rollback, and they are available in certain types of table within MySQL and version 4.x has an implementation of transactions, but you will need to code the rollback (using deletes) and commit (using queries to check) and display appropriate error/success messages.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

If the relation between Users and Client is one-to-one then yes you can have them in the same table. Database design is more of an art than a science.

My question to you is this - Can a client have more than one user?
Or a user have more than one client?, in my case no there is no logic in having more than a one user client relationship but I see what you mean.

Thanks
Post Reply