Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009

    Question 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.

    Please advise...

  2. #2
    Join Date
    Jun 2005
    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.

    See r937's link to a powerpoint presentation on supertypes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts