Piano Shop Database

[tomself1]

Hey,



I'm trying to construct a fairly simple database for a piano shop.

Basically, they order the products from the manufacturers (e.g. Yamaha), and they are logged onto the database. Each order consists of any number of different products, but all from the same manufacturer. When the products are delivered, they are ticked off in the database and their serial numbers are entered. As not all products are delivered in one go, I've gone for individual records, rather than quantities. That way each individual product can be marked as delivered as necessary.

A customer can only purchase products in stock (query is used to find products that have been delivered but not sold). When a sale is made, the customers details are entered (or found if a previous customer). The various products they have purchased are also entered, including the serial numbers.

I've had a go at trying to make this, and I've attached what I've got. The problem I'm having is regarding serial numbers. I need the serial numbers to be assigned to a particular sale. I also need to find a way of marking the products as sold, once they've been assigned to an invoice.

Any help would be much appreciated!

[llkhoutx]

Transactions, Orders and OrderDetail relationship/design seems to be faulty.

Seems to me that you should have an inventory table, out of which comes orders.