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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|