2

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

2 Answers 2

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.

3
  • Hello @AllanSHansen. Thank you for your message. Projects and People also have a (n-m) relationship, which is handled with a "ProjectsToPeople" table. I suppose having 2 tables to assosciate is a better option. Thanks!
    – Phil
    Nov 8, 2013 at 10:31
  • If People and Projects are connected, do you then need Message on Project? Then I'd think a Message could contain a Project FK and a relationship to People to tie it to a specific project; or maybe be connected to the ProjectsToPeople entirely instead? Nov 8, 2013 at 10:36
  • That also sounds great. But messages are independent of both Projects and People. I want to keep things "flexible", in case, say a person might moved from project A to B. Therefore, I believe your first suggestion with two separate tables is better to avoid modifying a message's Project_FK when such move occurs.
    – Phil
    Nov 8, 2013 at 11:13
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.