The relationship between resource_x_resource records and tiles should not be 1:1

[chiatt]

Currently the resource_x_resource table has a foreign key to the tiles table. This means that only 1 tile can be associated with an instance, and it forces a modeler to decide which resource should have the tile that represents the relationship. For example, a building may have multiple relationships to past owners. A graph designer could decide that each owner should have a single tile pointing to the building, or a designer could have a single building with multiple tiles each pointing to different owner instances. The right approach is up to the discretion of the modeler, but it has implications for search as well as reporting because a user can only search for resources based on their tile values and reports show tile information that would only be present for one side of the relationship. Neither option might be very good.

For truly mutual relationships, 1:1 doesn't really work at all. Marriage is a good example of this because if you can only have one tile per relationship then it becomes ambiguous which spouse 'owns' the relationship. If a tile was created for both spouses, then you'd have to create 2 relationship records. That would be redundant and would present duplicated information in the related resources section of a report.

One solution to this problem would be to remove the tile and relationship type information from the resource_x_ resource table and move it to a tiles_x_relationship table. With this design a tile would not represent the relationship, but only be used to describe a way in which two resources are related. Any number of tiles from two instances could be used to describe a single relationship. This could also be done without a tiles_x_relationship table, but we would have to rely on inspecting tile data to identify the resource_x_resource record.

If we were to remove the tileid from the resource_x_resource table, then we would have to manage the creation and removal of relationships separate from the creation and removal of a tile, but that isn't much different than how we managed relationships before the addition of the resource instance datatype. One possible advantage here is that systems that already have relationships not represented by a tile could then create cards with relationships and use existing resource_x_resource records.