Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 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? –  Allan S. Hansen Nov 8 '13 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 '13 at 11:13
add comment

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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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