Using a nonstandard foreign key

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?

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
john@acme.com
jane@corp.acme.com

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

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?

While generally I agree with Zack's comment about using the email address
as a link not being a good idea (e.g. if you used the email address as the
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, then
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 domain
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 email
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 the
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 email
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, if
just for the performance aspect alone.

Benedikt

Lars,

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://en.wikipedia.org/wiki/Database_normalization)

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

try
   User has_many :domains

then you can lookup the user based upon their one or more domains specified - just be sure that domain.name 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.

Cheers,
Jodi
General Partner
The nNovation Group inc.
www.nnovation.ca/blog

on-innovation.gif

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.