How to establish a Two-to-One table relationship (PK-FK-CK)?

Question

My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables.

I have Projects and People objects, mapped to their respective tables. Then I have Messages, also mapped to its own table.

A message can belong to either a person (people table) or a project (projects table) (n-1).

My question:

• Should I create two columns -- belongs_to_id and 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.

Tables:

People:
--------------
ID (PK unique)
Name

Projects:
--------------
ID (PK unique)
Title
Description

Message:
--------------
ID (PK unique)
Text

Answer 1

I'd say it depends on whether there's a connection between People and Project?

From what you've mentioned, I'd properly go with a: Projects -> ProjectsToMessage <- Message and People -> PeopleToMesseage <- Message type structure

(your bullet point 2)

Unless there's a releation between Projects and People in which case you might end up with some circular dependency which the database wouldn't like. However in that case, I would then question whether a Message can be associated to a Project without it going through People.

Answer 2

I think that the first option is correct as compared to creating 2 different tables as creating more tables and then using "JOIN" queries on them seems quite costly process for the database(i.e costly in terms of performance)