Two foreign keys on the same column?

Rails
1

Let’s say I have three hypothetical MySQL tables:

â?¢ people, with columns id, gender, and source_id
belongs_to :boys and :girls

â?¢ boys, with columns id and name
has_many :people

â?¢ girls, with columns id and name
has_many :people

The gender column in people specifies which of the two source tables the
source_id refers to. For example, if we have values:

1, boy, 1

in people, the application knows to pull id #1 from the boys table,
which is Roderick, and not Wilhelmina, id #1 in the girls table.

My question is whether I can perform this switch without Rails calling
me a dumbass. Would this require two foreign keys on the same table
column? Is this possible? Suggestions?

2

Justin S. wrote:

The gender column in people specifies which of the two source tables
the source_id refers to. For example, if we have values:

1, boy, 1

in people, the application knows to pull id #1 from the boys table,
which is Roderick, and not Wilhelmina, id #1 in the girls table.

First suggestion: Re-architect your data to actually follow the
relational model. Your schema above has numerous problems; trivial
examples include names like Jessie duplicated in both the “boys” and
the “girls” table; the inability to deal with people with ‘wrong’-sexed
names; different regions/countries assigning different sexes to the
same name; etc.

Now, I suppose you could make rails deal with this by something like
this in your Person model:

def name
(gender == ‘m’) ? Boy.find(source_id) : Girl.find(source_id)
end

3

With a little tweaking you could make this work as a polymorphic
association.

_Kevin

4

But my index on ‘people’ would be ‘source_id’ – not ‘name’ – referring
to the primary key (‘id’) of the two other tables. I’d use the ‘gender’
column to determine which table (‘boys’ or ‘girls’) the ‘source_id’
should reference.

So it shouldn’t matter whether the name is gender-neutral or a
nonsensical string of characters.

My question: Is it possible to place two foreign keys on the same column
(source_id)? Or should I scrap the whole switching mechanism?

The code will help, thank you.

5

Justin S. wrote:

But my index on ‘people’ would be ‘source_id’ – not ‘name’ –
referring to the primary key (‘id’) of the two other tables. I’d use
the ‘gender’ column to determine which table (‘boys’ or ‘girls’) the
‘source_id’ should reference.

If I’ve understood correctly, you are trying to use tables along the
lines of (in PostgreSQL syntax, ‘serial’ is like auto_increment in
MySQL)

CREATE TABLE boys(
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);

CREATE TABLE girls(
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);

CREATE TABLE people(
id SERIAL PRIMARY KEY,
gender CHAR(1) NOT NULL,
source_id INTEGER NOT NULL,
CONSTRAINT ‘sane_gender’ CHECK (gender = ‘m’ or gender = ‘f’)
);

And some data:
SELECT * FROM boys;
id | name
----±-----
1 | Tom
2 | Bob
3 | Jessie

SELECT * FROM boys;
id | name
----±-----
1 | Sue
2 | Jane
3 | Jessie

SELECT * from people
id | gender | source_id
----±-------±----------
1 | ‘m’ | 1
2 | ‘f’ | 1

Ok. Expanding this, there is one guy named Tom, and one gal named Sue.
Works so far — well, except for repeating yourself with “Jessie” in
both the “boys” and “girls” tables. Now, a guy named Jane — yes, a
guy named Jane — comes along, so you add:

INSERT INTO PEOPLE(gender, source_id) VALUES(‘m’, ‘2’);

But wait! That doesn’t work; he’s now Bob. Instead, you have to first
add ‘Jane’ to the boys table. So why not use:

CREATE TABLE names (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);

CREATE TABLE people (
id SERIAL PRIMARY KEY,
gender CHAR(1) NOT NULL,
name_id INTEGER NOT NULL REFERENCES names,
CONSTRAINT ‘sane_gender’ CHECK(gender = ‘m’ or gender = ‘f’)
);

Now, maybe you want to be able to find names for only one sex; if so:

CREATE TABLE names (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
is_boy_name BOOLEAN NOT NULL,
is_girl_name BOOLEAN NOT NULL
);

Now, you’ve got the ability to find which names are generally boys,
which are generally girls, and even which (like Jessie) can be either.
Not only that, you can handle exceptions — a boy with a girl’s name,
or vice versa.

And, you can query for other information. For example, to find all the
guys who don’t have a guy’s name:

SELECT p.id, n.name
FROM people p, names n
WHERE
p.name_id = n.id AND (p.gender = ‘m’ AND NOT n.is_boy_name)

6

Honestly, my problem has nothing to do with boys and girls.

The two tables (in my example, boys and girls) must exist separately.
You assume the ‘names’ are UNIQUE, but that’s not necessary to my
purpose. As long as the primary key (id) is unique, every boy’s name can
be Dick and every girl’s name Jane. Or everyone can be Dick. Doesn’t
matter. Repetition is fine.

My two, real-life tables (unlike the boys and girls examples) have
almost nothing between them in common. What I need to do is, based on a
single record, tell Rails which of these two tables to pull a subsequent
record from.

  • gender = ‘m’ and source_id = 1? OK, pull record 1 from the boys table.
  • gender = ‘m’ and source_id = 3? OK, pull record 3 from the boys table.
  • gender = ‘f’ and source_id = 2? OK, pull record 2 from the girls
    table.
  • gender = ‘f’ and source_id = 3? OK, pull record 3 from the girls
    table.

And so on. That’s it.

I appreciate your help but it doesn’t much apply to what I’m doing.

A polymorphic association sounds promising. Can you point me to
documentation?