2

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.

Improve this question

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
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.
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.

Improve this answer

2 Comments

what is the table name I am going to use sir?
@user1569143 Why not just call it "users" if you are doing one table. Short and makes sense, no unnecessary words are used in it.
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?

Improve this answer

4 Comments

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

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.