how to reference lookup data in DB table - by ID? (or should the ID not be relied upon)

Hi,

What’s a good approach for referencing lookup/reference data (static) that one has loaded in a reference table. Say for example “tax codes”, which pretty much have just the database ID and then a description field. Some options that come to mind:

1 - By ID - however this makes an assumption that the data always loads into a database and gets the same IDs (i.e. when you’re loading your static reference data)?

2 - Search by description to identify the record - but obviously if one changes the description this isn’t very good.

3 - Perhaps create a specific identify/key as a new column that you use, but is not the database Rails ID? How’s this sound?

Ideas/comments?

Tks

Hi, What's a good approach for referencing lookup/reference data (static) that one has loaded in a reference table. Say for example "tax codes", which pretty much have just the database ID and then a description field. Some options that come to mind: 1 - By ID - however this makes an assumption that the data always loads into a database and gets the same IDs (i.e. when you're loading your static reference data)?

If the descriptions change, you should do it just like this.

2 - Search by description to identify the record - but obviously if one changes the description this isn't very good.

That's the simpler way to solve the issue, if the descriptions do not change. But this isn't really "beautiful" from the database point of view, as your data isn't going to be normalized and I usually prefer to work with normalized data unless denormalization is needed.

3 - Perhaps create a specific identify/key as a new column that you use, but is not the database Rails ID? How's this sound?

I think this is a way to complex way to solve a simple problem :slight_smile:

if one uses the ID’s then can I assume you should then “force” the ID’s to be set as you want them in the migration? i.e. can’t rely on a standard rails migration where the database uses it auto-increment?

[...]

> 3 - Perhaps create a specific identify/key as a new column that you use, but > is not the database Rails ID? How's this sound?

I think this is a way to complex way to solve a simple problem :slight_smile:

I totally disagree. I think this is probably the best way to do it in certain cases -- namely, when you *know* that the key is not going to change. And then you might as well use that key as the primary key, instead of an autogenerated key.

A good example of this might be a table of countries. If the data is like this:

countries:

Greg Hauptmann wrote:

Hi,

What's a good approach for referencing lookup/reference data (static) that one has loaded in a reference table. Say for example "tax codes", which pretty much have just the database ID and then a description field. Some options that come to mind:

1 - By ID - however this makes an assumption that the data always loads into a database and gets the same IDs (i.e. when you're loading your static reference data)?

2 - Search by description to identify the record - but obviously if one changes the description this isn't very good.

3 - Perhaps create a specific identify/key as a new column that you use, but is not the database Rails ID? How's this sound?

I'd recommend the enumerations_mixin plugin that both caches the table and allows lookup and by id and (unique) name. e.g.

self.tax_code = :exempt if tax_code === :exempt    ...

http://svn.protocool.com/public/plugins/enumerations_mixin/