"Link table" to self?

[seancolsen]

@ghislaineguerin and @kgodey For #947 I'd like some clarity on the specs for the "Link Table" dialog. Should the user be able to link a table to itself? This type of schema is necessary for hierarchical relationships. But we could consider that use-case advanced-enough to warrant manual configuration through the constraints dialog. If we do want to support self-referential foreign keys in the "Link Table" dialog, I'd like some clarity on the two questions that come after the choice of table. They both seem to be irrelevant if the target table is the same as the starting table. Am I correct?

[kgodey]

Yes, a user should be able to link a table to itself. We do still need at least the first question, because we may want to set up a mapping table instead of a FK.

Consider the case where a user is adding a self-link to a Movies table.

• If they'd would like to set up a link to track movie recommendations, we would need a mapping table since each movie can have multiple recommendations.
• If they would like to track immediate sequels, we'd set up a FK.

If it's easy to do, I recommend only the first question if the user selects the current table and then setting up the link appropriately.

[seancolsen]

Cool, if we keep the first question, how should it be phrased? The phrasing used in the prototype seems like it would be confusing.

[kgodey]

@seancolsen Please rephrase as you see fit.