Обсуждение: Postgres, DB design, and object IDs (of any ilk)
Just looking for postgres "best practices" input from the veterans: If the primary key of the customer table is cust_short_name and my DB reflects also customer departments, I can link a customer to its departments one of three ways: 1. The department table has a cust_short_name column and makes that the first segment of its primary_key; 2. I use OIDs and link back to the customer's row with the row's OID; 3. I give the customer a cust_serial_id column and make it SERIAL and give the dept table a column called cust_serial_id. I know serial IDs are preferred to OIDs so let's forget #2. With #1, where we have a three or more level identifying hierarchy I end up repeating two or more primary keys in building up the primary key of the lower levels. Not the end of the world, but my last DB was built in an OODB so I got uses to having object identity and am tempted to use serial_ids for same under postgres. Bad idea?
On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton <kentilton@gmail.com> wrote: > If the primary key of the customer table is cust_short_name and my DB > reflects also customer departments, I can link a customer to its departments > one of three ways: > > 1. The department table has a cust_short_name column and makes that the > first segment of its primary_key; > > 3. I give the customer a cust_serial_id column and make it SERIAL and give > the dept table a column called cust_serial_id. This is the very well tread 'natural vs. surrogate key' debate. There's tons of threads about this online...including the archives here. It's a very complicated issue with lots of facets (performance, logic, elegance of design) with no clear right answer so it largely boils down to personal choice. I would venture to guess that a large majority of database developers use incrementing serial keys. That said, I personally was in that camp until I was tasked with converting a large erp system written in cobol/isam (where natural keys are used for technical reasons) into sql. Following that experience, I have decided that a hybrid approach is best for me. I would strongly advise learning how to map out your data either way and choose the approach that best meets your design criteria. I'm especially skeptical of database development standards that _always_ use a serial primary key and _always_ use it for relating data. merlin
I wouldn't trust OIDs, because they are 32bits for once. Secondly, Watch for index size. That's the main reason why (big)int as a key reference is a win over other types - at least in my general practice. And third advice, try different approaches, and queries - to figureout what would suit the solution. Anyone who says, that this is always win, and something else is not - is a lier.
Merlin Moncure wrote: > On Thu, May 21, 2009 at 12:35 PM, Kenneth Tilton <kentilton@gmail.com> wrote: >> If the primary key of the customer table is cust_short_name and my DB >> reflects also customer departments, I can link a customer to its departments >> one of three ways: >> >> 1. The department table has a cust_short_name column and makes that the >> first segment of its primary_key; >> >> 3. I give the customer a cust_serial_id column and make it SERIAL and give >> the dept table a column called cust_serial_id. > > This is the very well tread 'natural vs. surrogate key' debate. Ah, thx for the label, I have been able to google up some pros and cons. Thx again, kt
kentilton@gmail.com (Kenneth Tilton) writes: > Just looking for postgres "best practices" input from the veterans: OIDs are decidedly a bad idea; the difference between "natural" IDs and "surrogate" IDs is a general database issue that is fairly well documented in the literature and is not notably a PostgreSQL-specific issue. There are competing doctrines, basically between the respective beliefs: a) Some believe that there should always be a natural primary key, and that it is wrong to attempt to use surrogates b) Others contend that even when users claim to provide natural primary keys that they are actually lying when they suggest certainty about this Major bashing can take place back and forth. -- output = ("cbbrowne" "@" "acm.org") http://cbbrowne.com/info/wp.html Rules of the Evil Overlord #100. "Finally, to keep my subjects permanently locked in a mindless trance, I will provide each of them with free unlimited Internet access. <http://www.eviloverlord.com/>