Using of has_one association for model with encrypted primary key

Question

I have Address and ZipCode models. Address model has encrypted_post_code field, ZipCode has code field. I would like to tie Address records and ZipCode records with help of has_one association using specified fields. Currently I need it to use such ActiveRecords features as includes, preload, eager_load, etc.

Unfortunately all string fields of address records are encrypted (it's requirement of customer). I cannot use has_one :zip_code, primary_key: :post_code, foreign_key: :code, because I don't have post_code field in addresses table, it contains only encrypted_post_code field.

Please advise how to solve this problem.

Answer 1

I don't think your customer actually understands the benefits of encryption. But, it doesn't matter because your database should make associations by id rather than the encrypted postcode.

So, your zipcode table could have the columns id, encrypted_post_code and then an address just has a postcode_id field, and makes the join that way. This way you avoid dealing with the encrypted postcodes completely, until you need to find out what the real postcode is, at which point you look it up via the association and decrypt it.