Need assistance in relationships!!

[maniaouri]

Hi all,

please note that I am trying to create a database regarding the inbound shipment management of a warehouse.I have the following tables that I need to link:
SUPPLIERS
ORDERS
PRODUCTS
INVOICES
CASES

Every supplier has many orders.One order has one or many Products.So far is easy beacause I link them with one to many relationship.The problem is that one case has one or many invoices and each invoice has one or many orders.

So there is a conflict because the primary key of orders should be linked twice.

Any suggestions?

please help.

thanx

 

[David Eagar]

I think a Union Table is the answer, but have only just discovered this concept and still getting to grips with how to apply them - try a search and see if it fits your requirement

 

[neileg]

I think a Union Table is the answer, but have only just discovered this concept and still getting to grips with how to apply them - try a search and see if it fits your requirement

Do you mean a junction table?

 

[maniaouri]

Do you mean a Union query.The problem is that when the user will be working he will enter first the orders and after when the order is ready and he receive the documents of the shipment he will connect the entered order with the case and the invoice.

is it more clear now?

 

[David Eagar]

Do you mean a junction table?

D'oh!! - yes, I did mean a Junction Table (apologies)

 

[neileg]

What is the real world relationship between orders, deliveries and invoices? If a delivery can cover many orders and an invoice can cover many orders, then this is pretty complex.

I'm not clear what you mean by

So there is a conflict because the primary key of orders should be linked twice.

 

[maniaouri]

First of all by saying:

So there is a conflict because the primary key of orders should be linked twice.

I mean that the primary key of the table ORDERS should be linked one time with the table PRODUCTS and a second time with the JUNCTION TABLE INVOICE-ORDER as you can see in the attached file.So from my little experience I have never seen someone doing that so I guess it is wrong.

Yes indeed a delivery has a lot of invoices each of them may have more than one order.That's why is so complex.

I will describe you how is being doing now.

1) We send the order to the supplier that's why I need to enter the order first
2) the supplier is sending order confirmation so i check the order.
3)the supplier is sending the invoices which include the order.

I am trying to do it with a many to many relationship between the invoice table and the orders table.But I don't know if this correct

Am I more clear?

 

[neileg]

First of all by saying:

So there is a conflict because the primary key of orders should be linked twice.

I mean that the primary key of the table ORDERS should be linked one time with the table PRODUCTS and a second time with the JUNCTION TABLE INVOICE-ORDER as you can see in the attached file.So from my little experience I have never seen someone doing that so I guess it is wrong.

No that seems fine. A table can be linked with many other tables or even with itself.

Yes indeed a delivery has a lot of invoices each of them may have more than one order.That's why is so complex.

I will describe you how is being doing now.

1) We send the order to the supplier that's why I need to enter the order first
2) the supplier is sending order confirmation so i check the order.
3)the supplier is sending the invoices which include the order.

I am trying to do it with a many to many relationship between the invoice table and the orders table.But I don't know if this correct

Am I more clear?

You haven't given all the information but here's a start. Assuming your orders contain more than one product, you will need to create a order header table and an order lines table. Assuming deliveries can cover more than one product and more than one order, you will also need a header table and a lines table and then the same with invoices.

The complexity comes when you compare deliveries with orders. You may find that you have 25 items of product A delivered. You have two orders for product A, one for 20 units and one for 10 units. How do you know which orders these deliveries relate to?

When you are invoice for the products, do the invoices relate to orders or deliveries?

 

[The_Doc_Man]

What you are describing is a "split delivery" situation. Where more than one order can be delivered at once - and parts of some orders can be delivered, staged over time.

Your problem is first that you need to identify HOW you would approach this if done on paper. This requires you to determine the company's rules on product acceptance and receipt. I.e. is a partial delivery usable?

There are a couple of viewpoints here. The answers to YOUR question depend on the answers to THESE questions:

1. Are you using multiple suppliers for the same products? Even once counts as "YES"

2. Is there a funding issue that causes you to wish to determine the order number to which an order item belongs if it comes from the same supplier?

3. Do you have an incremental payment agreement with your suppliers? (Obviously, part of #2 above, but not the whole story.) Or do you have to wait until an entire order comes in before you can pay anything?

4. Do you have to account for shrinkage (damaged deliveries) by supplier or by specific order number?

See, where I'm going is simple. Your BUSINESS RULES will tell you how to account for things on paper. Once you have the paper trail figured out, you can implement it. UNTIL you have the paper trail figured out, don't even start. You'll get nowhere.

One way to do this is a multi-threaded inventory where you total up ordered items and received items. Another is to determine whether the suppliers provide you with documents identifying the orders they thing they are satisfying with each delivery. I.e. let THEM tell you what orders are complete. (OK, double-check them by counting what was delivered...)

But no matter how you cut it, the question will always be "What does the BOSS want to see?" And we can't answer that for you. All we can do is advise of approaches and refer you to threads where this topic has been explored.

 

[maniaouri]

The chain is DELIVERY-INVOICE-ORDER.Please take a look at the attached file i put the option I am trying now.Please feel free to make any suggestions.i don't know if I am correct.

 

[The_Doc_Man]

If you don't know if you are correct, we have to hesitate ourselves. The key to this problem is that if YOU are going to implement it, YOU must understand it intimately so that you never have to offer the disclaimer.

As my site doesn't allow me to download from overseas sites, another forum member will have to look at the file for you.

 

[neileg]

You're linking deliveries to invoices. I would have expected deliveries to link to orders and invoices to link to orders. The reasoning here is that you need to ensure that you receive what is ordered and you pay for what has been ordered and delivered. However, as The Doc Man says, only you know what your business rules are in reality.

 

[maniaouri]

What you are describing is a "split delivery" situation. Where more than one order can be delivered at once - and parts of some orders can be delivered, staged over time.

Your problem is first that you need to identify HOW you would approach this if done on paper. This requires you to determine the company's rules on product acceptance and receipt. I.e. is a partial delivery usable?

There are a couple of viewpoints here. The answers to YOUR question depend on the answers to THESE questions:

1. Are you using multiple suppliers for the same products? Even once counts as "YES"

2. Is there a funding issue that causes you to wish to determine the order number to which an order item belongs if it comes from the same supplier?

3. Do you have an incremental payment agreement with your suppliers? (Obviously, part of #2 above, but not the whole story.) Or do you have to wait until an entire order comes in before you can pay anything?

4. Do you have to account for shrinkage (damaged deliveries) by supplier or by specific order number?

See, where I'm going is simple. Your BUSINESS RULES will tell you how to account for things on paper. Once you have the paper trail figured out, you can implement it. UNTIL you have the paper trail figured out, don't even start. You'll get nowhere.

One way to do this is a multi-threaded inventory where you total up ordered items and received items. Another is to determine whether the suppliers provide you with documents identifying the orders they thing they are satisfying with each delivery. I.e. let THEM tell you what orders are complete. (OK, double-check them by counting what was delivered...)

But no matter how you cut it, the question will always be "What does the BOSS want to see?" And we can't answer that for you. All we can do is advise of approaches and refer you to threads where this topic has been explored.

1. No each factory has each one products.
2. The problem is not the table with the products the problem is how I will have the relationships in the database in order to have the opportunity to add the orders (with products) and then to import them in the invoices in order to create the deliveries.
3. There is no incremental matter I pay each invoice seperately.
4. No I don't want to extend for now so far the database.

So in conclusion two questions:
1.Shall I have a problem if I the primary key of a table is linked with 2 child fields at the same time.
------------------------------------------------------------------------
quote

neileg:You're linking deliveries to invoices. I would have expected deliveries to link to orders and invoices to link to orders. The reasoning here is that you need to ensure that you receive what is ordered and you pay for what has been ordered and delivered. However, as The Doc Man says, only you know what your business rules are in reality.
unquote

yes there are 2 different ways to see this matter that's why is so complex for me either way is fine.Let's consider the option you have mentioned.Even in this case the problem is the same the table order will be linked with 2 child tables at the same time.

But because to have the products under the orders it is better the invoice to be up as I said invoices has most of the time partial orders.

Many thanks for your interest guys I looking forward to hear your point of view.

 

[neileg]

Even in this case the problem is the same the table order will be linked with 2 child tables at the same time.

That is not a problem - you can have as many links as you need.