storing multiple items in single table entry

[Zaeed]

Hi,

I have an approval system im building for my database, what i want to beable to do is have a list of people that have the right to approve somehow be stored in a table.. this list would probably be their userID. My problem is I do not know how to store something like this..

Approval Table
approvalID - ChangeNumber - requiredApprovals - currentApprovals - (something here that can sore multiple numbers)

see my issue? i need some way of storing a varying amount of numbers that can easily be used to identify people when pulling this information out and into a form.

can anyone help? sorry if the above doesn't make sense, ill try to clarify if you want.

 

[georgedwilkinson]

So, you're saying that each user can be qualified for one or more approvals and each approval can be executed by one or more users?

If so, that is a classic many to many pattern (M:M). You typically implement M:M via a junction or associative table. In that table, you would have an FK from your 2 tables (Approval & wherever you keep your user IDs or groups).

 

[Zaeed]

basically the system is for tracking changes. In order for a change to proceed it must be approved. I have my system correctly identify who the approvers are. there are usually 3 approvers.

After a change has been initially submitted it goes into a standby while it waits for all the designated approvers to view the submission and click approve.

So what I am trying to do atm is design an approval table, which has a single entry for each Change (there is a table of changes, each row a different change).

Where I am getting stuck is somehow storing who the approvers are.

Does this make more sense?

 

[neileg]

George is right but maybe I can explain in simpler terms. You can't do this in one table. In general, each entity in a database needs a table. You have three entities, changes, approvals and approvers. So you need three tables. These might be

tblChange
ChangeID Primary key autonumber
Description text
RequiredApprovals Number

tblApprovers
ApproverID PK autonumber
ApproverName text

tblApproval
ApprovalID PK autonumber
ApproverID Foreign key storing PK of approver }Linked by multifield index set to no duplicates
ChangeID FK storing PK of change }Linked by multifield index set to no duplicates
ApprovalDate

The relationship between tblChange and tblApproval and between tblApprover and tblApprovals are both one to many giving you an overall many to many between tblChange and tblApprover. The ID fields in tblApproval are set to no duplicates so that the same approver cant approve the same change twice. You can then count the number of entires in tblApproval and compare this with RequiredApprovals.

 

[Zaeed]

Ok Neil, I think im starting to understand this. This seems a little more complicated than the previous things i've been doing so this is approaching new ground for me and I thus may need to be led by the hand a little.

Once I setup the table structure like this, how do I populate it so that I can assign approvers to a change?
(the basic coding structure)

Also, how do I design the tables to have a one to many relationship AND have them Indexed with no duplicates.. It seems to force a one to one relationship with the indexing on.

 

[neileg]

Ok Neil, I think im starting to understand this. This seems a little more complicated than the previous things i've been doing so this is approaching new ground for me and I thus may need to be led by the hand a little.

's alright.

Once I setup the table structure like this, how do I populate it so that I can assign approvers to a change?
(the basic coding structure)

Use a form with combo boxes to select the values.

Also, how do I design the tables to have a one to many relationship AND have them Indexed with no duplicates.. It seems to force a one to one relationship with the indexing on.

You need to define a two field index so that the combination of the fields is unique, not the individual fields.

 

[Zaeed]

You need to define a two field index so that the combination of the fields is unique, not the individual fields.

I dont understand what you mean by this

 

[neileg]

Open your table in design view. Click the Indexes button on the toolbar. This will show your primary key details as currently defined. In the first blank row, type a name in the Index Name column (can be anything MyIndex, Fred, etc). In the Field Name column, type the name of the first field for the index ApproverID or whatever, or select from the drop down. The sort will default to ascending but it's irrelevant. In the next blank line leave the index name blank and select the second field in the Field Name column. Now place the cursor in the Index Name cell where you typed your new name in. In the box at the foot of the index window, set Unique to Yes. Close and save the table.

If anyone tries to enter data in these two fields that duplicates an existing entry, the index will prevent this from being saved.

 

[Zaeed]

this may be a stupid question, but should the relationships be defined in the front or back end of my database?

also, what field do i select for the second line of the index. For example, in your above table list, for tblApproval, when creating the index for approvalID, what would the two fieldnames be? one would be approvalID but what about the other?

 

[neileg]

In the back end

 

[Zaeed]

ah
yes.... thats EXACTLY how i had it lol

 

[neileg]

also, what field do i select for the second line of the index. For example, in your above table list, for tblApproval, when creating the index for approvalID, what would the two fieldnames be? one would be approvalID but what about the other?

One index, two fields, ApproverID and ChangeID. ApprovalID is yourPK.

 

[Zaeed]

Ok so I have this setup now, but unfortunately I fail to see how this solves my problem.

To my untrained eye, it seems that this produces the opposite of what I want. When I open the Approver table, there are the 2 fields approverID and approverName, and a little plus sign on the left that expands a view of the Approval table with matching approverID. Thus, it seems that this creates multiple approvals instances for each user, what I want is the opposite, multiple users for one approval.

Or have i done something wrong? (The most likely situation)

*Edit* Hang on, I think i'm starting to see it.. From the above, would you thus create an approverID for each and every user that can approve a change, and then for each approverID you list all the Changes that user can approve. i.e. user bob can approve change 1, 2, 3, 4, 5 so in the approval table there would be 5 instances of approve'bob' for the different changes?

is this correct? (sorry for seeming a bit dull)
atm approvals table looks like the following

ApprovalID | ChangeNumber | ApproverID | Approved
____ 1 ___|_______ 1 ____|_____ 1 ___|_________
____ 2 ___|_______ 1 ____|_____ 2 ___|_________
____ 3 ___|_______ 2 ____|_____ 1 ___|___ _-1___
____ 4 ___|_______ 2 ____|_____ 6 ___|_________
____ 5 ___|_______ 2 ____|_____ 10 __|____ -1___

 

[neileg]

You're getting there! However, there's no way you can handle a complex relationship just by looking at tables. A properly set up form is required because the way the user interacts with the data will often be quite different from the way data behaves in the tables.

As you suggest, there has to be an entry in the approval table for each valid combination of change and approver. So, yes, if Bob has approved 5 changes, Bob will appear in 5 records. Similarly, if change 123 requires 3 approvers, then 123 will appear in 3 records.

I assume the Approved field is a yes/no. An alternative is to only create the approval record when the approver approves. Depends on your business process whether this would be more appropriate.