Hi Jason,

Thanks for the response. I see your point on the Warehouse table, even
though I assigned a Primary Key auto field that would not prevent
duplicates. I had not thought of that I thought that because I had a
Primary Key that each record would be unique I can see where I was wrong on
that Thank you.
I still do not really understand though how that works in the products
detail table? How does putting those two different fields in ensure that
you get the one record? I mean you have many different WarehouseNumbers and
many different WarehouseNames I do not see how they are referencing to the
same record?

I am still not sure how that address my OnHandQty issue that if I have lets
say 5 RedPencil in Warehouse1 and 10 RedPencil in Warehouse2 how does that
come out in the products detail table? or do I still need another table?

Thanks,

Jeff B.

"Jason Lepack" wrote in message
ups.com...
Herein, lies the issue of the multi-field key:

Warehouse (WarehouseID-PK-, WarehouseNumber, WarehouseName)

I assume that you are using MS Access (because that's where I find
this post).

When creating tables one must think about what physical attributes
uniquely identify the real world object.

In your example you have chosen a WarehouseID, I suspect that for that
you are going to use an Autonumber. That's fine. An Autonumber will
cluster quite nicely on the disk drive, and will avoid alot of page
splits.

However, it doesn't ensure that your data matches the real world.
There is nothing to prevent you from inserting:
WarehouseID, WarehouseNumber, WarehouseName
1, 1, Jason
2, 1, Jason

In alternative you can create an index on (WarehouseNumber,
WarehouseName) that will be unique, or you cna create a multi-field
primary key.

Using a multi-field primary key your ProductsDetail would be as such.
ProductsDetail (ProductDetailID-PK-, ProductsID-FK,WarehouseNumber,
WarehouseName)

You would join it to the products table on both fields.

Cheers,
Jason Lepack



On Sep 17, 7:29 am, "Jeff B" wrote:
Hi All,

I am wondering if someone here could help me and hopefully help me get
somewhat un-confused. I am in a class in college for database design.
We
are discussing Normalization and 3rd and 4th normal form, This has really
confused me in setting up tables. I have not dealt with tables that use
more than one field as a Primary Key, as in combining 2 fields that are
foriegn keys to a couple of different tables to make the Primary Key for
said table? Is there an easy way to think of this that might make sense?
Also the otherr thisng that has me very very stumped which goes along
with
the first question is when you have items that are Many-to-Many
Relations.
Example being:

A company has more than one warehouse that are identified but a name and
number. Products could be stored in any warehouse. So I know that this
is
a Many-to-Many Relationship.

The company has many suppliers that it could get any given product from.
So
I know that this is a Many-to-Many Relationship

So setting up the table design for this I understand I would have, I
think,
either 4 or 5 tables:

Warehouse (WarehouseID-PK-, WarehouseNumber, WarehouseName)
Suppliers (SuppliersID-PK-, SupplierName)
Products (ProductsID-PK_, Description, Class, Price, SuppliersID-FK-)

I am pretty sure those are needed and correct except for maybe the
suppliersId Foriegn Key in the Products Table

This is where I am very confused in setting up the tables for the
Many-to-Many Relationships, would I need just one more table that would
incorporate both the many suppliers and many Warehouses or does this need
to
be broke up?

ProductsDetail (ProductDetailID-PK-, ProductsID-FK, WarehouseID-FK,
SuppliersID-FK-, OnHandQty)

Thank you for the help, I am sure there has to be an easy way to
think/remember this and that I am probably just making it way more
difficult
than it really is.

Jeff B.