I've been going back and forth about this kind of thing for a while. I reverse my opinion on it every project, and I'm wondering if anyone has any advice/battle scars to share. As usual, please forgive my long-windedness in advance. Let's say I have this: Table: PLANS ID int CATEGORY_CODE_ID int --Other junk removed for clarity Table: CATEGORY_CODES ID int DESCRIPTION varchar(200) "category codes", to the user, are 6-digit numbers, sometimes with leading zeroes, that have specific meanings. My customers usually want a metric truckload full of reports. Often, there are more reports than actual business logic. So much fun. Reports get much, much easier when you don't need to join over into a lookup table constantly. It would be nice to able able to simply say: Plans.find_all_by_category_code '123456' If I follow the 'pure' ActiveRecord intent for this, it would instead look like: Table: PLANS ID int CATEGORY_ID int Table: CATEGORIES ID int CATEGORY_CODE char(6) DESCRIPTION varchar(200) ..and I would then need to do: Plans.find(:all, :conditions => "categories.category_code = '123456'", :include => :categories), which would be much slower. ..and then @some_plan.category.description whenever I needed to display the description text. In this design, 'PLANS.CATEGORY_ID' would be a non-semantic synthetic value. Any find_by_sql queries would be much more tedious to write. Am I going to run into trouble by having my 'code table' be an association with a primary key that actually means something, rather than being synthetic? The code works either way, but I hate feeling like I'm going against the grain with ActiveRecord. Anyone have any comments or complaints? Thanks, --Wilson.
on 2005-12-16 18:24
on 2005-12-16 18:39
Having a primary key that has meaning is a "Bad Thing". What happens when they decide that 0003231 should actually be 0003322? It smells bad. We have a system that has this problem and it hurts. Primary keys are the unique identifiers for a row and should have no other meaning. There are some really cool ways that you can make this work in ActiveRecord that will keep things from being ugly. I will let others tell you more about ActiveRecord use. I just wanted to chime in and say: Don't make the PK have meaning.
on 2005-12-16 19:00
Just to lend my support to this argument - I can't think of a reason why anyone would ever use a meaningful primary key? Does it save space? Probably not very much given how cheap space is these days does it matter? Perhaps for a few specialist db's that are vast. Is it quicker? Well, there is one less column to deal with but since computers this year are so much faster than last year, does it matter? If you have to rebuild your db and code because you had to change the primary key, is it going to matter? I think so. Why take the chance. You gain so little and you lose so much (potentially). bruce
on 2005-12-16 19:12
OK... first thing, take Carl's advice (and my advice) and create an actual, auto_increment ID column for CATEGORY_CODES. You will save yourself a lot of pain in the end. I've tried to get around needing a Rails controlled ID field, and it just doesn't work well. But, doing that makes your life somewhat easier. Your Rails model should look like: class CategoryCode < ActiveRecord::Base has_many plans end class Plan < ActiveRecord::Base belongs_to category_code end Assuming you have a "code" column that holds the 6 digit code, which you could actually expose in your routes/URLs, to efficiently get all the Plans for a particular Category_Code you would do: category = CategoryCode.find_by_code(mycode, :include => :plans) plans = category.plans I would say this is the "Rails way" to do what I think you're trying to do. Let me know if any part of this doesn't make sense. -Brian
on 2005-12-16 20:13
that's the thing, sometimes you have to look at things differently when in the rails universe. i had to work this out myself when i started with rails coming from a PHP background. my first thought would be to attack the problem from the 'plans' side...i want a list of plans for a given code... so i would try to implement something like class Plan < ActiveRecord::Base belongs_to :category_code def self.find_by_category_code(code) find :all, :joins => "as p, category_codes as c", :conditions => ["c.id= plan.category_code_id and c.code = ?", code] end end plans = Plan.find_by_category_code("000123") which works perfectly fine...but rails makes you think about it from a different viewpoint (using your example) plans = CategoryCode.find_by_code("000123", :include => :plans).plans which, now that i have learned to think in rails, is much easier and i didn't have to write any additional code! and my $0.02 on the primary key issue....say your company/employer wants to change those codes, say add dd- (2 digits and a dash) to the beginning of all codes. since you've used this as a primary key, not only will the codes have to change in the category_codes table, but any other tables that reference that primary key will also have to change....not something i would want to have to deal with. Chris
on 2005-12-16 21:10
Chris, Thanks! That was the point I wanted to make. Tobias also has an interesting example of what you can do with ActiveRecord here: http://blog.leetsoft.com/articles/2005/10/31/scope... I think this scoping idea will make it easier to create the reports. Carl
on 2005-12-16 22:22
On 12/16/05 Chris H. wrote: > and my $0.02 on the primary key issue....say your company/employer > wants to change those codes, say add dd- (2 digits and a dash) to the > beginning of all codes. since you've used this as a primary key, not > only will the codes have to change in the category_codes table, but > any other tables that reference that primary key will also have to > change....not something i would want to have to deal with. A very good point here. I would put forth that a table's primary key (aka. ID) should be immutable. And a very good way to accomplish that is to pyt it totally under the database's control, so that all your app's code can to is refer to, and refer through, it. That might seem a little extreme, but it certainly makes Rails a whole lot happier when that's the case. :) -Brian
on 2005-12-16 22:59
In article <firstname.lastname@example.org>, wilsonb- email@example.com says... > Reports get much, much easier when you don't need to join over into a > lookup table constantly. > It would be nice to able able to simply say: > Plans.find_all_by_category_code '123456' Hasn't someone done at least one "lookup table" / "enumerated" plugin recently? Maybe even two people, though one was pre-plugins. Both implementations cached the lookup table and let you refer to it in some syntactically nice way, if I recall...
on 2005-12-16 23:03
On 12/16/05, Carl F. <firstname.lastname@example.org> wrote: > > > CATEGORY_CODE_ID int > > there are more reports than actual business logic. So much fun. > > CATEGORY_ID int > > ..and then @some_plan.category.description whenever I needed to > > > email@example.com > http://lists.rubyonrails.org/mailman/listinfo/rails > I seem to recall from my (incomlete) db theory... something like "4th (or 5th) degree normalization". On of the requirements for 4thN is that there are no "non-identifying" information in teh database... that means no primary keys, and composite-column-keys is the only way you get FK's to work. Somenoe correct me ? I have to deal with one accounting db that is this way and it is Soo Soo painful. --
on 2005-12-17 00:57
On Dec 16, 2005, at 11:59 AM, Bruce B. wrote: > Just to lend my support to this argument - I can't think of a > reason why anyone would ever use a meaningful primary key? I'll second (third?) that! > Does it save space? Probably not very much given how cheap space > is these days does it matter? Perhaps for a few specialist db's > that are vast. > > Is it quicker? Well, there is one less column to deal with but > since computers this year are so much faster than last year, does > it matter? I think everyone could agree that it's likely FAR FASTER to create an index and lookup against an index for typical synthetic ID type (INT, LONG INT) than a typical real ID (strings, strings everywhere!). -- -- Tom M.
on 2005-12-17 04:32
On 12/16/05, Tom M. <firstname.lastname@example.org> wrote: > On Dec 16, 2005, at 11:59 AM, Bruce B. wrote: > > > Just to lend my support to this argument - I can't think of a > > reason why anyone would ever use a meaningful primary key? > > I'll second (third?) that! > I think everyone could agree that it's likely FAR FASTER to create an > index and lookup against an index for typical synthetic ID type (INT, > LONG INT) > than a typical real ID (strings, strings everywhere!). > Thanks, everyone. I think you've helped me break myself of the senseless desire to avoid this join. If I ever need it to work a different way in some other context, I'll just do a materialized view for it in the DBMS. --Wilson.