Hi, I'm having doubts of how a database has been designed for a project, I got 2 tables, one Message and another User
A User can recieve many Message and a Message can be send to multiple Users, which requires a table in between to break the many to many relation. That one called MessageUser stores 2 kind of things
- Type of user (like main or CC)
- Multiple columns that indicate when the User performed some action on the Message (a column called Accepted that has the date when the Message was accepted, a column called Deleted that has the date when it was deleted, a column called Modified that has the date when it was modified, etc, etc, etc...)
What it seems it was done is that a 4th table called MessageUserStatus was created which is also a one-to-many table between Message and User, which contains all the dates and leaves MessageUser just with the type of user thing.
I'm aware something is wrong because I have insert and updates that modify just one field, and queries that require me to join the 2 tables... but I'm just a programmer pawn and maybe I'm wrong.
Now, this are my 2 questions:
- Is correct to have all the events registered like that or it should be a table called Event that says the type of event and the date it occured?
- Splitting a table in 2 where both have the same relationships helps somehow? is this a secret technique for separatyng big data from small data? It doesn't violate any of the 3 norms, but I always got taught that one object should always map to one and only one entity in a db.