ActiveRecord - default ID vs custom identifier

Hi,

So far I've let Rails and ActiveRecord rule the game. But I think there's some things I should change.

I have a record (called Tag) that is searched by using the default auto incrementable Id that AR provides. On the Tag record I also keep a unique identifier which is a string of 13 characters. That identifier is really the Tag.

So my doubts are: - Should I set the custom identifier as the primary key and remove the default id? - Or should I keep the default Id but instead fill it with the custom identifier? - Would it make any difference when thinking of performance?

I'm currently adding some statistics record for each of those Tags. So one tag "has_many" statistic records. And this statistics record has the Tag identifier stored. That's when I thought that the common key to tie them together should be the custom identifier instead of the default id.

Any opinions? I hope I managed to explain my doubts.

Cheers.

Hi,

So far I've let Rails and ActiveRecord rule the game. But I think there's some things I should change.

I have a record (called Tag) that is searched by using the default auto incrementable Id that AR provides. On the Tag record I also keep a unique identifier which is a string of 13 characters. That identifier is really the Tag.

So my doubts are: - Should I set the custom identifier as the primary key and remove the default id?

No

- Or should I keep the default Id but instead fill it with the custom identifier?

No

- Would it make any difference when thinking of performance?

I doubt it.

I'm currently adding some statistics record for each of those Tags. So one tag "has_many" statistic records. And this statistics record has the Tag identifier stored.

Why store the tag identifier in the statistic? You have the tag_id presumably. If you need to show the tag itself it is statistic.tag.identifier or whatever it is called.

Colin

Colin Law wrote in post #999008:

So my doubts are: - Should I set the custom identifier as the primary key and remove the default id?

No

- Or should I keep the default Id but instead fill it with the custom identifier?

No

- Would it make any difference when thinking of performance?

I doubt it.

I'm currently adding some statistics record for each of those Tags. So one tag "has_many" statistic records. And this statistics record has the Tag identifier stored.

Why store the tag identifier in the statistic? You have the tag_id presumably. If you need to show the tag itself it is statistic.tag.identifier or whatever it is called.

Colin

Thanks Colin.

Why store the tag identifier in the statistic?

Externally the tag is known by its identifier. They are tags that are read with the mobile and when they come to the server they are found by this identifier. When a stat is created the tag where it belongs is also found by the identifier (creation of a stat is not triggered by a tag being read).

Yes, when the stat is created I could first use the tag identifier I get, find the internal tag ID and actually store that internal ID with the stats instead of the identifier. Then the relationship would be complete. But I guess I was trying to save that db call since if I would use the identifier for both that call wouldn't be needed.

Cheers.

It is almost always a bad idea to store the same data twice in the database (or in this case many times, in the tag and in each statistic). One issue is that it becomes very difficult to edit the tag as you have to change it in all the statistics, and the updates have to be protected by transactions in case a problem arises half way through. My advise is always to do it the simplest way initially and only worry about efficiency if such a problem arises. The bottlenecks in an app almost always turn out to be not where they are initially expected and time spent optimising the app before problems arise is time wasted. Also by ending up with more complex code then bugs are more likely and can be more difficult to fix.

Also in Rails it is almost always best to stick to the conventions (using id as the primary key in this case), the conventions can be overridden but usually this just adds complexity and makes life more difficult.

Colin

Colin

Don’t forget, you can also add a database index to a field that is going to be searched often, that will improve search performance.

In your model’s migration:

add_index(table_name, column_names, options)

from: http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

Got it. I'll stick to the conventions unless really needed otherwise later.

Thanks a lot for your comments.