Table design invoices

Hi,

I have a question regarding a somewhat complex table design issue. The situation is:
- There is a table with activities
- There is a table with appointments
- There is a table with invoices
- An appointment is related to an activity
- An activity has a price
- There are 2 types of activities:
     * Activities where the customer pays for each appointment
     * Activities where the customer pays for the activity only once, no matter how many appointments
- The appointments should have a relation to the invoices, on the invoice you must be able to see how many appointments have taken place
- Appointment activities can be changed, using an update

Currently I have some tables constructed, but it doesn't work properly like described above. The complexity is with the pay-only-once activities. This needs a lot of complex checking in code, to make sure the activity is invoiced only once.

What would you suggest as table design?
dhmderuiterAsked:
Who is Participating?
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.

grendel777Commented:
Hi dhmderuiter,

You're going to need to construct a many-to-many table that keeps track of (1) clients and (2) the pay-once items that they have already paid for.  Then when you generate an invoice, it will look up any pay-once items and see if that client has an entry in the already-paid table.  If so, change the invoice amount to $0.  The only other option would be to add multiple boolean values in the client table: Have they paid for service 1?  Have they paid for service 2?  Etc., but I highly recommend against this  unless you're certain that the items will not change, because you'll need to change the table structure every time which WILL become a nightmare.  Does that make sense?  You need to capture if person X has paid for service Y and compare them at invoice time.

Hope that helps!
0
dbbishopCommented:
is the first step is to create an activity or an appointment?

It appears that there are dual rules here:
If an activity can have multiple appontments, and the customer pays for each appointment, then the price would be tied to the appointment, but if there is only one payment no matter how many appointments, then the price is tied to the activity.

If that is the case, you might have a price in each (activity and appointment) and an entry would be made in one or the other. Your logic would probably need to make sure that both were not entered (in other words, the customer would pay for the activity and each appointment charge would be 0, or there would be an activity charge and each appointment charge would be 0.

With this type of design, you could just sum the activity and appointment charges together, since you would only have one or the other. An indicator in the activity table could indicate whether they were charged once or for each appointment and your transaction logic could make sure the proper table was updated with the charge.
0
dhmderuiterAuthor Commented:
Well, to clearify the exact case, there is a number of tables:

- Activities table: includes preset definition of activities, including ID, name and price, plus a boolean pay-once
- Clients table: includes ID, name, etc of clients
- Appointments table: includes time of appointment and references to client, activity and invoice-item
- Invoice table: includes invoice date and printed boolean
- Invoice-items table: includes price of the item, payed boolean and reference to invoice table

The activities and clients table are filled with activities and clients.

When an appointment is made, automatically the invoice and the invoice items are generated, based on appointment data and the selected activity. Invoice items are added to the current invoice until the invoice is printed, after that a new invoice is created (this to allow multiple appointments on a single invoice, until printed).

It would be easy if each activity had to be payed, then for each new appointment a new invoice-item would be created. However now it's also possible that an activity has pay-once=true. In that case, for the selected activity multiple appointments can be made, but only 1 invoice item should be created.

What makes it even more complex is that it's possible to change the appointment activity if the invoice isn't printed yet.

So the following situations are possible:
1. Normal activity appointment
2. Normal activity appointment > Changes to other normal activity appointment
3. Normal activity appointment > Changes to pay-once activity appointment
4. Pay-once activity appointment
5. Pay-once activity appointment > Changes to other pay-once activity appointment
6. Pay-once activity appointment > Changes to normal activity appointment

I hope this makes it a bit more clear, what do you think?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbbishopCommented:
I love how users can think something should be SO simple. My boss can merge data manually from several different sources into an Excel spreadsheet, examine the data and make intelligent decisions on what to do with it, so he thinks, "let's write a program to do it." What he in effect wants is an artificial intelligence system that can think like he does. If a=b and c=l and d=i then p=q unless t=r and v=w then p=i unless... It can become a nightmare.

Check monster.com and careerbuilder.com :-)

Apart from that, this sounds like a good candidate for some complex triggers to handle populating invoices. It will need to add new, update and delete invoices based on the type of activity and appointment, since both the previous (DELETED) and new (INSERTED) values are available.
0
dhmderuiterAuthor Commented:
Not sure what to do with this last reply, as I already know it's complex and that there is a lot of checking needed in the application :-)

Basically what I'd like to know is, how would you design such a table structure? I'm asking, because maybe some experts have experience in this area and come up with better ideas than my own.
0
grendel777Commented:
I would create a view that holds a list of all clients who have paid the paid-once activities.  I'm making up the tables and fields, but I think you'll get the idea.  Then it depends on how your invoices are generated - when an activity is added to a new invoice, a trigger or usp will check to see if that Client.ID and Activity.ID are in this view.  If so, set the price to zero.

CREATE VIEW vwPaidOnce
AS
SELECT Invoices.ActivityID, Invoices.ClientID, Activities.ID, Activities.PaidOnce
FROM Invoices INNER JOIN Activities ON
Invoices.Activity = Activities.ID
WHERE (Activities.PaidOnce = 1)

Open in new window

0
Ken SelviaRetiredCommented:
Not sure this will help but it seems like your work flow can get to invalid or inconsistent states.

If a pay-once activity get printed, then changed to a pay-every type, you have a condition that would not have been allowed to occur had it started as pay-every type.    That is, they would have been required to pay if it was printed.

It sounds like "printed" should mean locked or unlocked or opened/closed in more common invoicing terms.

Your pay-once boolean is more like a Purchase Order number, or PayKey.   Maybe change it to an integer and keep a table with the next PO#.  Assign each new invoice a PO# if they have the option of paying-once.  Leave it as 0 for pay-immediate.

Any open invoice can be assigned a PO at any time.
An invoice can be closed at any time.
No changes can be made to closed invoices.

What if you NEED to change a closed PO invoice?  You don't.  You make a new invoice and assign the PO assigned to the prior invoice to it.
What if you NEED to change a closed pay-immediate invoice?  You don't.  It should already have been paid anyway.  Make a new invoice.

So the logic for transition from closed to open is not to allow it, but provide the users with a way to arrive at the same state by creating a new invoice for the new activity.



Not sure if that helps or just repeats back to you what you already wrote :)

Sometimes changing terminology helps business owners define their business rules better.
0

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.