Rails - Mar 2006 - Two foreign keys on the same column?

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?

-- 
Posted via http://www.ruby-forum.com/.

Justin Skolnick wrote:
> 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.
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

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.

-- 
Posted via http://www.ruby-forum.com/.

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

_Kevin

On Friday, March 03, 2006, at 8:40 PM, Justin Skolnick
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.
>
>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.
>
>--
>Posted via http://www.ruby-forum.com/.
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails




-- 
Posted with http://DevLists.com.  Sign up and save your time!

Justin Skolnick 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)

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?

-- 
Posted via http://www.ruby-forum.com/.