Request Purchase Order Database Design
I am building a new business application for my personal business which has close to ~100 transactions of sale and purchase per day. I am thinking of having Separate tables to record the sale and purchase with another linked table for Items that were sold and a seperate linked table with items that were purchased.
would this design be better or would it be more efficient to have one transactiontable with a column stating sale or purchase?-From an App/Database/Query/Reporting Perspective
izzymo
izzymo2 Answers
Microsoft Access Basic Business Purchase Order Database Template Microsoft Access Expert. How to Make a Database - Part 1 - Tables - Duration. Create Purchase Order. Jun 29, 2015 - Most often, vendors would require a purchase requisition (PR) form. Logical database design translates the conceptual data model into a.
An invoice is not the same as a sales order. An invoice is a request for payment. A sales order is an agreement to sell products to a party at a price on a date.
A sales order is almost exactly the same as a purchase order, except you are the customer, and a sales order line item can reference a purchase order line item. You can put them in separate tables, but you should probably use Table Inheritance (CTI, extending from an abstract Order). Putting them in the same table with a 'type' column is called Single Table Inheritance and is nice and simple.
Don't store totals in your operational db. You can put them in your analytic db though (warehouse).
Purchase Order Table Design
You are starting small, thats a quick way to do. But, I am sure, very shortly you will run into differences between sale and purchase transactions, some fields will describe only a sale and some fields that will be applicable only for purchases.
In due course, you may want to keep track of modifications or a modification audit. Then you start having multiple rows for the same transaction with fields indicating obsoletion or you have to move history records to another table.
Also, consider the code-style-overhead in all your queries, you got to mention the Transaction Type as sale or purchase for simple queries.
It would be better to design your database with a model that maps business reality closest. At the highest level, everything may abstract to a 'transaction', with date, amount and some kind of tag to indicate amount is paid or received against what context. That shouldn't mean we can have a table with Tag, Date, Amount, PayOrReceive to handle all the diverse transactions.
Whirl MindWhirl Mind