setting relation ships in my tables

[locutusofborg]

Hi
Im new around here but need a bit of help with organising a few tables in my database.
I am gonna develop a small visual basic application to work with an access database
for work. The basic premise is as follows:
We have bins and boxes that contain a type of material this material has a name like
E386 or E299 or E321 etc ( this is the type of polymer that has been made on site)
We now have to store this material in a box or bin. Each box or bin will have a unique
identifier like BOX22 or BIN14 , there will be 500 bins and boxes.
And there will be a location where the bin is stored ie plant 5 storage bay or plant 2
storage bay.

So I have set up the following 3 tables to define these 3 areas

Master material file ( to hold all material made by company )
Material_code - unique code for material
Material_description - description of material
Material_UOM - unit of measure for material
Material_quarantine _ whether material should be quarantined when in container

Master Location file ( to hold all locations on site )
Location_code - uniquecode for a location
Location_Type - Location type , ie internal , external
Location_description - description of location
Location_dedicated - indicates whether a location is dedicated to a material

Master Container file
Container_Code - unique code for container
Container_type - ie box or bin
Container_description - description of container

I have these tables set up at the moment , my next stage is to define a key for them
I presume this will be the unique number in all of them , is this correct?

Now this is my next stage , i need to set up these tables as in the project definition

Container contents table
Container_code - unique container code ,cross reference with master container table
Container_Material - material in container ,cross reference with master material table
container_quantity - quantity of material in container
Container_Contents_datetime - date and time material was put in container
Container_Contents_personel - Personel code of person who updated the container contents

Container location table
Location_code - unique location code ,cross reference with master location table
Container_Location - location of container ,cross reference with master location table
Container_Location_datetime - date and time a container moved to a location

My problem is really setting up the relationships between the tables. Mostly the last two tables

container contents and
container location file
and what are the keys to these tables ?

Can anyone help as how to best set these relation ships and give me some advise as I am new
to this database malarky

 

[johnnymac43]

First, some assumptions:
> A container will hold only one type of material at any given time.
> At times, a container may not hold any material.
> An empty container will have a location.
> At times, a type of material may not be in any container.
> At times, a location may not have any containers.
> A history of container contents will be maintained.
> A history of container location will be maintained.
> A container may be moved from one location to another with no change of contents.
> The contents of a container may change without a change of location.
> New containers, materials and locations may be added to the database in the future.

Based on the above:
> Master container has an optional 1:M relationship with container contents on container code.
> Master material has an optional 1:M relationship with container contents on material code.
> Master location has an optional 1:M relationship with container location on location code.

To tie it all together you need to put container code in container location and establish a mandatory 1:M relationship between master container and container location.

Unique primary keys for your working tables will be:
> Container code and/or material code plus datetime for container contents.
> Container code and/or location code plus datetime for container location.

HTH

John