Users table - one table or two ?

Question

i wanna have a Users details stored in the database.. with columns like firstname, last name, username, password, email, cellphone number, activation codes, gender, birthday, occupation, and a few other more. is it good to store all of these on the same table or should i split it between two users and profile ?

Answer 1

One table should be okay. I'd be storing a hash in the password column.

Answer 2

If those are attributes of a User (and they are 1-1) then they belong in the user table.

You would only normally split if there were many columns; then you might create another table in a 1-1 mapping.

Another table is obviously required if there are many profile rows per user.

Answer 3

I suggest you read this article on Wikipedia. about database normalization. It describes the different possibilities and the pros and cons of each. It really depends on what else you want to store and the relationship between the user and its properties.

Answer 4

One table should be good enough.

Two tables or more generally vertical portioning comes in when you want to scale out. So you split your tables in multiple tables where usually the partiotioning criteria is the usage i.e., the most common attributes which are used together are housed in one table and others in another table.

Answer 5

Ideally one table should be used. If the number of columns becomes harder to manage only then you should move them to another table. In that case, ideally, the two tables should have a one-one relationship which you can easily establish by setting the foreign key in the related table as the primary key:

User
-------------------------------
UserID INT NOT NULL PRIMARY KEY


UserProfile
-------------------------------------------------------
UserID INT NOT NULL PRIMARY KEY REFERENCES User(UserID)

Answer 6

Depend on what kind of application it is, it might be different.

for an enterprise application that my users are the employees as well, I would suggest two tables.

1. tbl_UserPersonallInformation (contains the personal information like name, address, email,...)
2. tbl_UserSystemInformation (contains other information like ( Title, JoinedTheCompanyOn, LeftTheCompanyOn)

In systems such as "Document Managements" , "Project Information Managements",... this might be necessary. for example in a company the employees might leave and rejoin after few years and even they will have different job title. The employee had have some activities and records with his old title and he will have some more with the new one. So it should be recorded in the system that with which title (authority) he had done some stuff.