Handling duplicate foreign key columns from the 'Link Table' dialog

[ghislaineguerin]

Problem

The 'Link Table' dialog can help users quickly set up the appropriate columns and constraints to link tables across a schema. However, there's no way to let the user know a foreign key constraint already exists.
In some cases, the user might want to do this intentionally. For example, imagine a scenario where a 'track' has a column for both 'artist' and 'featured artist,' both containing FK constraints that are the same.

Question 1

Should we have a warning and then explain to the user that we'll add a new column anyways?

Question 2

Would the new column be auto-named as 'artistId(2)' or similar?

[seancolsen]

Should we have a warning

The warning is a good idea and would be nice in some cases -- but it would be more work to implement, so I'd say let's deprioritize it for now but consider adding it later. The warning is not that important because adding a new link is a very low-risk operation which the user can easily undo.

Would the new column be auto-named as 'artistId(2)' or similar?

Yes, though I'd propose artistId_2 instead of artistId(2), but I don't feel strongly about that.

[mathemancer]

Yes, though I'd propose artistId_2 instead of artistId(2), but I don't feel strongly about that.

I'd usually agree, but we're already quoting every identifier at the DB level, so there's no advantage to avoiding certain characters, implementation-wise. Although, it might be an opportunity to guide users towards DB-friendly naming, making it easier for them to use their DB through other clients.

[kgodey]

I think it could be useful to ask the user to name the link after the user fills out the first two questions. We can pre-fill artist_id but in @seancolsen's example, the user can name the first link to the "Address" table as origin and the second link as destination.

I don't think we need a warning.

[pavish]

I also think this would be best. Similarly for the many-to-many links, we can ask the name of the mapping table.

[mathemancer]

we can ask the name of the mapping table.

That would have the added advantage of helping the user think of their "mapping table" as an object in its own right, which it often is (e.g., shipments)

[ghislaineguerin]

@kgodey I like this idea, I will incorporate a way to rename the newly created objects.

[kgodey]

Please also note that foreign keys can be self-referential (referring to the same table). For example, imagine a Person table.

id	name	spouse_id
1	Catherine Zeta-Jones	2
2	Michael Douglas	1

Here, spouse_id is a foreign key to the same table.

I'm not sure if this affects the user experience, but I wanted to mention it just in case.