CODEWITHSUNDEEP
phpmysqldatabase
stergosz
stergosz

Reputation: 5860

PHP MySQL database design

I have a user table which has username,user_id,password and email fields.

The user can post updates,can subscribe to users and can friend people.

What is the best way to design my database in order to store the count of the updates, subcribers and friends of a user?

Should i add 3 more fields in the user table and just increase the count of these 3 columns?

Thanks.

Upvotes: 1

Views: 778

Answers (6)

Joey
Joey

Reputation: 687

Not sure if i understand your question but i think the best way to do this is to make a table for each of this functions, you can link those tables with the user_id field. Like so:

Table 'Users' :

*user_id*
username
password
email

Table 'Friends':

id
*user_id*
friendids

You can then get the users with something like 

SELECT * FROM friends WHERE user_id = '$userid'

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

First I'd try to calculate

the count of the updates, subcribers and friends of a user

on the fly, something like

select count(*) from user_updates where user_id = :uid
select count(*) from user_subcribers where user_id = :uid
select count(*) from user_friends where user_id = :uid

If you have performance problems with this approach, then you can consider adding these count fields to user table, but you have to be careful to maintain them.

Upvotes: 1

lvil
lvil

Reputation: 4326

You can make 3 tables: friends (user1_id,user2_id), updates(user_id,content),subscribe(user1_id,user2_id).
If the database is big and you don't want to count the friends each time, you can add a field in users table and update it every time a friend is added/deleted
Maybe drawing a diagram would help:
http://en.wikipedia.org/wiki/Entity-relationship_model

Upvotes: 1

YuS
YuS

Reputation: 2045

Well, it is faster to read if you have separated relations and content, however if you have only content and generates relations from it, the result sometimes could be more accurate. So, it depends.

Upvotes: 0

duffymo
duffymo

Reputation: 308763

I'll assume you meant to type "columns" instead of "friends" in that last sentence.

That's one way to do it.

You should think about the entities, relationships, and cardinalities when you design. Do you want to keep track of timestamp for each update? If yes, I'd make that a separate table with a one-to-many relationship to user. Same for subscribers and friends. The answer depends on how you choose to model the problem. There's no right or wrong answer, but there are tradeoffs.

Upvotes: 0

Mike Thomsen
Mike Thomsen

Reputation: 37506

Either create a separate table with that user meta data or add them as new columns. Either way is fine.

Upvotes: 0

Related Questions