Database..Inventry system

[mrapoc]

Hello

I need to make it so that a customer can have as many orders as they want throughout time, but each order needs to be able to hold as many products as they require.

Here is my ERD

View attachment ERD.doc

I am unsure which entries to put under product2order and what to link to

Any help is appreciated and reppage

 

[Freak]

so you have product, customer and order.

an order can have many products

a customer can have many orders

product (m-1) order (1-m) customer

Thats what I would have. I dont see why you have the entity product to order. Though it has been a while since I normalised a database without looking at it.

Talk it through in this post. I solved things by explaining it to someone else. Either you get it or they are able to get it.

edit:

it looks as though you ought to break down product details further.

 

[prosser13]

The way I did it was:

Sale ID - this is each individual stock sale (i.e. 2 Kit Kats)

Transaction - this is each transcation, which can have multiple sales (i.e. 2 Kit Kats - Sale ID 10, 3 Galaxys - Sale ID 11)

Customer ID

 

[mrapoc]

Sale id being a autonumber primary key?

Transaction linking to a order number?

Customer ID linking to..customer number

 

[prosser13]

Have a form for adding customers details, with the Customer ID as the primary key (auto number) in table Customers.

Then have a table for Sales, with Sale ID as an autonumber primary key and the product details.

Then you need a middle table for table Transactions, with the field Transcation ID, the Customer ID and the Sale ID.

In the form, the Sale ID is filled in (n + 1), the Customer ID is entered (I made a link to a search field) and the Transaction ID is entered, then the product details are entered

Obviously you need other forms such as table Stock, etc

 

[mrapoc]

i think i have the tables etc. setup now

What i want to do is add products to a list box under each customer order or something

then i can start worrying about calculating stock etc. but for now i want to sort the ordering

anybody have ideas on what i physically need to carry this out