Using a nonstandard foreign key

Rails
1

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?

2

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
[email protected]
[email protected]

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

–
Zack C.
http://depixelate.com

3

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
address.

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
table?

4

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?

5

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

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 C.http://depixelate.com

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)

6

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 C.http://depixelate.com

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://
Database normalization - Wikipedia)

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

7

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.