Reputation: 28312
I have a table called Project. For simplicity, lets assume it has one column called projectId.
Projects --------- projectId PK
I have another table. It is called SubProjects. It has two columns, one is called projectId, and the other is called subProjectId. Both columns make up the primary key.
Sub Projects -------- projectId PK subProjectId PK
They way this works is you have a bunch of projects listed in the projects table. Some of these projects can be sub projects of projects. For example, if the projects table had 3 rows like so
Projects ------- projectId 1 2 3
Projects 2 and 3 could be considered sub projects of project 1.This is where the SubProject table comes in. To represent this, Subprojects would look like this
Sub Projects -------- 1 ,2 1 ,3
Meaning that projects 2 and projects 3 are sub projects of project 1.
I am having issues creating a model for this. In the SubProjects table, I need both projectId and subProjectId to reference a projectId in the Projects table. But, the key thing is that they could be referencing different projects. I supose I should do this using foregin keys? Does this make sense? Is this allowed? Is there a better way to do this?
I am using MySql and MySql workbench, but cant seem to pull this off. Any ideas?
EDIT A project can be a sub project of multiple tables.
Upvotes: 0
Views: 609
Reputation: 2575
Why don't you add a new field in table Projects, named as ParentProjectID. For projects, it will be null, and for subprojects, it will have its parent projectID there.
Upvotes: 0
Reputation: 9166
You can add foreign key constraints to both columns in "Sub Projects" referring back to the ProjectId column in the "Projects" table. Doing so will not force both columns to have the same value on the row in "Sub Projects". When you insert a new row to "Sub Projects" (lets say 2,3), the server will check:
This kind of model will work but it will not guarantee that the project hierarchy is "sane". There is nothing in the database itself to stop you from inserting both (2,3) and (3,2) into the "Sub Projects" table. Or (1,1) for that matter. So you will have to enforce that in your application logic instead.
Upvotes: 0
Reputation: 31
How you considered creating one table with a hierarchy? Using your example it would be something like
Projects
----------
ProjectID | ParentID
----------|----------
1 | NULL
2 | 1
3 | 1
Upvotes: 0
Reputation: 2744
I would create one table with hierarchy using columns, such as:
project_id parent_project_id
-------------------------------
1 null
2 1
3 1
Then you can just say if a project's parent_project_id
is null, it is not a sub-project of any other project. Otherwise, the project is a sub-project of the project listed in parent_project_id
.
Upvotes: 0
Reputation: 6953
Doing it like that should be fine. Can you be more specific about why you can't set it up?
If a project is only a sub-project of one project, another way to do it would be to add a "parent project" column to the projects table, with a FK pointing to "itself". I think your queries most likely will end up being simpler if you do it that way.
Upvotes: 1