Using a nonstandard foreign key

rubyonrails-talk
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 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.

3

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?

4

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

5

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: 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

on-innovation.gif

6

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.