Table Design & Relationships

[daherb]

Hi Guys, thought i would pick your brains to try and help me figure out how to build this database. Any help would be very much appreciated as this one is making my head spin.

Brief::

I need to create a database that will hold information about our suppliers and the products that they stock and make.

One supplier can have a number of contacts that we speak to.
The supplier produces a number of different products.
Each of the suppliers stock certain items in certain sizes and weights and make the remainder of their stock to order, so each supplier has a number of different supply options. So in addition to the multiple supply options their are multiple sizes & weights, as well as multiple qualities.

dont know if this helps people to understand buit the products we are talking about is paper (sheets or paper & reels of paper)

example.

Joe BLoggs (Supplier) has 3 sales members (Louise, Maggie, Sue) this supplier holds some sheets and some reels in stock (Stock Sheets, Stock Reels) but they also make sheets of any size and weight between a certain range as well as reels (Bespoke Reels, Bespoke Sheets). These sheets and reels can be of different qualities (gloss, silk, offset). These different qualities can have a number of different grades (Manhattan, Triumph, Charter) and will be available in a number of different weights (70gsm, 115gsm, 150gsm).

The idea behind creating the database is to allow the sales team to query the database to find a supplier that can match the customers criteria.

I have created the following database tables

tblMainDetails
AccNo (PK)
SupplierName
Address1
Address2
Address3
Address4
PostCode
Tel
Fax
Web
Email

tblContacts
ContactID (PK)
FirstName
LastName
Tel
Email

tblStockingOptions
OptionID (PK)
BespokeSheets
BespokeReels
StockSheets
StockReels

TblQualities
QualityID (PK)
PaperQuality

tblWeights
WeightID (PK)
Gsm

tblPaperInfo
PaperID (PK)
SupplierPaperName
OurName
PaperInfo

Do these tables look aabout right???? Now comes the bit thats making my head spin Relations, can someone please give me some advice on this.

 

[daherb]

Looking at this further as i have been doing for a while now i have noticed that the supply option table is incorrect and would not work as i need to record the sheet sizes and reel sizes. - i am going to re-organise the table strutcure and post it back up in a second.

 

[daherb]

So in addition to the tables list minus tblstockingoption

tblStockSheets
SheetID (PK)
Width
Length

tblStockReels
StockReelID (PK)
Width

tblBespokeSheets
BespokeSheetID (PK)
Width
Length

tblBespokeReels
BespokeRellID (PK)
Width

 

[Pat Hartman]

The contacts table needs to contain the AcctNo which will be a foreign key.

You need only one table to define the supplier's products:

tblProduct
ProductID (autonumber pk)
StockType (stock sheets, stock reels, bespoke sheets, bespoke reels)
LowWeight
HighWeight
LowSize
HighSize
Quality (gloss, silk, offset)
Grade (Manhattan, Triumph, Charter)
ActualWeight (70gsm, 115gsm, 150gsm).