Can anyone help with this simple DB table structure?

[tjp]

I really suck at table design and coding, and access. I'm a techie that usually stays away from all this development stuff; it makes my head hurt. I'm a lot happier knee deep in an Exchange migration than developing even the most basic of databases as I suck at it (think I mentioned that already) However, i've been roped into doing a tiny little project for a friend. Below is the process I went through for the data / table design. When I started to create the relationships it got messy with the many to many relationships. Am I over complicating it or am I on the wrong track? Any assistance from you super-clever developer dudes would be greatly appreciated and would affirm your superiority over us humble techies yet again.

I started with the raw data:

Supplier (e.g. Builder's Supplies Co.)
Item (e.g. Plywood, Sharp Concreting Sand)
SupplierItemCode (e.g. WWE3428X)
Unit (25KG, 9x2440x1220mm)
Cost

Here are the main relationships:

A Supplier can have many Items, SupplierItemCodes and Costs
An Item can have many Suppliers
An Item can have many Units (e.g. Sharp Concreting Sand comes in many different weights (Units))
A Unit can have many Items (e.g. the unit of 25Kg can have several different types of sand or other items associated with it)

So what I did was to create four tables (* - primary key, ( ) foreign keys):

I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).

Supplier Table
-------------
*SupplierID
Supplier
(ItemID)

Item Table
----------
*ItemID
Item
(SupplierID)
(UnitID)

Unit Table
----------
*UnitID
Unit
(ItemID)

SupplierItemandCost Table
-------------------------
*SupplierItemID
SupplierItemCode
SupplierItemCost
(ItemID)
(SupplierID)

 

[Kraj]

I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).

No, it would not be a bad practice. In fact, it's exactly what you should do.

If I'm reading your post correctly (and it's possible I misunderstand), I think what's causing you problems is your foreign keys. When you use a foreign key in one table, you do not have to add that table's key into the first one. What I mean is, if you have three tables (1, 2, and 3) and Table 3 contains primary keys for Tables 1 and 2, those three tables will be connected and you do not have to have Primary Key 2 in Table 1 or Primary Key 1 in Table 2. So your final tables should look more like this:

Supplier Table
-------------
*SupplierID
Supplier

Item Table
----------
*ItemID
Item

Unit Table
----------
*UnitID
Unit

SupplierItemandCost Table
-------------------------
*SupplierItemID
SupplierItemCode
SupplierItemCost
(ItemID)
(UnitID)
(SupplierID)

This would mean that for every combination of Supplier, Item and Unit, there is a code and a cost.

I'm sure this setup is not exactly what you need, but it ought to get you on the right track.

 

[tjp]

Thank you very much for taking the time. I was getting there but you've clarified that very nicely thank you. Damn I wish I had slept in every SSADM class at college 14 years ago. Muchly 'preciated.