1-to-1 relationship based on two fields

[gmworrall]

A 1-to-1 relationship based on two fields. Hear me out...

tbl_Products has a one-to-many relationship with tbl_Stock. Each product can have many suppliers.

tbl_Stock has a one-to-one relationship with an external feed (which I have no control over), containing cost, quantity etc. There are no individually unique fields in the external feed, but SupplierSKU and SupplierID together are always unique.

So I want to relate tbl_Stock to the external feed using two fields.

End result is ONE product, with MANY suppliers, each with their own cost and quantity.

Tried a few things with no success, and finally admitted I'm out of my depth on this particular thing. What's the best way to make it work?

 

[plog]

Can you provide sample data? List each table's fields and some sample data, then the end result of what you want to end up with based on that sample data.

 

[gmworrall]

Sure! Basically need to join Stock and StockFEED into one set of data, but can't guarantee a unique field. But a combination of two (SKU and SupplierID) WILL be unique.

Products
PID Title
1 ProductA
2 ProductB

Stock
SID PID SKU SupplierID
A 1 SKU1 SupplierX
B 1 SKU1 SupplierY
C 2 SKU2 SupplierX

StockFEED (external source - can't change this)
SKU SupplierID Cost
SKU1 SupplierX 10.00
SKU1 SupplierY 20.00
SKU2 SupplierX 15.00

Desired result
PID Title SID SKU Supplier ID Cost
1 ProductA A SKU1 SupplierX 10.00
1 ProductA B SKU1 SupplierY 20.00
2 ProductB C SKU2 SupplierX 15.00

 

[Pat Hartman]

In relational databases it is best to not mimic your flat file structures. In the real world you have one product and many suppliers and potentially many stock locations. You would then use a query to "flatten" the structure so it looks like your spreadsheet. This gives you 4tables. The fourth table is a junction table which is where you keep track of your stock by location, supplier, and product.

tblProduct:
ProductID
Desc
etc.
tblSupplier:
SupplierID
CompName
etc.
tblLocation:
LocationID
LocDesc
etc.
tblInventory:
ProductID
SupplierID
LocationID
quantity
etc.

 

[gmworrall]

Pat, the problem is the inventory is from an external CSV file, which doesn't have OUR ProductID. So I have:

tblPRODUCTS ----1-2-M---- tblINVENTORY ----1-2-1---- CSV (external)

tblINVENTORY needs to relate to the external CSV to pull in quantity, cost etc. But the problem is, only a combination of two fields is unique.

 

[plog]

Overall, I think you're structure's fine. The only thing that I see is that SID in the Stock table should probably be an autonumber instead of text. As for bringing all your data together like you want, I think this SQL will do it:

SELECT Products.PID, Products.Title, Stock.SID, Stock.SKU, Stock.SupplierID, StockFEED.Cost
FROM (Products INNER JOIN Stock ON Products.PID = Stock.PID) INNER JOIN StockFEED ON (Stock.SupplierID = StockFEED.SupplierID) AND (Stock.SKU = StockFEED.SKU);

There's nothing wrong with have tables join using 2 fields instead of one, and that's all I did.

 

[gmworrall]

Thanks Plog! You're right.

I guess I'd been looking at it so long that I needed someone to tell me that it's nothing complicated, for me to realise it was something simple (data type mismatch)....

 

[Pat Hartman]

If you need to maintain a product ID and/or name that you can use to join to an import spreadsheet, that will add another layer to the schema. It was technically always there but we had no data for it so I omitted it originally for simplification. Here is the revised schema:
tblProduct:
ProductID (autonumber PK)
Desc
etc.
tblSupplier:
SupplierID (autonumber PK)
CompName
etc.
tblSupplierProduct:
SupplierProductID (autonumber PK)
SupplierID (FK) (unique index fld1)
ProductID (FK) (unique index fld2)
ProductCode
ProductName
etc.
tblLocation:
LocationID (autonumber PK)
LocDesc
etc.
tblInventory:
InventoryID (autonumber PK)
SupplierProductID (FK) (unique index fld1)
LocationID (FK) (unique index fld2)
quantity
etc.

You can use the product identification in tblSupplierProduct to join to the spreadsheet.

I added the unique indexes to enforce business rules since the PKs of those tables will not prevent logically duplicate entries but the unique index will. You could, for those two tables, use a compound PK and eschew the unique index and autonumber but you will find over time that working with single field primary keys is much easier. So, even though I actually prefer "natural" keys, I use autonumbers because they simplify my life.