# Using a nonstandard foreign key **URL:** \ **Category:** Rails\ **Created:** [February 1, 2007, 6:09pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128 "2007-02-01T18:09:42Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 6:09pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/1 "2007-02-01T18:09:42Z")
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? ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 6:17pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/2 "2007-02-01T18:17:43Z")
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 removed\_email\_address@domain.invalid removed\_email\_address@domain.invalid Save yourself a lot of trouble and do this in the standard way. You’ll thank me for it. – Zack C. [http://depixelate.com](http://depixelate.com) ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 6:26pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/3 "2007-02-01T18:26:44Z")
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? ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 7:07pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/4 "2007-02-01T19:07:47Z")
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? ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 7:20pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/5 "2007-02-01T19:20:48Z")
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](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) ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 7:25pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/6 "2007-02-01T19:25:49Z")
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](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](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 ---
**Author:** ![BSLars](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e5b9ba/32.png) [@BSLars](https://www.ruby-forum.com/u/BSLars)\ **Post date:** [February 1, 2007, 7:31pm UTC](https://www.ruby-forum.com/t/using-a-nonstandard-foreign-key/84128/7 "2007-02-01T19:31:50Z")
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.