That does seem a bit overly complicated. Again, assuming that my model would work for your requirements, to list all of the documents that a user is responsible for you'd just:
SELECT DocumentId, ApprovalStatus, etc. FROM DocumentApproval WHERE UserId = #UserId# If you only want those that are awaiting approval: SELECT DocumentId, etc. FROM DocumentApproval WHERE UserId = #UserId# AND ApprovalStatus IS NULL Why does it need to be any more complicated than that? As a side issue, and this is just my opinion, but it comes from years of data modelling, I wouldn't indicate administrator approval by putting a record in the tblDocApprove table with a User_id of 0. You're breaking all kinds of data modelling rules such as: - never allow your keys to have any meaning (i.e., User_id should simply be an arbitrarily assigned value) - a foreign key (which User_id in the tblDocApprove table should be) must have a matching entry in the primary key of the related table (does the Administrator actually have a User_id of 0?) - Never try to put two pieces of data in the same column that have different meanings Please feel free to ignore all of that. I'm just a big stickler for what I consider to be proper database design, so I felt a need to point that stuff out. Thanks for listening, Bob > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of David Nesbitt > Sent: Tuesday, 5 February 2002 5:45 a.m. > To: [EMAIL PROTECTED] > Subject: Re: [CFTALKTor] Q: Approval Process > > > ----- Original Message ----- > From: "Bob Silverberg" <[EMAIL PROTECTED]> > Subject: RE: [CFTALKTor] Q: Approval Process > > Without knowing all of the details, it sounds like you have a > one-to-many > > relationship between document and user. Here's what a basic > table structure > > might look like: > ...snip.... > > Thanks for you reply. My post was really more out of frustration > than anything > else. I'm using a table structure similar to what you > illustrated, except I've > been breaking the Approved/NotApproved into bit/yes|no fields to > try and track > them: > > tblDocApprove > ------------------------ > doc_id > User_id > Approved > Not_approved > > ie > doc_id | user_is | Approved | Not_Approved > 12 | 100 | 0 | 0 > 12 | 16 | 1 | 0 > 12 | 344 | 0 | 1 > 12 |0 | 0 | 0 > > Every document would have a couple User_id's in it and I'd throw > in a 0 (zero) > user to take the place > of the Admin when it gets passed to him/her. Then I was doing > some funky select > stuff: > > Select sum(approved) as SumApr, sum(Not_approved) as SumNapr, > count(user_id) as > Cuid > from tblDocApprove > Where USER_ID <> 0 > AND DOC_ID = 16 > > and checking the SUMAPR against the User Count (CUID) to see the > status (if > SUMAPR = CUID then all the users approved). > I hadn't though of using Nulls. I'll have to give that try. I > have a tendency > to try and make things overly complex, the above query snippet is > only about 1/5 > of the actual query. > > I suspect that my main problem is that I'm trying to list all the > documents a > user is responsible for in one giant query. I suppose I should > break it down > into smaller parts like a good programmer and not an sql addict :-) > > > : David Nesbitt > : Internet Developer (ColdFusion) > : Tundra Semiconductor Corporation > : Ottawa, Canada > : (e) [EMAIL PROTECTED] > > > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "David Nesbitt" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Bob Silverberg" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
