If email address is a login to my webpage -how should I design the very simple database behind it?

Question

I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep a basic user profile information like:

• street
• city
• zip code
• mobile phone
• country
• contact name

And the user's email and a hash of their password (of course). What is more, when user changes any of his data, I want to keep track of it and store it too. I know storing all of this data in one table is a bad idea, but I cannot come up with a better solution for now. Can you help me with that and suggest a way of dividing this data into different tables?

Answer 1

To keep all data in a single table is a bad idea if there are too many repetitive long data.

For example, if the name of the country United States of America, and there are lot of users registered with that country name, this is the best practice to create another table to keep names of countries with some code, and in the main table insert only code of country

Moreover, there is no guarantee separating them makes your database schema optimal.

If you haven't idea how to design database read about normalization forms. This are the techniques proven by time.

In your case something like the following could be managed:

user(id, login, name, password)

user_actions(user_id, action_type, action_time)

user_specifics(user_id, address, phone, country_code)

country_name(country_code, name)