How to store user specific data in a database [closed]

Question

I'm trying to find some information on this, but I'm not sure where to look or how to phrase it.

I've madea simple app to learn PHP. However, the entire app is user-specific (you login to view your own posts).

Where should I store the user data, and the post data? For example, I would need to store post_title, post_content, post_id, post_time, post_status, and the user would have user_name, user_password, user_email.

Should these be in separate tables, or together? What is the common method for storing this kind of data?

Answer 1

You need to have the details in a separate user table.

User ID | User Name | Full Name | Email Address

And the User ID field is unique. Now you need to store your posts in another table:

Post ID | Post Subject | Post Content | Post Time | Post Status | Author

And this Author is a foreign key, and should be linked with the User ID field of Users table. This is called Relational Database. A relation can be made as this:

If you see in this table, the User ID and Post ID are unique keys. They are called Primary Keys, which means, each row is identified by that value. Also, the main reason for using this is, to avoid redundancy among the user values and also, this saves a huge amount of space occupied. :)

The SQL for this is:

CREATE TABLE Users (
    `User ID` int PRIMARY KEY,
    `User Name` varchar(255),
    `Full Name` varchar(255),
    `Email Address` varchar(255)
);

INSERT INTO Users (
  `User ID`, `User Name`, `Full Name`, `Email Address`
) VALUES (
  NULL, 'Username', 'Full Name', '[email protected]'
);

CREATE TABLE Posts (
  `Post ID` int PRIMARY KEY,
  `Post Subject` varchar(255),
  `Post Content` TEXT,
  `User ID` int
);

INSERT INTO Posts (
  `Post ID`, `Post Subject`, `Post Content`, `User ID`
) VALUES (
  1, 'Hello World', 'Hello World', 1
);