CODEWITHSUNDEEP
sqldatabase-design
X 47 48 - IR
X 47 48 - IR

Reputation: 1498

More tables VS More columns

In my scenario I want to log user's activities like checking someone's profile ... I could do this in two ways

The first one with two tables :

post_view :

        user_id | post_id | times | created_at | updated_at
           1    |    1    |  13   | 2018-01-19 | 2018-01-19

user_view :

         user_id | viewed_id | times | created_at | updated_at
            1    |    2     |  11   | 2018-01-19 | 2018-01-19

Note :

Even can be more ..!

The second one with one table:

views :

        user_id | target_id | view | times | created_at | updated_at
           1    |     1     | post |  13   | 2018-01-19 | 2018-01-19
           1    |     2     | user |  11   | 2018-01-19 | 2018-01-19

enter image description here

I think having more tables is better than having more columns ... Because it's easier to find something in 1000 records instead of 2000 ! What is your idea ?!

Upvotes: 1

Views: 395

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Two tables is better, but not for the reason that you give.

The reason is that you can properly declare foreign key relationships:

create table post_view as (
    user_id int references users(user_id),
    post_id int references posts(post_id),
    . . .
);

Declared foreign key relationships are quite powerful. In the combined table, target_id does not have such a relationship.

That said, my actual preference is for a single table for performance reasons. This is easier to maintain (a single table), easier to investigate (a single table), easier to add new types (a single table). The downside is maintaining relational integrity, which is a bit trickier.

Upvotes: 1

Related Questions