Associate a specific list with a table of unique records

Question

I am creating a C# application that will log a product request.  That request will require 10 approvals.  I want to maintain that list of approvals in its own table so they can be added, changed or removed and not affect the product request records . What I want to happen is that when a new product request is created a set of associated approvals are created in its own table.

 

Is this the best way to handle this?  I need a little help putting this together.  Thanks

 

Bennie

Answer 1

Separate table is a good idea. But about "creating records in it as soon as product request is created", I am not so sure about.

 

My suggestion:

 

t1 - product request : insert record as soon as product request is created. 

 

t2 - product request approvals : a set of 10 records which indicate which approvals must be obtained. Note that this table DOES NOT have record for actual approval received.

 

t3 - product request approvals received : again a set of 10 records which indicate that approvals actually received. There may be 0 records at the beginning but insert each record as approval is received. This has who approved, when approved, comments, etc.

 

t4 - Optional - auditing of t3 table. In this log each change in t3 (insert update delete of t3) Who gave approval and then revoked it, etc. 

 

The benefit of above approach is you can select records from  t2 left outer join with t3 and know pending approvals. I am sure you will need it some time. Note that t2 is just a template table. This approach protects you from increase / decrease in number of approvals.

 

 

Answer 2

Thanks!

 

Feel free to ping for any other question(s) you have face in the process. 

Answer 3

Thank you Nitin.  The missing link for me was the Left Outer Join and inserting after approval.  You have cleared things up.  

 

9 years ago I was just getting started as an independent consultant and was hopeful to pick up as a programmer.  Consulting opportunities directed me in other areas, BA and Systems admin.  I now have the fortune to implement small projects that are mostly data capture and reporting.  A great opportunity to learn.

 

Thanks again

 

 

Answer 4

Somehow it surprises me that you ask your first question after full 9 years after becoming member of this forum.