product purchases and activity payments (was "Help Needed")
I'm trying to design part of a database schema and want to know your thoughts on my two ideas.
I have a training system which allows operators to record shop "product" prurchases and "activitiy" payments. An example Activity is where someone has arranged to have some training, the activity has associated rate, instructors and discount depending on the purchase of a course. I see activities and products as different types of items. This is debatable...
A client (via the operator) can pay for a number of product purchases and activities as one transaction.
Option 1: The product and activity tables are versioned and a sales table (recording the action of a purchase) records foreign keys back to the product and activity at the time of purchase. Late on we can report of the sales as we have keys back to the original products and activities.
Option 2: On payment, a copy of the product(s) and activities is peristed in seperate "sales item" tables and linked to the sale table.
I think you need to explore the supertype/subtype design model. Your sales table will hold the general information like the date, customer, taxes, total amount etc., and the sale item will reference a item (supertype of a product or activity). The sale item will hold the quantity, description, final item price and the id to the item which holds common data and an ItemType (Product or Activity) attribute.