CODEWITHSUNDEEP
sqldatabasedatabase-design
1110
1110

Reputation: 6829

Table with user activities - design issue

On my site user can make some items and those items goes in Items table.
Users can also follow each other and those data I store in Followings table.
Now I need one table that keep track of user activities, so I have created:

Users{UserId, Username, FirstName, LastName}
Items{ItemId, Title, CreatedByUserId}
Activity{ActivityId, ActivityTypeId, UserId, ItemId}

Here ItemId can be or ItemId from Items table or UserId from Users table.

Is this design good or there is better approach to do this?

Edit: I have added table structures. The point is that I wan't to add record to that Activity table every time when user create item or start to follow some user. So I can always track what user did on the site.

Upvotes: 0

Views: 1068

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Here ItemId can be or ItemId from Items table or UserId from Users table.

This will prevent the DBMS from enforcing the foreign key. If you go down that route, better separate these fields (so you can make foreign keys towards their respective tables) and use a CHECK to ensure exactly one of them is non-NULL:

enter image description here

CHECK (
    (ITEM_ID IS NOT NULL AND FOLLOWED_USER_ID IS NULL)
    OR (ITEM_ID IS NULL AND FOLLOWED_USER_ID IS NOT NULL)
)

The ACTIVITY PK is crafted so it is easy to query for a "timeline" of a specific user and is friendly to clustering.

(You may or may not need ACTIVITY.TYPE depending on what exactly you want to track.)

The alternative would be to have a separate table for each kind of activity:

enter image description here

(And you could add a TYPE field in FOLLOW_ACTIVITY to distinguish between "follow" and "unfollow".)

Another alternative would be to inherit users and items from a common parent "class" and then link to that class:

enter image description here

This design is probably an overkill in this case, but could be useful if you later add many other kinds of objects that could be tracked.

Upvotes: 2

user1594895
user1594895

Reputation: 587

Items{ItemID,descr,UserIDCreator,date-time}
User{UserID,descr}
Followings{UserIDFollowed,UserIDFollower,date-time}

now if you wont all items created by eser use user code on items the same on followings I add date-time for chrono select

You can create atriggered table or a view this depends on cardinality of data

If the same object can be share between users 

Items{ItemID,descr,}
UserItems{UserID,descr,ItemId,date-time}
User{UserID,descr}
Followings{UserIDFollowed,UserIDFollower,date-time}

I hope to be useful

Upvotes: 0

Related Questions