So i'm quite new to database structure. And currently i need to build a notifications system for my web application. After googling for awhile i find the first answer is interesting.
Why activity_type is TINYINT? Doesn't it suppose to be string because he says the example value is like 'added favourite'. Anyone can help?
Yes, don't follow that example.
He's using numbers in the TINYINT that have magical meanings and most likely represented in his code as constants, so 1 means one kind of activity, 2 means another, etc., and only the code knows those meanings -- not the database.
Then there's the "source_id" that refers to an undeclared, unenforced foreign key referencing one of several other tables. Don't do this.
Within a database column, all of the values should have the same meaning -- they should all be from the same information domain. If there's a "thing_id" column, it should always refer to the "id" of a row in the thing table. It should not refer to a row in the "thing1" table sometimes or maybe a row in the "thing2" table other times depending on whether "thing_type" is set to "1" or "2" ... yuck.
There are probably viable use cases for such a design but I would interpret this model as a red flag warning of a database that has no enforcement of referential integrity... and that is not consistent with best practices for relational databases.
Note from his last comment...
No joins.
He's not really using the relational database as a relational database. He's using it almost as some kind of key/value store.
