How to create a MySQL database for users data

Question

I would like to save user data in my database. There is common data about the user account (nickname, password, etc.) but also data like firstname, name, age, location, ... How can I manage my data base? Should I create different tables? One containing common user data and another containing all the other data?

Answer 1

This is a design choice, and it basically depends on how much information you usually need, and how many extra fields you have.

Option 1: Keep them in the same table, if its not too much or you usually need all the data.

Option 2: Create a User Profile table, that contains the user data that its related to the person and not the account.

Answer 2

create one single table.

CREATE TABLE `admin` 
( 
 `User_Name` varchar(60) NOT NULL,
  `Password` varchar(60) NOT NULL,
 'firstname' varchar(60) not null,
 'Age' int(11) Not null,
 'Location' varchar(50) NOT NULL,
   PRIMARY KEY (`User_Name`) 
)
  ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 3

Create 2 tables: 1. userProfile

create table userProfile(
UserID int primary key auto_increment(1,1),
firstname varchar(50),
age int(11),
location varchar(50));

2.userAccounts:

create table userAccounts(
ID int primary key auto_increment(1,1),
UserID int(11),
UserName varchar(50),
Password varchar(50));

there is a relation between Table1(UserID) and Table2(UserID).