setting the relationships of my tables help required

[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

 

[79ssecca]

Great start you have taken the time to think out the problem and define you tables, most people believe it or not seam to forget this step.

Using a unique number as the unique id for each of the first three table will work and there is nothing wrong with that at all. In fact it is normally the method I would choose. Some people, however like using a code system instead this aids in the reading of data sometimes later on. your choice. One down side of codes is if you ever feel the need to change a code later on it can be a problem.

The latter two tables are what I call linking tables. They pull together and gather pertinent data regarding the actual material in question. Unless the Master tables are going to be huge you won't really see any performance gain by building a relationship between them and the linking tables.

I have to wonder of your question might really be how to get the unique id from the Master table into the linking tables more than a relationship issue. If this is the issue then a simple form using combo boxes or list boxes will resolve your issue.

Post back if more detail is needed or wanted.