A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DB Table Design Help



 
 
Thread Tools Display Modes
  #1  
Old September 17th, 2007, 12:29 PM posted to microsoft.public.access.tablesdbdesign
Jeff B[_3_]
external usenet poster
 
Posts: 5
Default DB Table Design Help

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.

  #2  
Old September 17th, 2007, 01:06 PM posted to microsoft.public.access.tablesdbdesign
Jason Lepack
external usenet poster
 
Posts: 600
Default DB Table Design Help

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.



  #3  
Old September 17th, 2007, 01:31 PM posted to microsoft.public.access.tablesdbdesign
Jeff B[_3_]
external usenet poster
 
Posts: 5
Default DB Table Design Help

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.




  #4  
Old September 17th, 2007, 02:02 PM posted to microsoft.public.access.tablesdbdesign
Jason Lepack
external usenet poster
 
Posts: 600
Default DB Table Design Help

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)
NoteProductsID and SuppliersID combine to be the primary key and
they are related to their associated fields in the parent tables.

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

Cheers,
Jason Lepack



On Sep 17, 8:31 am, "Jeff B" wrote:
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.- Hide quoted text -


- Show quoted text -



  #5  
Old September 17th, 2007, 02:04 PM posted to microsoft.public.access.tablesdbdesign
Jason Lepack
external usenet poster
 
Posts: 600
Default DB Table Design Help

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


On Sep 17, 8:31 am, "Jeff B" wrote:
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.- Hide quoted text -


- Show quoted text -



  #6  
Old September 17th, 2007, 09:25 PM posted to microsoft.public.access.tablesdbdesign
Amy Blankenship
external usenet poster
 
Posts: 539
Default DB Table Design Help


"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.


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.


  #7  
Old September 18th, 2007, 04:19 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default DB Table Design Help

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/acc...abaseDesign101
http://www.dbpd.com/vault/9805xtra.htm

hth


"Jeff B" wrote in message
...
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.



  #8  
Old September 18th, 2007, 02:03 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default DB Table Design Help

On Sep 17, 12:29 pm, "Jeff B" wrote:
I am in a class in college for database design. We
are discussing Normalization and 3rd and 4th normal form


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.

--


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.