You could put a text field in the projects table that holds a return delimited list of project IDs that any given project is associated with.
Then you have something to relate projects to projects in a many to many relationship.
However that may not be the best way to solve your problem because i'll bet that projects is not the only entity in the solution.
it is definitely possible that a join table would fill your needs but we don't know enough of what your trying to accomplish.
If you in fact have two tables project 10 and project 25 then you should stop and rethink your design.
You can also use a script to create a second join table with the match field values swapped in order to make the link between project records bidirectional.
So if you create this join table record:
Join::_fkProjectA = 325
Join::_fkProjectB = 654
Your script would generate a second record that looks like this:
oin::_fkProjectA = 654
Join::_fkProjectB = 325
And now your join table links project records in both directions
Another option is to use a ProjectGroup table that has two fields: groupID and fkProject
groupID = 1
fkProject = Project 10 ID
groupID = 1
fkProject = Project 25 ID
groupID = 1
fkProject = Project XX ID
These records can be added or deleted independently, without reference to another project.
Then you find a project's siblings through a Project_siblings table occurrence
ProjectGroup::groupID = ProjectGroup_siblings::groupID
(optionally: ProjectGroup::fkProject ≠ ProjectGroup_siblings::fkProject)
and a Project_siblings TO where
ProjectGroup_siblings::fkProject = Project_siblings::id
Guess I should apply the KISS principle... and ask a few more questions...
Will a project only ever be connected to one group of siblings (ie. Project 10, Project 25 and Project XX group together and each does not group with any other projects)? Or is it ever the case that a project might be a part of multiple groupings (ie. Project 10 groups with Project 25 and Project XX, and also with Project 33 and Project 42, but Project 25 does not group with Project 33 or Project 42). If there is only one set of siblings for each project you could add a SiblingSetID field to the ProjectTable and connect to other Projects with the same SiblingSetID. Simple, but limited flexibility.
How large are the sibling sets? Ie. On average how many projects are grouped together? A large sibling set requires a significant number of join records, however not so many ProjectGroup records.
Thanks for this info! I ended up using a combination of your suggestions.
Initially my goal was for only 1 set of groups, but I added the ability for multiple groups in case the future deems necessary.