Best Design Practice To Provide an ID Number for both Invoiced Work and Non-Invoiced Pipeline Work?

Hi experts,

A simplified Access database of my Employment Services application is attached below.
The database is intended to record details in order to create Invoices for Placements and Retainers.
These can be related, as a Retainer fee may be charged first, and then followed by a Placement fee when the position has been filled.
As a result, the Placements and Retainers tables can be linked via an AssignNumber in the Assign table (ie. short for Assignments)

The database is also intended to provide details of potential future Pipeline work.
This Pipeline table essentially records a description of future Employment Services potentially required by a client.
It is also linked to the Assign table.

The relationships are:
Table Relationships
The Problem: How To Provide A Number for Pipeline Submissions
As it is currently designed, the database is auto-creating an Invoice number, even for Pipeline work.
This invoice number is calculated in the format YYMMDD + a 2-digit number based on number of invoices that day.
Eg. the second invoice on 25-Jan-2018 will appear as:
InvoiceNumber: 18012502

Obviously potential pipeline work should not receive an Invoice number, because it won't be sent out as an Invoice.
If I continue to use the Invoice Number for Pipeline submissions, then the Invoices that do get sent out to clients potentially won't have sequential numbers.
However, I'm at a loss as to how to handle this.

For example, I've wondered if a Pipeline submission should have an "Invoice Number" of, say, "Pipe-00001" (for the first Pipeline submission)
But this route would require some fancy calculation to find and increment the last Pipe-0000x".
So I'm wondering what would be a better practice, or best practice, for this problem.
Here is what the Invoice Number looks like for Placements;
Placement Invoice Number
And here is what the Invoice Number looks like for Pipeline:
Pipeline Invoice Number
Obviously the Invoice Number looks wierd for Pipeline work, since no Invoice would be sent out.
But I'm somewhat stumped as to how to handle this.

I'm wondering if this PipelineNumber should also appear in the InvoiceNumber column?
Or should there be two columns: one for InvoiceNumbers, and one for PipelineNumbers?
Or should there be a separate table?
All these ideas seem far from elegant, and I'd prefer to avoid some fancy calculation method.

Any suggestions/solutions for this please?

Many thanks in advance,
ChrisSelf EmployedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Your current design is a super-sub-class model. Thus placements, pipelines and retainers are a sub class of an assignment (btw, don't use abbreviations, when not necessary and the common approach is using plural nouns for table names). Your assigments table has a discriminator to define the type of the sub-class, which implies that one assignemnt is either a placement, a pipeline or a replacement, but only one of them. Correct?

I'm not sure about the semantics, cause in this case I would expect more common attributes in the assigments table. Also - while not being a native speaker - why the difference between the table name assignments and the discriminator name submission type. This indicates two different entities. Otherwise it must be assignment type. And further more, as far as I understand your wording, your pipelined items can lead to be "converted" to a placement. I'm not quite sure whether they are really sub types of the same super type.

Can you explain the business process behind?
Gustav BrockCIOCommented:
The simple method is to not assign an invoice no. until submit, thus linking (as now) via ID/AssignID leaving InvoiceNumber blank (Null).
ChrisSelf EmployedAuthor Commented:
Hi Ste5an,

Oh, I'd not known its a super-sub-class model.  
Thanks for pointing that out, it makes things clearer now that I'm aware of it.
(And its prompted me to go learn a bit about design patterns).

Yes correct, an assignment is mutually exclusive between placement, pipeline and retainer.

Ok, I'll match the Assignment table to the discriminator name Submission Type.
I'd not thought about that before, and it makes sense - thanks for that point.

Yes, pipeline work can later lead to a placement.  However:
(i) pipeline work requires substantially less detail, because at time of entry its only a speculative judgement about future work;
(ii) pipeline work may not therefore eventuate into placements and future fees

Hence, and primarily because of point (i), that was my reasoning in making pipeline work a seperate entity/table.
Having said that, I'd not previously thought about the two being sub classes of the same super group.

And retainers are fees received now for spending the next few months to do exclusive research & investigation that may lead to possible placements.
Once again, the substantially less detail required led me to use a different table for retainers.
(Hopefully that all covers your question about the business process behind it)

Thanks for these points, and the advice on table names being plural and non-abbreviated.
They are all extremely helpful, and very much appreciated!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ChrisSelf EmployedAuthor Commented:

Thanks, that's a simple, but very interesting idea which I'd not thought about.
This would remove the Invoice Number from the Submission form.

So if I were to take a stab at how to do this, I would guess:

1) That Invoice Number would be generated later for Placement and Retainer tables.
    For example, via an after-update event on those tables.
    Or when a button for "Generate Invoice" was pressed.

2) Those Invoice Numbers could be kept in a separate table and linked back to the Placement and Retainer tables.
Gustav BrockCIOCommented:
1. Yes. After the final confirmation by the user.

2. If you adjust the Assign table to allow blank InvoiceNumber, and simply update this at submit, you wouldn't need anything else.
ste5anSenior DeveloperCommented:
Using a separate invoice table is a better idea. Here you can track the invoice number and when it became one. And when you keep the super-sub-classes, then the model should be more strictly defined, e.g.

relationship diagramassignments tablepipelines table
ChrisSelf EmployedAuthor Commented:
Thanks very much for that detailed diagram Ste5an...both interesting & useful.
I've digested as best it as I could, and have attached an updated dummy database to reflect my interpretation of your suggestions.
(Probably a few things I didn't understand so well)
Below are the new relationships.
- All PK IDs are now 'vertically partitioned'.  That is, based on the auto-generated Assignments PK ID (as per the subsequent image).
- I've added two tables: for the Invoice Numbers and the Pipeline Numbers.
Invoice Relationships - Updated
The individual data record tables for each submission type are the same, albeit "simplified".
- I've removed the AssignmentNumberID foreign key
- The data record table's PK ID now links & identifies them with the correct row in the Assignments table (via its auto-generated PK).
Pipeline, Placement and Retainer Tables - Updated
The Submissions form is now based on the below query.
- I included the Invoice Number and Pipeline Number tables so that I could later use VBA to enter the appropriate calculated number.
SELECT Assignments.*
        INNER JOIN InvoiceNumbers 
        ON Assignments.ID = InvoiceNumbers.[ID]
        INNER JOIN PipelineNumbers 
        ON Assignments.ID = PipelineNumbers.ID;

Open in new window

The Submissions form is much the same, except that it no longer has an Invoice Number at the top.
- That Invoice Number, and the Pipeline Number, are now calculated when the form is submitted, via the Submit button.
Submission Form - Updated
The VBA code to calculate the Invoice Number and Pipeline Number is shown below.
- it populates those fields in their respective tables via the query in the form's recordsource.
Private Sub cmdSubmit_Click()
    Select Case Me!cboSubmissionType
        Case Is = "Placement", "Retainer"
            If Not IsNull(Me!txtID) Then
                Me![InvoiceNumbers.ID] = Me![txtID]
                Me![InvoiceNumbers.InvoiceNumber] = GenerateInvoiceNumber
            End If
        Case Is = "Pipeline"
            If Not IsNull(Me!txtID) Then
                Me![PipelineNumbers.ID] = Me![txtID]
                Me![PipelineNumbers.PipelineNumber] = GeneratePipelineNumber
            End If
    End Select
    DoCmd.Close acForm, Me.Name

End Sub

Open in new window

I hope that captures the intent of what you and Gustav were suggesting.
But a couple of things I struggled with were:

1) I didn't understand why to include the multi-field Unique indexes that you highlighted in yellow.
    I eventually resorted to just the Assignments table PK to link the other tables

2) I didn't understand why you highlighted the validation in yellow.
    I liked the idea, because I'd not seen that approach before.
    But at the moment I'm using non-editable dropdowns in the form to maintain validation.
    I wondered if you were highlighting it because my previous model didn't have enough validation (ie. through the form dropdowns)

Many thanks again for your help...I'm not sure if this database is now totally right, but it certainly seems to be much better!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
1) & 2) Just take a look at your foreign key relationship. It allows that the same ID from Assignments exists in Placements, Pipelines and Retainers.

This is not possible, when you add the discriminator to the foreign key relationship and when you add check constraints on each table, which only allow one value.
ChrisSelf EmployedAuthor Commented:
Best solution simply points the future reader to the 'finished' solution.
The assisted solutions refer the reader to the thinking behind it.

Points allocated on the basis:
(i) Gustav provided an excellent and succinct solution to the issue
(ii) Ste5an documented a way to achieve that solution, plus added several additional comments and insights that immediately helped develop my understanding of database design, hence the heavier point allocation.

Many thanks to both Gustav and Ste5an.
Gustav BrockCIOCommented:
You are welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.