Обсуждение: artificial keys or not?
we have lots of small relations that only consist of a few attributes. like in an ecommerce app we have relations like: payment_methods payment_methods_lang delivery_types delivery_types_lang basket_states basket_states_lang payment_methods, delivery_types, basket_states mostly just consist of foo_id serial [PK] active boolean the *_lang relations look like foo_id integer [PK] language_id integer [PK] label varchar description text The problem is, our queries got a bit unreadable.. SELECT b.basket_id FROM baskets b WHERE b.basket_state_id IN (1,3,6,7); ..dosen't tell much, so we came up with an additional attribute called handle, which is just an alternate key for foo_id. SELECT b.basket_id FROM baskets b INNER JOIN basket_states bs USING (basket_state_id) WHERE bs.handle IN ( 'open', 'sign_pending', 'freight_cost_calc_pending', 'expired' ); looks more readable, but there is need for one more join as well.. ..so the only question is: drop the the serials as PKs and just use short text handles? our database size is arround 290 mb. there shouldn't be speed issues as long as proper indexes exit, right? some of the conditions are dynamic though, so there can't be an proper index in any case. any other concerns? thanks in advance
> ..so the only question is: > > drop the the serials as PKs and just use short text handles? our > database size is arround 290 mb. there shouldn't be speed issues as long > as proper indexes exit, right? some of the conditions are dynamic > though, so there can't be an proper index in any case. any other concerns? Someone (a manager) somewhere (probably marketing) sometime (when you least expect it) will demand that those short text names be changed, possibly for a completely irrational reason. I know there are still text books that claim that artificial keys are evil for some inscrutable reason, but hard experience has taught me to never, ever, under any circumstance, make a primary key out of data that comes from humans, and to be very suspicious of using data that will be visible to humans. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
"Scott Ribe" <scott_ribe@killerbytes.com> wrote in message news:BE8ED6E7.21FC1%scott_ribe@killerbytes.com... >> ..so the only question is: >> >> drop the the serials as PKs and just use short text handles? our >> database size is arround 290 mb. there shouldn't be speed issues as long >> as proper indexes exit, right? some of the conditions are dynamic >> though, so there can't be an proper index in any case. any other >> concerns? > > Someone (a manager) somewhere (probably marketing) sometime (when you > least > expect it) will demand that those short text names be changed, possibly > for > a completely irrational reason. I know there are still text books that > claim > that artificial keys are evil for some inscrutable reason, but hard > experience has taught me to never, ever, under any circumstance, make a > primary key out of data that comes from humans, and to be very suspicious > of > using data that will be visible to humans. This is my experience also. But opinions vary, and I wouldn't be surprised to see a further post from someone which argues the exact opposite. :-) > > > -- > Scott Ribe > scott_ribe@killerbytes.com > http://www.killerbytes.com/ > (303) 665-7007 voice > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >