Mysql – Single table with more columns vs multiple tables with fewer columns

database-designMySQL

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows?

For example: A user can post an update on their wall or in a group.

Two database designs I can think of are:

Design 1

UserPosts

id
userId
post
datetime

UserGroupPost:

id
groupId
userId
post
datetime

Potential problem : Might require joins, which can (in future) be a slow query.

Design 2

Posts:

id
userId
groupId
post
datetime (where groupid would be null if user posts on their wall)

Potential Problem : Looping over large dataset could take a (long)time.

How can I get better performance when data increases? Is there any other (better) way?

Best Answer

My inclination here would always be design option 1, or at least along those lines. Do not worry too much about trying to eliminate the need to join tables in future queries - any normalized database is going to use joins in any useful queries, that's just relational databases.

Also, why would you necessarily have to join the userPosts and userGroupPosts tables for your website? Would they not be displayed separately? The only reason you would join these tables is maybe when searching for posts, but it shouldn't be too hard to write efficient queries for that. Apart from that you may end up wanting to query the tables for analysis purposes, but that is not the primary purpose of this database.

Design 2 could at the least mean that you end up with a very busy table.

The best option though would be to prototype each and run some tests. Build a prototype of each design option, and do some performance benchmarking on different operations with some dummy data.

Related Solutions

Sql-server – SQL Server database forum design sample

You might have something like this. If the post is a new topic, ParentID and ReplyToID would be NULL. For forums it is often desirable to show when a post is a reply to another post, but still indicate which topic it belongs to, so a second column (ReplyToID) can indicate this. When it is a reply to the original parent this can be NULL or have the same value as ParentID.

CREATE TABLE dbo.Posts
(
    PostID      INT IDENTITY(1,1) PRIMARY KEY,
    UserID      INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(UserID),
    ParentID    INT NULL REFERENCES dbo.Posts(PostID),
    ReplyToID   INT NULL REFERENCES dbo.Posts(PostID),
    Content     NVARCHAR(MAX), -- more accurate than "description"

    -- ... other columns ...

    -- "DateTime" is not a good choice for a column name
    create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modify_date DATETIME NULL -- or same default as create_date
);

Mysql – SQL DB design to support user feeds (in application like facebook)

Get rid of ambiguous columns like object_id. Use log_type to determine what you are querying on. So something like:

CREATE TABLE logs (
    log_id bigint AUTO_INCREMENT, -- not sure if this is the mysql syntax
    user_id int references users(id),
    post_id bigint references posts(id)
);

A second point is I would suggest changing the friend/unfriend to be immediate so it doesn't hit this table. This should be only for logging actions to posts, not friendships and the like. If you must log and propagate, create a separate table for this.