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

database-agnosticdatabase-designforeign keyrelational-theory

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

Best Answer

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.

Related Solutions

MySQL data modeling help

I would use separate tables for the project-sponsor and event-sponsor relationships. You are making two many-to-many relationships here, and that just seems like the right way to do it to me.

Can each project/event/org have more than one leader? If not, why not simply put the leader id in the project/event/org table? Otherwise, I would use three tables for the same reason as above: you are describing three many-to-many relationships.

Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Related Question