0

What is the best way to handle database table ids, if entries of that table are expected to be created and deleted frequently? Basically, most entries are temporary over a timespan of a few days. However, entries might stay in the table for an arbitrary amount of time as well.

Using an integer or long id with Auto Increment might be problematic after a certain time, when the maximum key value is reached. Considering a large user base, where most users will probably create a dozen or more entries each day, this might happen sooner than expected...

Therefore the question. Is there some mechanism with MySQL or another database to get looping ids or any other solution to handle this problem with good performance?

Btw. this is in context of a Ruby on Rails application. I was thinking about having nested ids, i.e. make an entry unique on user_id and user_entry_id. But I don't know, how well this approach is supported by rails.

Edit: I see I should have done some calculation on how likely it is that the id range will run out anytime soon. I guess I don't have to worry about that problem anytime soon. Still happy, if people can report some experiences related to that problems. If anybody ever had problems with that....

Felix Klein
  • 21
  • 2
  • 1
    A combined primary key may be the answer, as you're alluding to. I don't see why that would not be supported by RoR. – D.N. Jul 19 '11 at 01:56
  • Considering ActiveRecord relation, e.g. belongs_to, has_one etc. Does this really works when the connection has to consider two databse columns? No wait, that's wrong! Shouldn't be a problem... – Felix Klein Jul 19 '11 at 02:00
  • If this link is true (http://compositekeys.rubyforge.org/), then RoR does NOT support combined (or composite) primary keys (at least without installing the ActiveRecords piece as they mention). Go figure... – D.N. Jul 19 '11 at 02:01

2 Answers2

0

Yep, you'd want to create a composite key as D.N. has stated, here's another post which explains how that works/why you'd want it. (This post is in SQL-Server but MySQL is much the same.

Why use multiple columns as primary keys (composite primary key)

RoR supports multiple primary-key columns. This is very common database practice. (you do need the extension though)

Community
  • 1
  • 1
Edgar Velasquez Lim
  • 2,426
  • 18
  • 15
0

I would look at using a UUID http://www.rubyflow.com/items/5717-uuid-primary-keys-with-activerecord-in-rails-3

Shane Courtrille
  • 13,960
  • 22
  • 76
  • 113