My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables.
People objects, mapped to their respective tables. Then I have
Messages, also mapped to its own table.
message can belong to either a
person (people table) or a
project (projects table) (n-1).
Should I create two columns --
belongs_to_type-- for Messages and query to get them? or;
Should I create an association table for each -- people_have_messages and projects have messages?
What is the correct way to work in this case scenario?
How can I create FK constraints and restraints if I go with the first option?
My problem is, as both a Project and a Person (people table) can have the same PK (id), it wouldn't be enough to reference without stating the table and I don't know what exactly to do.
People: -------------- ID (PK unique) Name Projects: -------------- ID (PK unique) Title Description Message: -------------- ID (PK unique) Text