MYSQL table planning [closed]

Closed. This question needs to be more focused. It is not currently accepting开发者_StackOverflow answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 4 years ago.

Improve this question

mysql is new to me as is most web programming. I am building something that allows users to login and do various things. Is it a good idea to create more tables used for specified tasks rather than putting everything in one?

For instance if I have users, and articles and let users be connected to articles.

Would I need a table for users

and one for online users

and one for articles

and one for articles and the connected users?

A friend recommended this to me, but it confuses me that most of those tables have things in common. Why wouldn't you have a column in articles that listed connected users?

---

You need creat 3 tables 
(example)
1 Users
  id
  login
  username

  (example)
2 Articles
  id
  athor
  title

(example)
**This is type call many to many that mean you user can have many books
Book can have a lot of users 
3 User_and_Articles
  id
  user_id
  article_id** 

  And after you will query from tables User_and_Articles   
 You can ask Like what does user have books  like this 

$sql =   "SELECT * FROM   User_and_Articles  WHERE user_id  = Users.id";

$sql - in this sql you will have all books id

---

Try and level up your line of thought, it will be much easier for you.

1. Reduce the business model to entities, the objects inside your app that interact with each other.
2. The tables will be the representation inside the database system. Build them.
3. Consider the relationships next. Ask yourself: how do they work together? Does one belong to the other? Does one have many of the other?
4. The relationships should be new fields in each respective tables. For example:

   If an article belongs to a user, the field in the articles table would be user_id.

A good start, I would suppose. Read the articles mentioned in the other answers, it'll teach you the basics on what I just explained here.

Good luck :)

---

I think to best way to start up with is Data Modeling , first start with a ER Diagram then apply the normalization theory , This structured approach will always provide you a good database desgin

---

I suggest:

a) Have a read of http://en.wikipedia.org/wiki/Database_normalization
b) Design your tables normalized to the extreme
c) Have your application running and tested, i.e. all the last minutes changes are in ;-)
d) then profile your sql queries and de-normalize as needed.