0

I am working on an application design, using Ruby on Rails and Postgresql. I have a table with the following fields

Table: account_type Fields: id(primary key), name(String)

AccountType name is unique string (so am thinking about putting unique constraints on it). Depending on the name (type) I'm going to make some checks in my Models. Something like that:

def urban? self.name == 'Some long type' end

The question is: do I leave it like that? Or, as the other option, I can depend on some ID. So, assuming that my 'Some long type' is always created with ID=1, I can check for

def urban? self.id == 1 end

Is it a good practice if I do depend on the ID? What about readability? Are there other solutions to that problem?

uselpa
  • 18,732
  • 2
  • 34
  • 52
Xentatt
  • 1,264
  • 22
  • 35
  • No, it is not good practice to depend on id. As it is self incrementing, you may find yourself in confusion after e.g. truncating table of content, while the id counter will stay on the last of your deleted row. You should either create a column urban that will get 2 values 1 or 0 or depend on name as you've shown in first snippet. – konole Sep 16 '14 at 19:12
  • if you don't have data in AccountType (just name), maybe you should use `enum` for your `Accout` model? – Igor Guzak Sep 16 '14 at 19:27

1 Answers1

0

The second example is a text-book case of how NOT to use surrogate keys Your real primary key is account_type and should have a unique key. There is always endless debate about the 'goodness' of using auto-inc id columns for primary keys. To query by id depends on how the rows have been inserted. Querying by account_type.name is immutable.

Readability? the id field gives no information to what the record really means.

Other Solutions? I don't really see what problem is, but you could also use an enum type (but it is much less flexible than a lookup table.)

Community
  • 1
  • 1
user17130
  • 241
  • 1
  • 6