Autosuggest table name for foreign key

[diffen]

In the table structure view, when adding a new constraint for foreign key, Postico should be able to make an intelligent guess based on table and column name.

e.g. you're editing a table structure for the orders table and adding a new foreign key constraint on a column called customer_id. The database already has a table called customers or customer with an id field that matches the data type of the current column (customer_id). So Postico should be able to connect the dots and the pop-up/modal for adding the foreign key constraint can easily default to the "Referenced Table" being customers.

It's probably very easy to implement and would be a nice feature to have.

[jakob]

That's a good idea for a feature, but I'm not sure how trivial it is to implement -- at the very least, I'd need to support the most important singular/plural inflections for English (eg. person -> people). It would probably be a good idea to port the Inflector from Ruby on Rails, since those are the rules that a lot of people use.

Anyway, before I start working on this feature, it would be nice if people could send me sample data, so I can see what kind of names are used in real world applications. To do so, simply execute the following query:

select 
  conrelid::regclass as table,
  ARRAY(select (SELECT attname FROM pg_attribute where attrelid=conrelid and attnum=i) from unnest(conkey) as key(i) ) as key,
  confrelid::regclass as foreign_table,
  ARRAY(select (SELECT attname FROM pg_attribute where attrelid=confrelid and attnum=i) from unnest(confkey) as key(i) ) as foreign_key
from pg_constraint
where confrelid > 0

On one of my databases, this has the following output:

table	key	foreign_table	foreign_key
comments	{monument_id}	monuments	{id}
monuments	{ancient_finding_place_id}	ancient_places	{id}
monuments	{archaeology_author_id}	authors	{id}
monuments	{architecture_author_id}	authors	{id}
monuments	{conservation_place_id}	places	{id}
monuments	{epigraphy_author_id}	authors	{id}
monuments	{finding_place_id}	places	{id}
monuments	{museum_id}	museums	{id}
monuments	{regional_info_id}	regional_info	{id}
monuments	{parent_monument_id}	monuments	{id}
pages	{photo_id}	photos	{id}
photos	{author_id}	authors	{id}
photos	{copyright_id}	copyrights	{id}
photos	{monument_id}	monuments	{id}
photos	{regional_info_id}	regional_info	{id}

As you can see, a simple heuristic wouldn't always work. It would be awesome if a lot of people could send me the results of the above query (either formatted as a table like above, or attach a csv file to this issue). Then I could evaluate potential solutions with real world data.

[diffen]

Sent you a CSV file via email.

Good idea to collect some data to estimate the accuracy of a simple heuristic. My guess is it will work half the time, which is still great. It doesn't have to work all the time.

You will save a couple of seconds for the user every time there is a match. It's a nice-to-have; so no point in over-engineering it.