Forum: Ruby on Rails Two foreign keys on the same column?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Justin S. (Guest)
on 2006-03-02 23:10
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?
Anthony DeRobertis (Guest)
on 2006-03-03 21:20
(Received via mailing list)
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
Justin S. (Guest)
on 2006-03-03 21:40
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.
Kevin O. (Guest)
on 2006-03-03 22:03
(Received via mailing list)
With a little tweaking you could make this work as a polymorphic
association.

_Kevin
Anthony DeRobertis (Guest)
on 2006-03-03 23:52
(Received via mailing list)
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)
Justin S. (Guest)
on 2006-03-04 00:26
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?
This topic is locked and can not be replied to.