what relationship to use

[Stefanovski]

Hi all,

I have following problem so please help if you can.
I have three tables: Order Demands (from production), Order Demand Details and Orders (for placing by different suppliers).

ORDER DEMANDS
Order Demand No.(ID)
Date

ORDER DEMAND DETAILS
ID
Product
Quantity
Order Demand No.
Finished (checkbox)

ORDERS
Order No. (ID)
Date
Product
Quantity

Relationship between Order Demands and Order Demand Details is one-to-many. I have no relationship between Order Demands and Orders but it should be actually many-to-many relationship.

Following must be possible:
Order demands with specific details come from the production. It can be one item or several items (products) that are demanded. I must be able to place an order for one or more items (products) which are unfinished (not ordered till now) by selecting items. I must also be able to place an order independently from Order Demands for example because not all demands come from production department. After I have placed an order and the ordered products were supplied then I must be able to enter received products (maybe I do not get all ordered items) into Orders form for instance and this should check the field "Finished" in Order Demand Details table so that I know that I have received the products that are demanded from production department.

Do you have any suggestion how to organise relationships between tables.
Thanks for your assistance.

 

[KKilfoil]

Let's break your problem down a bit.

You are hinting that you need to keep track of inventory, or at least a pseudo-inventory, in order to properly track and connect orders, demands, fills, etc.

So the first thing that I would do is search these forums for inventory control threads, as there is a lot of related stuff there.

You are going to have to know how many of each ID you have on hand at all times to check your order demands against to know when you can fill them, etc.

I must also be able to place an order independently from Order Demands for example because not all demands come from production department.​

You need to set up a field to identify your 'client', maybe as a foreign key to
another table. Production is simply a special kind of client for your database.