[Solved] Issues with linking two tables in Base

[MatthewSabre]

First of all, Greetings!

Second of all, apologies....I did try to utilise the search bar before coming here but I could not find the answer I was looking for.
I am a relative newbie to databases in general as well as OO. As part of a work project I have to create a database, now through luck I appear to have made the majority of it work, but I am having one issue which I cannot seem to fix.

Below are my relationships.

http://postimg.org/image/thix37x8n/

Now through these current relationships I have been able to make a form that allows me to view:

1) What Barrels are linked to a Supplier
2) What Drawings are linked to a Barrel
3) What Orders are linked to a Supplier

I am trying to create a table/relationship so I can make a form that allows me to search for a Barrel and bring up all the orders it has featured in.
On advice from a friend of mine I have been using an Autonumber for all of my primary keys.


Thank you
Matthew

[rudolfo]

Normally you would need to join the Barrels table with the Orders Table. But this cannot be done cleanly because you don't know if you should join it to Barrel_ID1 or Barrel_ID3 or Barrel_ID12. You would need something like

Code: Select all

 ON b.barrel_ID IN (o.Barrel_ID1, o.Barrel_ID2,...o.Barrel_ID12)

but this is not very efficient. Actually your table design does not follow the normalization rules. Repeated names like Barrel1, Barrel2, Barrel3, etc. as column names are always a sign that you need to split your single Order table into two tables:

Code: Select all

.--------------.               .------------.
|    ORDERS    |               |    ITEMS   |
+--------------+               +------------+
| Order_ID     |1 <---------> N| Order_ID   |
| Order_Number |               | Barrel_ID  |
| Order_Date   |               | Barrel_Qty |
| Order_Value  |               +------------'
| Order_Status |
| Order_Notes  |
'--------------'

And you should have then up to 12 (or 20, not all Barrel columns are visible) records with the same Order-ID:

Code: Select all

| Order_ID | Order_Number | Order_Date | Order_Value | Order_Status | Order_Notes |
|----------+--------------+------------+-------------+--------------+-------------|
|       22 | XX1234       | 2015-09-12 |       40.99 | delivered    | what ever   |

| Order_ID | Barrel_ID | Barrel_Qty |
|----------+-----------+------------|
|       22 |       102 |         24 |
|       22 |       112 |         44 |
|       22 |       105 |        100 |
|       22 |       142 |         20 |
|       22 |       155 |         30 |
|       22 |       101 |         40 |

Then you can join your Barrel records to the single column Barrel_ID in the ITEMS table and join the items to the Orders using the Order_ID as join condition.

Code: Select all

SELECT .... FROM Barrels b INNER JOIN Items i ON b.Barrel_ID = i.Barrel_ID
   INNER JOIN Orders o ON i.Order_ID = o.Order_ID
 WHERE b.Barrel_... = ...

In relational database theory the Items table is called a cross table because it establishes a N:M relation between the Orders and the Barrels.
Ideally you save the SELECT code above without the WHERE part as a query in Base. Then base your form on this query and the form will generate a Where condition on its own when you configure filtering for this form. For example you might set that filter to show only a certain single Barrel_ID.

[MatthewSabre]

Rudolfo.

First of all, apologies for the late reply, I have been away with work and just didnt get round to checking here.
It makes sense now and seems really simple, my only over question would be in the new Items Table which I am going to create, what should be the PK?

[update]
I tried to make the items table but I came across the issue, I made the Order ID as the PK but it wont let me have duplicate values because it is the PK...Help!

[MatthewSabre]

Problem Fixed.

Thank you for Assistance.

Thread can be closed now.