Design of a sale and purchase database?

Answer 1

I shall give you a solution to what i think it is, if i am wrong, post back with further queries.

First of all what tables you need depend entirley on what information your company is interested in retaining. I will assume all and you can delete as nesseccary

First of all there should be a person and a product table:

PERSON (person_ID*, name, start_date, end_date)

PRODUCT (product_ID*, name, description, price, colour, weight, size, stock, start_date, end_date) //add more fields as nesseccary

if you want to keep track of your staff make a staff person table:

STAFF_PERSON (sp_ID*, staff_ID, person_ID, start_date, end_date) //assuming a staff table exists already

Then you are going to want a customer table:

CUSTOMER (customer_ID*, person_ID, name, start_date)

Then tables to connect customer to person and customer to product:

CUSTOMER_PERSON (cp_ID*, customer_ID, person_ID, start_date, end_date) //this is to keep track of your customers names and other details that might be in the person table

PRODUCT_CUSTOMER (pc_ID*, customer_ID, product_ID, staff_ID, start_date, end_date) //this is to keep track of which products have been sold to which customers. (I put the staff_id in this in case you need to know who sold it for commision)

This is only a very simple solution, but I think it will give you the gist of what needs to be done. If you need more help with, or I didnt answer your question then just repost here and i wil get back to you.