DB Table Design Help

  • Thread starter Thread starter Jeff B
  • Start date Start date
J

Jeff B

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.
 
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
 
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.
 
Using the eample of your ProductsDetail table:
ProductsDetail (ProductDetailID-PK-, ProductsID-FK, WarehouseID-FK,
SupplierID)

I suggest that you leave the supplier out of this table.

When I develop database structures I look at them this way:

I have Items.
I have Warehouses.
I have Suppliers.
Many Items are stored in a warehouse
Warehouses have many items.
Suppliers supply many items.
Items have many suppliers.

So that leads me to your original three tables.
Warehouse (WarehouseID-PK-, WarehouseNumber, WarehouseName)
Suppliers (SuppliersID-PK-, SupplierName)
Products (ProductsID-PK_, Description, Class, Price)

Note that the supplier id doesn't belong in the products table because
many suppliers can supply an item.

Then we have the two juntioning tables:

SupplierProducts(ProductsID, SuppliersID, other info about this
supplier and this item)
<Note>ProductsID and SuppliersID combine to be the primary key and
they are related to their associated fields in the parent tables.

WarehouseProducts(WarehouseID, ProductsID, Qty)
<Note>WarehouseID, ProductsId combine to be the primary key and they
are related to their associated fields in the parent tables.

Cheers,
Jason Lepack
 
If my last post doesn't help then email me at jlepack AT gmail DT com
and I'll forward you an example that I've put together this afternoon.

Cheers,
Jason Lepack
 
Jason Lepack said:
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.
Click to expand...

Just keep in mind that if you create a multi-field primary key, you have to
reference all of those fields in other tables where you are using a Foreign
key to point back to this table, which somewhat violates normalization
(since you're storing relevant data in more than one place) and is unwieldy
and takes up more space than necessary. Of course, you could then go back
to having an autonumber that you then use as a "shorthand" reference to the
record but is not actually defined as the primary key. In that case, the
difference between whether it is or is not used as the primary key is for
the most part academic.
 
don't be discouraged. the principles of relational design are probably the
hardest concept to master for most people - but it's also most important,
when you're looking to work with relational data, so it's well worth the
effort. suggest you read up on the topic from as many sources as you can;
when the same ideas are presented in different ways, often one way strikes a
chord better than others. here's some additional reading for your leisure
hours <g> :

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
http://www.dbpd.com/vault/9805xtra.htm

hth
 
I am in a class in college for database design. We
are discussing Normalization and 3rd and 4th normal form
Click to expand...

Bear in mind those are only of interest to academics :)

In practice, BCNF is what 3NF should have been but someone messed up.
4NF is always achievable but most often is not desirable; most designs
head straight on through to 5NF.

Jamie.

--
 
Back
Top