Is Users table suitable for data such as userName and password? 1

[ericnet]

Is users table suitable for data such as user name and password?
The context is one table in SQL 2000 Server for users data (Companies) in a Website marketplace who can access to it through a user name and password. And another table in the same database for users data (personnel of our company) who can access to our Company Intranet through a user name and password.

Users table of Website marketplace registers data such as Company name, company email, address, phone number, ZIP code, tax number, activity,..

Users table of personnel of our Company registers data such as User name, Surname, Company Department, tax number, private address, private zip code, private phone, private email, Social Security Number,..

Are those two tables suitable to register also user name and password? Or I have to create another two tables to register this kind of data?

Thank you

 

[SQLSister]

In the first place your user tables should be split into multiple tables as well. People have multiple addresses and phone numbers and emails.

We keep user login information in a separate table. Of course as part of our business model, there are times when a few users (usually managers) need to have multiple logins to be able to access different parts of the site (such as when a manager is covering for a vacancy in a particular sales territory, he needs to see information for the other territory as well as his own).

An additional consideration is record size. You do not ever want too many fields in the same table or you can have performance problems do to paging issues. Also while SQL Server will let you create a table with fields that add up to more bytes that can be stored in a record, it will not actually store all the information if it exceeds the limit. Therefore any time you have over 8060 bytes of information to store in a row, you will have problems.

"NOTHING is more important in a database than integrity." ESquared

 

[ericnet]

Hi,
Thanks four your answer. If you don’ t mind I would like to clarify some issues

About to split my users tables to allow multiple addresses, phone numbers, emails,.. Can I wait until I need those multiple records? Or is it better to prepare and split them now? Since for the moment I don’ t need them

About keeping user login information in a separate table, with your example of business model I guess you may want to store more data in that table apart of user login information, like some kind of user permissions or something similar in order to access other parts of the site if needed by some users. Could you give me an example of the more habitual additional fields used (logic and kind of data stored) in the user login information table?

Users table of Website Marketplace will also need user login information in a separate table? May I need additional fields in that table? What kind of fields for ex.?

 

[SQLSister]

I would split them now. A future task to split out this information would require an extensive change to the database structure and all the queries that access this data. Plus you have the performance gains from having tables with fewer fields.

I think that this link would be helpful to you inthe design phase of your project

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Bad design is one of the biggest causes of performance problems as the database grows. It is also the most difficult to fix after the fact. It pays tremendous benfits to really think through all the implications of your design before you commit to it. One thing you need to make sure you do is create a unique key (whether natural or artificial) for every table. Names especially are not unique and will need an artificial key of some type. This is the field you will need to join to the related tables.





"NOTHING is more important in a database than integrity." ESquared

 

[ericnet]

Thank you for your aid