Re: identity columns

> There seems to be a lot of talk in this NG about the good, bad and ugly
> aspects of primary keys - especially Identity columns.

Thanks for all of your responses.
I think my current database design is on the right track - I had used "email address" as just an example of a "possible" candidate key for the Employee table. The system we've built is an online ordering system for my companies customers - so we get whatever information the customer can give us - we don't have much control over the content of the Employee table.

One last question - does anybody have any design resources for building a generic approval routing scheme? Right now - we have a table called "Req" (short for Requisition - we've abreviated it everywhere in the database) and another table called "ApprovalRouteQueue"

create table Req (ReqId int)
create table ApprovalRouteQueue (ReqId int, ApprovalCounter int)

So - obviously - there is a 1 to many relationship between Req and Approvals. Each time a Req gets approved the current "Pending" row in ApprovalRouteQueue gets updated, emails get sent to the Employee who created the Req, who approved the Req, and the next Approver. Then the next Approver clicks the link in the email, comes to the website and the process starts all over - this keeps repeating until the Req is denies somewhere along the way - or Approved - the number of times it needs to be approved is defined differently for each customer. This is really a simplified model - there are about 10-15 tables involved here that define the levels of approval, email notices that get sent, etc.

The problem with this design - it only works well for Serial approvals. Some customers would like "Parallel" approving - which means instead of an Approval Queue I need an Approval Collection. Has anybody seen an exisiting design of this? Received on Sun Jan 06 2002 - 16:20:02 CET