Multiple-reference foreign keys in table definition?

Question

Summary

How do I make it easy for non-programmers to write a query such as the following?

select
    table_name.*
  , foreign_table_1.name
  , foreign_table_2.name
from
  table_name
left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
;

Context

I have a situation like this:

create table table_name (
    id integer primary key autoincrement not null
  , foreign_key_1_id integer not null
  , foreign_key_2_id integer not null
  , some_other_column varchar(255) null
);

create table foreign_table (
    id integer primary key autoincrement not null
  , name varchar(255) null
);

...in which both foreign_key_1_id and foreign_key_2_id reference foreign_table. (Obviously, this is simplified and abstracted.) To query and get the respective values, I might do something like this:

select
    table_name.*
  , foreign_table_1.name
  , foreign_table_2.name
from
  table_name
left outer join foreign_table foreign_table_1 on foreign_table_1.id = foreign_1_id
left outer join foreign_table foreign_table_2 on foreign_table_2.id = foreign_1_id
;

(That is, alias foreign_table in the join to link things up correctly.) This works fine. However, some of my clients want to use SQL Maestro to query the tables. This program uses the foreign key information to link up tables using a fairly straightforward interface ("Visual Query Builder"). For instance, the user can pick multiple tables and SQL Maestro will fill in the joins, like seen here:

(That's a diagram from their website, just for illustration.)

This strategy works great as long as foreign keys only reference one table. The multiple-reference situation seems to be confusing it because it gneerates SQL like this:

SELECT 
  table_name.some_other_column,
  foreign_table.name
FROM
  table_name
  INNER JOIN foreign_table ON (table_name.foreign_key_1_id = foreign_table.id)
  AND (table_name.foreign_key_2_id = foreign_table.id)

...because the foreign keys are defined as follows:

create table table_name (
    id integer primary key autoincrement not null
  , foreign_key_1_id integer not null
  , foreign_key_2_id integer not null
  , some_other_column varchar(255) null

  ---------------------------
  -- The part that changed:
  ---------------------------
  , foreign key (foreign_key_1_id) references foreign_table(id)
  , foreign key (foreign_key_2_id) references foreign_table(id)
);

create table foreign_table (
    id integer primary key autoincrement not null
  , name varchar(255) not null
);

That's a problem because you only get 1 foreign_table.name value back, whereas there are often 2 separate values.

Question

How would I go about defining foreign keys to handle this situation? (Is it possible or does it make sense to do so? I wouldn't think that it would make a big difference in constraint checking, so I've thought that that is a reason I can't find any information.) My end goal is to make querying this information easy for my clients to do by themselves, and although this situation doesn't happen every day, it's time-consuming / frustrating to have to help people through it every time it comes up.

If there isn't a way of solving my foreign key problem this way, can you suggest any alternatives? I already have some ways of getting people this information though views, but people often need to have more flexibility than that.

Answer 1

It seems to me that your definition is just fine, and SQL Maestro is incorrectly interpreting two foreign keys to the same table as the same foreign key, so I would alert them to that fact so that they can fix it.