Forum: Ruby on Rails Using a nonstandard foreign key

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
464c5a2bd1382891fd301d3c2c1c7b93?d=identicon&s=25 B. Lars (Guest)
on 2007-02-01 18:09
(Received via mailing list)
Hopefully this is an interesting question.

I have a "user" table with a field "email_address".  I would like the
part of "email_address" after the '@' to be a foreign key to a
"companies" table populated by a companies that are clients.

Any pointers on how to do this in rails?
C4dc94c893471878a105761a9207f29b?d=identicon&s=25 Zack Chandler (Guest)
on 2007-02-01 18:17
(Received via mailing list)
I think this is a bad idea for lot of reasons.  First, it is a totally
non-standard way of doing things and there is no support in rails for
this.  Second, there is not a natural relation from a domain to a
company.  A company might have various subdomains for example.

Acme Corp

Save yourself a lot of trouble and do this in the standard way.
You'll thank me for it.

Zack Chandler
464c5a2bd1382891fd301d3c2c1c7b93?d=identicon&s=25 B. Lars (Guest)
on 2007-02-01 18:26
(Received via mailing list)
We have a relatively small number of clients and I know all of their
email domains.
I want to automatically identify a persons company from their email

Would this be workable if I created another field called "domain",
with just the email domain, and make it a foreign key to the companies
464c5a2bd1382891fd301d3c2c1c7b93?d=identicon&s=25 B. Lars (Guest)
on 2007-02-01 19:07
(Received via mailing list)
I believe identifying a persons company from their email address is
hardly asking too much.
I am new to RoR and it does not bode well for Rails if it can't
accommodate such a simple need.

Are there any other options I might have?
Df52f4571626e3d1067d218f1cc3cffa?d=identicon&s=25 Benedikt Heinen (Guest)
on 2007-02-01 19:20
(Received via mailing list)
While generally I agree with Zack's comment about using the email
as a link not being a good idea (e.g. if you used the email address as
key (and foreign keys in one or more tables) then everytime a user wants
to change an address,  you have a whole load of records to update.

Also, if you want to use only PART of a field as the identifying key,
the database can't help in this either (foreign key declarations work on
column level only); which means that the database can't guarantee
referential integrity  (i.e. ensuring that you HAVE a company record for
every domain).

What you might want to do is to look up the company record from the
in the email address, and add ID of the found company as the foreign key
in your user table.  Alternatively - but also non-standard, break up
addresses at the @ sign and store the email address in two fields - user
(part before @) and domain (part after @). That domain part could then
reference a domain field in your company record.  (moving from one to
other is probably done easiest by using two migrations -- one to add the
two new fields, and then process all email addresses and fill them into
the new fields -- and the second one to remove the old email address
column - to be called once the first has been completed.  Simarly -
migrating back do in two steps first adding the email column, create
address data from the user+domain fields, then in the second migration
drop the user and domain columns).

Still - using articial IDs (simple integers) would still be preferable,
just for the performance aspect alone.


On Thu, 1 Feb 2007, B. Lars wrote:

>> I think this is a bad idea for lot of reasons.  First, it is a totally
>> --
>> Zack Chandler
> >

   ALLIANCE, n.  In international politics, the union of two thieves who
     have their hands so deeply inserted in each other's pockets that
     they cannot separately plunder a third.
       (Ambrose Bierce, The Devil's Dictionary)
B09a3f6cdc4797532647d2d264b5df49?d=identicon&s=25 Jodi Showers (jshow)
on 2007-02-01 19:25
(Received via mailing list)
On 1-Feb-07, at 1:07 PM, B. Lars wrote:

>> Save yourself a lot of trouble and do this in the standard way.
>> You'll thank me for it.
>> --
>> Zack Chandler


Using a string as a foreign key is a bad practice - take it from
someone with nigh 20yrs database experience. Rails encourages you to
make good decisions based upon good design principles - and makes bad
decisions tough. That's the vision anyway. Keys foreign and otherwise
should be numeric - generally because numerics are easier to compare
and thus join with - plus smart keys (keys with special meaning such
as domain_name) are a big no-no - since changing the value of a
domain name will mean that you must then carry that change into all
relations.  (If relevant to you this looks like a decent ref: http://

But this doesn't mean there's not a solution to your needs using best

   User has_many :domains

then you can lookup the user based upon their one or more domains
specified - just be sure that is unique.

The domain table would then have a primary key (id: integer) a name
(name: string) and a foreign key to the user table (user_id:
integer), and your user.

General Partner
The nNovation Group inc.
464c5a2bd1382891fd301d3c2c1c7b93?d=identicon&s=25 B. Lars (Guest)
on 2007-02-01 19:31
(Received via mailing list)
Thank you Benedikt and Zack for your replies.  I've enjoyed working
with RoR so far and will take your advisement and see what I can do.
This topic is locked and can not be replied to.