Proper design of sales/fulfillment dbase

[ben]

Hello,

I need to make a good database for sales, and for fulfillment. I have a prototype, part of which consists of the following tables:

Quotes

Quote Line Items

Orders

Order Line Items

Invoices

Invoice Line Items

My plan is this:

1) Sales employees make the quotes, then turn quotes into orders

2) Accounting employees pick up the orders, verify payment, the turn orders into invoices

3) Fulfillment employees pick up the invoices, and ship them.

My question is this: do I really need three 'line items' file? Why not just make one line items file with a field for a Quote ID, an Order ID, and an Invoice ID. It seems that would allow the Quotes, Orders, and Invoices tables to view line items through portals? Is there any reason to do this one way or the other? Any input is greatly appreciated.

Thanks,

Ben

[Chuck]

quote:

---

Originally posted by ben:

Hello,

I need to make a good database for sales, and for fulfillment. I have a prototype, part of which consists of the following tables:

Quotes

Quote Line Items

Orders

Order Line Items

Invoices

Invoice Line Items

My plan is this:

1) Sales employees make the quotes, then turn quotes into orders

2) Accounting employees pick up the orders, verify payment, the turn orders into invoices

3) Fulfillment employees pick up the invoices, and ship them.

My question is this: do I really need three 'line items' file? Why not just make one line items file with a field for a Quote ID, an Order ID, and an Invoice ID. It seems that would allow the Quotes, Orders, and Invoices tables to view line items through portals? Is there any reason to do this one way or the other? Any input is greatly appreciated.

Thanks,

Ben

---

If Quotes turn into Orders which then turn into Invoices, I would just make one file for them (I'd probably name it Orders) and have a field which indicates the status of the order (quote, order or invoice). Given what you've said, I see no compelling reason to have six files instead of two. I don't even think you'd need separate IDs for the different status that a record can have. One ID field (Order ID) should be fine.

Chuck

[ben]

Thanks for the quick reply - My employers insist upon keeping invoice numbers sequential, with no gaps. That is why I wanted to have a seprate invoice file, so I can use an auto-serialnumber field for an invoice key. I could achieve this with scripting and using global fields, but it seemed more complicated and error prone.

-Ben

[Gleep]

Why don't you just make different layouts for different departments...that way they are still manipulating the same file?

That's what we do here. When we take an order from a "Professional", who needs an invoice, our manager just pushes the "invoice" button (simple go to layout button) and the information is organized to look like an invoice....but its still the same information.

I am however new at this but I agree that you do not need 6 files.

[Chuck]

quote:

---

Originally posted by ben:

Thanks for the quick reply - My employers insist upon keeping invoice numbers sequential, with no gaps. That is why I wanted to have a seprate invoice file, so I can use an auto-serialnumber field for an invoice key. I could achieve this with scripting and using global fields, but it seemed more complicated and error prone.

-Ben

---

If the primary reason you're looking to have separate files is because of the sequential nature of invoices, I don't think you need to go that route.

You're on the right track with global numbers to keep track of the last invoice number. The only caveat with this is that if the database is being served on the network, each guest gets their own global field. If user 1 places a value in the global field, user 2 never sees it.

The workaround for this is to use a separate file for the serial number. You may already have a file which can act in this capacity, such as a main menu file. All it needs is to have a single record and a constant relationship to your quotes/orders/invoices file. Then the single record in the main menu file can act like a global, being available to all records at all times.

Regarding your worry about this route being error prone, if users don't have the ability to move an order to an invoice without going through your scripts, then it shouldn't be a problem.

Chuck

[ben]

Thanks for all the advice, guys!