Database Design - User Table

Question

I am designing a database that has these tables, user_accounts and user_info.

Here's the structure.

*USER_ACCOUNTS*

user_id
user_password
user_type

*USER_INFO*

user_id
user_fullname
user_address
user_email
user_contact_no

Now, my question is, is that design is okay? Somebody told me to merge them into one table. But can't fine the reason why I should merge it.

Any suggestions?

By the way, I'm designing it for authentication of users and for user's personal information.

Thanks.

score 6 · Answer 1 · answered Sep 25 '12 at 19:59

6

You could make that a flat table because the relationship is 1-1 (a user will never have more than 1 record in your user_info table). Nothing will be gained from this table relationship, in fact most queries will take more time because you have to look at two tables instead of one.

answered Sep 25 '12 at 19:59

Scott

12,077
4
27
48

Yes, it's a 1-1 relationship. But the table name user_accounts is used only once when the user logged in to check it's user type. When I will access the user_info to query a user's fullname, I will not go to user_accounts first, but I can go directly to user_info. – user1569143 Sep 25 '12 at 20:12
This is true, but it won't save you too much. The overhead associated with the two extra columns (if you were to combine the tables into 1 table) is very minimal. There is more overhead in the future if you ever have to search both tables at the same time or do a lookup in the first table to get info from the second table or vice versa. I don't think either is completely wrong or right, it depends more on style and how the tables are going to be used in the future. – Scott Sep 25 '12 at 20:16

score 0 · Answer 2 · answered Sep 25 '12 at 19:59

0

You could merge them into one; it would make it more accessible. I do not really see why they need to be separate. Their account would hold their info.

answered Sep 25 '12 at 19:59

Web Owl

567
2
15
29

what is the table name I am going to use sir? – user1569143 Sep 25 '12 at 20:07
@user1569143 Why not just call it "users" if you are doing one table. Short and makes sense, no unnecessary words are used in it. – David Sep 25 '12 at 20:08

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Splitting user information from account information is common, it mainly depends on your overall application. However, I'd recommend the USER_INFO table having a primary id too if you were using two tables, such as:

USER_INFO
- user_info_id (Primary Key)
- user_id (Foreign Key for USER_ACCOUNTS)
- etc

Questions with answers that are on this topic:

Database design: 1 table or 2?

MySQL: multiple tables or one table with many columns?

edited May 23 '17 at 12:07

Community

1
1

answered Sep 25 '12 at 19:59

David

2,053
2
16
26

Thanks for the links. Can I ask, is it not good to have same primary keys in two tables? – user1569143 Sep 25 '12 at 20:13
Well - they wouldn't necessarily be the same if you are using auto increment on both. – David Sep 25 '12 at 20:14
I mean, if that's the case, I could add "user1" to user_info table and "user1" twice to user_info table? – user1569143 Sep 25 '12 at 20:17
Primary Keys should be integers, as it allows for fast queries on the index and minimal storage. – David Sep 25 '12 at 20:33

score 0 · Answer 4 · answered Sep 25 '12 at 20:27

0

I think merge is better option. Number of row for both will remain same and so there is no question of redundant data.

answered Sep 25 '12 at 20:27

Maulzey

4,100
5
22
30

Database Design - User Table

4 Answers4