Invoicing database design
in [Tables]
|
From: gorsoft on 4 Nov 2009 08:45 I am designing a database that includes the simple (!) functionality of producing invoices (not an accounting package). I understand the convention is to have a table for invoice headers and one for invoice transactions. My question is what are the advantages of this approach rather than just having one table for invoice transactions. The printed invoice (an Access report) can have the invoice headings (date, invoice no customer address etc) in the report header and the invoice transactions in the report detail. What would be wrong with that? Gordon
From: Klatuu on 4 Nov 2009 09:21 "What would be wrong with that?" Almost everything. The common design of a header and transactions in separate but related tables is always the correct way to accomplish this. If you had one table to hold it all, how many individual transactions could such a table hold? Well, with a limit of 256 fields in a table, you will run out very quickly. And if you are saying to repeat the header info for each transaction, then you are breaking the redundant data rule. And in the report, where will the header info come from? Or maybe I am missing the point and this post is a joke. <gorsoft(a)hotmail.com> wrote in message news:1ad2a0d4-d748-48bd-a9e3-b8a603f00d9f(a)b2g2000yqi.googlegroups.com... >I am designing a database that includes the simple (!) functionality > of producing invoices (not an accounting package). I understand the > convention is to have a table for invoice headers and one for invoice > transactions. My question is what are the advantages of this approach > rather than just having one table for invoice transactions. The > printed invoice (an Access report) can have the invoice headings > (date, invoice no customer address etc) in the report header and the > invoice transactions in the report detail. What would be wrong with > that? > > Gordon
From: gorsoft on 4 Nov 2009 10:23 On 4 Nov, 14:21, "Klatuu" <dahar...(a)verizon.net> wrote: > "What would be wrong with that?" > Almost everything. > The common design of a header and transactions in separate but related > tables is always the correct way to accomplish this. > > If you had one table to hold it all, how many individual transactions could > such a table hold? Well, with a limit of 256 fields in a table, you will > run out very quickly. And if you are saying to repeat the header info for > each transaction, then you are breaking the redundant data rule. > > And in the report, where will the header info come from? > > Or maybe I am missing the point and this post is a joke.<gors...(a)hotmail.com> wrote in message > > news:1ad2a0d4-d748-48bd-a9e3-b8a603f00d9f(a)b2g2000yqi.googlegroups.com... > > > > >I am designing a database that includes the simple (!) functionality > > of producing invoices (not an accounting package). I understand the > > convention is to have a table for invoice headers and one for invoice > > transactions. My question is what are the advantages of this approach > > rather than just having one table for invoice transactions. The > > printed invoice (an Access report) can have the invoice headings > > (date, invoice no customer address etc) in the report header and the > > invoice transactions in the report detail. What would be wrong with > > that? > > > Gordon- Hide quoted text - > > - Show quoted text - Wow, I feel as though I have been flamed. This is no joke. It was only a question on which I wanted advice. I said the invoicing function was simple, not the database itself. Fields in the one table invoicing transactions I envisage would be: invoice date invoice no customerID transaction date transaction ref productID item description Quantity Unit price As I see it, only the first 3 would break the redundant data rule. The data for the invoice report (including the header) would be built on the fly (using VBA) at time of invoice creation, drawing data not just from the invoice transaction table but also the related tables on customers/products etc. But thanks for responding - at least you confirmed the only objection to a one table design for invoice transactions is the data redundancy rule which in this particular case is not a great overhead if it saves having to maintain and update 2 tables.
From: BruceM via AccessMonster.com on 4 Nov 2009 11:01 Had I been asked I would have recommended against adding the last sentence to the previous posting, but the other points are valid. My understanding of your proposed structure is different from Klatuu's, but in either case there are literally no arguments in favor of your approach in a relational database. It would make sense only in a flat database such as Excel. When a flat database is called for Excel is generally the better tool for the job, but a flat databse is not called for here. Are you planning to copy the invoice number and other invoice header information to each invoice item record? If so, is the only reason that you believe it would be less overhead than maintaining two separate tables? There are any number of objections to such an approach, one of which is that overhead is likely to be greater, not less. You would not throw all of your paper files into a large crate in the belief that a single container is less overhead than a series of file drawers. The analogy is imprecise, but still relevant. Among the problems I see is that you do not have a ready way to view a single invoice's information. One way would be to scroll through all of the records until you notice the number you want, then take note of when the number changes as you continue to scroll through the records. Another way would be to filter the recordset (or load a selective recordset) to view a single invoice. With a filtered recordset you would need to run the filter again to view the next batch of records. With invoice header information in a single table you could simply navigate to the next invoice to see all of that invoice's associated line items. The flat file approach will inevitably lead to a more awkward user interface. Another objection would be the possibility of data entry errors. You could avoid that to some extent by using code to copy the record, but that involves overhead. Another is that you cannot enforce unique invoice numbers at the table level. You could use code to check whether an invoice number has been used for a different customer on a different date or something, but again that is a lot of overhead, and probably difficult to construct. "Only" three fields breaking the redundant data rule is three more than I would accept. There is sometimes room for debate about what constitutes redundant data, but not in this case. Adding a related table is easy, and does not involve "maintenance" after it is created. Coding for a flat structure is where the overhead really starts to add up. gorsoft(a)hotmail.com wrote: >> "What would be wrong with that?" >> Almost everything. >[quoted text clipped - 25 lines] >> >> - Show quoted text - > >Wow, I feel as though I have been flamed. This is no joke. It was >only a question on which I wanted advice. I said the invoicing >function was simple, not the database itself. > >Fields in the one table invoicing transactions I envisage would be: > >invoice date >invoice no >customerID >transaction date >transaction ref >productID >item description >Quantity >Unit price > >As I see it, only the first 3 would break the redundant data rule. >The data for the invoice report (including the header) would be built >on the fly (using VBA) at time of invoice creation, drawing data not >just from the invoice transaction table but also the related tables on >customers/products etc. > >But thanks for responding - at least you confirmed the only objection >to a one table design for invoice transactions is the data redundancy >rule which in this particular case is not a great overhead if it saves >having to maintain and update 2 tables. -- Message posted via http://www.accessmonster.com
From: gorsoft on 5 Nov 2009 07:27
On 4 Nov, 16:01, "BruceM via AccessMonster.com" <u54429(a)uwe> wrote: > Had I been asked I would have recommended against adding the last sentence to > the previous posting, but the other points are valid. My understanding of > your proposed structure is different from Klatuu's, but in either case there > are literally no arguments in favor of your approach in a relational database. > It would make sense only in a flat database such as Excel. When a flat > database is called for Excel is generally the better tool for the job, but a > flat databse is not called for here. > > Are you planning to copy the invoice number and other invoice header > information to each invoice item record? If so, is the only reason that you > believe it would be less overhead than maintaining two separate tables? > > There are any number of objections to such an approach, one of which is that > overhead is likely to be greater, not less. You would not throw all of your > paper files into a large crate in the belief that a single container is less > overhead than a series of file drawers. The analogy is imprecise, but still > relevant. > > Among the problems I see is that you do not have a ready way to view a single > invoice's information. One way would be to scroll through all of the records > until you notice the number you want, then take note of when the number > changes as you continue to scroll through the records. Another way would be > to filter the recordset (or load a selective recordset) to view a single > invoice. With a filtered recordset you would need to run the filter again to > view the next batch of records. With invoice header information in a single > table you could simply navigate to the next invoice to see all of that > invoice's associated line items. The flat file approach will inevitably lead > to a more awkward user interface. > > Another objection would be the possibility of data entry errors. You could > avoid that to some extent by using code to copy the record, but that involves > overhead. Another is that you cannot enforce unique invoice numbers at the > table level. You could use code to check whether an invoice number has been > used for a different customer on a different date or something, but again > that is a lot of overhead, and probably difficult to construct. > > "Only" three fields breaking the redundant data rule is three more than I > would accept. There is sometimes room for debate about what constitutes > redundant data, but not in this case. Adding a related table is easy, and > does not involve "maintenance" after it is created. Coding for a flat > structure is where the overhead really starts to add up. > > > > > > gors...(a)hotmail.com wrote: > >> "What would be wrong with that?" > >> Almost everything. > >[quoted text clipped - 25 lines] > > >> - Show quoted text - > > >Wow, I feel as though I have been flamed. This is no joke. It was > >only a question on which I wanted advice. I said the invoicing > >function was simple, not the database itself. > > >Fields in the one table invoicing transactions I envisage would be: > > >invoice date > >invoice no > >customerID > >transaction date > >transaction ref > >productID > >item description > >Quantity > >Unit price > > >As I see it, only the first 3 would break the redundant data rule. > >The data for the invoice report (including the header) would be built > >on the fly (using VBA) at time of invoice creation, drawing data not > >just from the invoice transaction table but also the related tables on > >customers/products etc. > > >But thanks for responding - at least you confirmed the only objection > >to a one table design for invoice transactions is the data redundancy > >rule which in this particular case is not a great overhead if it saves > >having to maintain and update 2 tables. > > -- > Message posted viahttp://www.accessmonster.com- Hide quoted text - > > - Show quoted text - Hi Bruce, Thanks for your detailed post. You make a much more convincing argument which has won me over. Part of my reticence to construct an invoice header table and a separate one for invoice item lines lay in my ignorance of the approach I should take. I did say I was not designing an accounting system here but just the ability to print invoices. Can you give me some advice? I understand what fields should be in the 2 tables but am unclear how to populate them. The nature of the business is a service based one - charges are made to a range of clients for each enquiry handled. These are captured through a basic data entry screen, allocated to a client and populate an enquiries transaction table. What is the best approach then to populate the invoice header and invoice lines tables? Gordon |