Database Design question

[ggrayman]

I am designing a database that tracks the approval sequences of various document submissions. The various types of documents have different approval sequences (different people must approve the the various document types). I created a table for the names of the different approval sequences, but I cannot figure out how represent the approval sequence and attach it to each approval sequence name. Would I make a table for each approval sequence with each field representing the steps of the sequence?

[patjones]

Generally speaking, it seems like you may want to have a table that stores information about the people involved, a table for the types of files involved, and so forth...but beyond that it's hard to advise you given what you've laid out so far.

Can you maybe give a few concrete examples of a typical approval sequence?

Pat

[hidust]

@ggrayman
I have a database that does something similar. From the process side of things, do you have any identifiying numbers for your documents? I have multiple tables one for each type of document that uses a document number so these documents can be searched for and referenced. The rest of the table contains approval timestamps, the approver, metadata from the document, and a link to the document in .pdf format stored on the server. Provide some more details might be able to help you.

[ggrayman]

Thank you both for responding. Attached is my current chart of relationships. My problem is on the bottom-right. I would like to be able to assign a predefined Approval Sequence to each Submission without having to manually define the sequence for each submission. How to create a single item that is actually a series of predefined items. Thanks for considering this.

[patjones]

Hi,

I've just been trying to digest your diagram. A few questions...

First, is the relationship between "Submission/Process Initiation Types" and "Approval Sequences" one-to-one? In other words, one Sub/Process Initiation ID corresponds to one and only one record in the Approval Sequences table.

Second, at the point where a submission is entered in the database, what information in Approval Sequences, if any, do you have?

Third, how do you assign Submission Type ID, Sub/Process Initiation ID, and Approval Sequence ID? Are they autonumbers, or related to something substantive about the documents?

Thanks.

Pat

[ggrayman]

@zepphead80
Pat,

Thank you for your assistance.

1. Each Submission Initiation Type will have one Approval Sequence, but more than one Submission Initiation Type may have the same Approval Sequence.

2. Once we know what the submission type is, we will know what the appropriate Approval Sequence is.

3. These are lookup tables for predefined records. As the data is entered, the ID would be automatically assigned.

Thanks again!

[patjones]

In my opinion, the way those three tables off to the right are setup, it looks pretty good, but I do understand what you're saying about the Party/Process columns in Approval Sequences. It seems like there should be a "cleaner" way to do that, but I actually think it is okay. The only thing I would consider is making a table that stores the Party/Process names with a corresponding code, and just use that code in the Party/Process columns in Approval Sequences.

Have there been any issues with the database operationally that would cause you to question the table structure? The main point is to avoid duplication of data, which you seem to be doing.

Pat