How to structure some tables better?

[James Minns]

Hi I would like some advice on how to restructure the tables for some data -
I am tracking objects which are in bins in workshops.

Here are the tables which I have:
Workshop Table:
wsID
WorkshopName < Unique

BinTable:
binID
BinName

ObjectTable:
objID
ObjectName < Unique

LinkTable:
wsID
binID
objID


The result of my query is:
WorkshopName / BinName / ObjectName
A / GA / Screw_1
A / GA / Screw_2
A / GB / Nut_2
B / GA / Bolt_9
B / GM / Screw_12

Each workshop has to have a unique name, and the Unique property in the
Workshop Table enforces that.

How can I restructure the tables so that the database automatically
generates an error for duplicate bin names in a Workshop, but allows
duplicate Bin names if the workshop is not the same - I can't set BinName to
unique (example Bin GA exists in both workshop A and B, but I want to make
sure that the user doesn't generate 2 bins called "GA" in the same workshop)

The unique identifier would be the pair WorkshopName/Bin name, but I can't
see how to enforce that at table level.

Can Access do this?

Thanks for any help,
James

 

[mnature]

The result of my query is:

WorkshopName / BinName / ObjectName
A / GA / Screw_1
A / GA / Screw_2
A / GB / Nut_2
B / GA / Bolt_9
B / GM / Screw_12

The result of your query shows Workshop A having two bin entries that are
the same. Bin GA shows up twice in Workshop A.

If you can have more than one object in a bin, then you need to configure
your tables to allow for that. If you make both the wsID and binID the
primary keys in your Link table, then you will only be able to have one objID
for any combination of wsID and binID.

One way to have a unique combination of Workshops and Bins would be to have
a table of Workshop Bins:

tbl_WorkshopBins
WorkshopBinID
wsID (PK)
binID (PK)

You would then use another table to link your objects to a particular
workshop/bin combination in the WorkshopBins table.

tbl_LinkObjects
LinkObjectID (PK)
WorkshopBinID (FK)
objID (FK)

This method would keep your bins unique for a particular workshop, but allow
you to link any number of objects to a particular bin in a particular
workshop.

 

[James Minns]

The result of your query shows Workshop A having two bin entries that are
the same. Bin GA shows up twice in Workshop A.

If you can have more than one object in a bin, then you need to configure
your tables to allow for that. If you make both the wsID and binID the
primary keys in your Link table, then you will only be able to have one
objID
for any combination of wsID and binID.

[x]
The answer to my problem was, as you wrote, to make both wsID and binID
primary keys in the link table (I didn't know I could do that)
Thank you very much!

James