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
|
|||
|
|||
Designing tables
I want to design a database for a business that
manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John |
#2
|
|||
|
|||
John,
It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John |
#3
|
|||
|
|||
Ooops! The table ids should be Product ID in Size table, product ID in Price
table! not the way I stated. "Damon Heron" wrote in message ... John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John |
#4
|
|||
|
|||
The hits just keep comin'.... Only two tables are required, the product
table and the size table. Add a price field to the size table. On a form, you can have the product, with a subform for various sizes and price of each size... Dumon "Damon Heron" wrote in message ... Ooops! The table ids should be Product ID in Size table, product ID in Price table! not the way I stated. "Damon Heron" wrote in message ... John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John |
#5
|
|||
|
|||
Thanks for that Damon, yes your correct a better way to
look at this is to assume that each variable size is another product. I am still unsure as to how this table design you have proposed for me will relate the size of the product (window) to the price? Considering the client would like the system to up date the price automatically. This design could work but wouldn't the user have to have an external pricing scheme? or have I missed something thanks agian John -----Original Message----- John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John . |
#6
|
|||
|
|||
My posts were rather confusing because the first was done without thinking
about the product. My final table design would be like this (only two tables): Product table ProductID ProductName Description Size Table SizeID Height Width ProductID Price Establish a relationship in the rel. window - one product to many sizes. Design a form with Products as the record source and on it a subform for sizes - master/child relation is productID. Now as to your question, "the client would like the system to up date the price automatically" - are you talking about a standard across the board price change for all product -for instance, a 10% price increase? If so, that could be done with a query. If you are talking about price changes on specific products, that also could be a parameter query, but someone, sometime is going to have to input the prices for all products to start with. I don't have enough info about the product pricing to tell you how I would do it... Set up my tables and forms and play with that idea to get you started. Damon wrote in message ... Thanks for that Damon, yes your correct a better way to look at this is to assume that each variable size is another product. I am still unsure as to how this table design you have proposed for me will relate the size of the product (window) to the price? Considering the client would like the system to up date the price automatically. This design could work but wouldn't the user have to have an external pricing scheme? or have I missed something thanks agian John -----Original Message----- John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John . |
#7
|
|||
|
|||
Yes I believe that this design is probably the most
straight forward and avoids me getting into a muddle, it still means that for every product style I will have to manually input every height and width size and its corresponding price. Although I thought there was some merit in have a table with all possible sizes (height and width) and giving each one there own ID. Tell me what you think of this Product_Table ProductID ProductName Description Size_Table SizeID Height Width Price_Table Price ID Price I could then have a fourth table made up of the other three Product_Link Table Porduct ID size ID Price ID Then my client would be able to add new products from a form. or does this all sound too much, its was you first message that gave me the idea so please don't laugh out too loud! Thanks john -----Original Message----- My posts were rather confusing because the first was done without thinking about the product. My final table design would be like this (only two tables): Product table ProductID ProductName Description Size Table SizeID Height Width ProductID Price Establish a relationship in the rel. window - one product to many sizes. Design a form with Products as the record source and on it a subform for sizes - master/child relation is productID. Now as to your question, "the client would like the system to up date the price automatically" - are you talking about a standard across the board price change for all product -for instance, a 10% price increase? If so, that could be done with a query. If you are talking about price changes on specific products, that also could be a parameter query, but someone, sometime is going to have to input the prices for all products to start with. I don't have enough info about the product pricing to tell you how I would do it... Set up my tables and forms and play with that idea to get you started. Damon wrote in message ... Thanks for that Damon, yes your correct a better way to look at this is to assume that each variable size is another product. I am still unsure as to how this table design you have proposed for me will relate the size of the product (window) to the price? Considering the client would like the system to up date the price automatically. This design could work but wouldn't the user have to have an external pricing scheme? or have I missed something thanks agian John -----Original Message----- John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John . . |
#8
|
|||
|
|||
Yes I believe that this design is probably the most
straight forward and avoids me getting into a muddle, it still means that for every product style I will have to manually input every height and width size and its corresponding price. Although I thought there was some merit in have a table with all possible sizes (height and width) and giving each one there own ID. Tell me what you think of this Product_Table ProductID ProductName Description Size_Table SizeID Height Width Price_Table Price ID Price I could then have a fourth table made up of the other three Product_Link Table Porduct ID size ID Price ID Then my client would be able to add new products from a form. or does this all sound too much, its was you first message that gave me the idea so please don't laugh out too loud! -----Original Message----- My posts were rather confusing because the first was done without thinking about the product. My final table design would be like this (only two tables): Product table ProductID ProductName Description Size Table SizeID Height Width ProductID Price Establish a relationship in the rel. window - one product to many sizes. Design a form with Products as the record source and on it a subform for sizes - master/child relation is productID. Now as to your question, "the client would like the system to up date the price automatically" - are you talking about a standard across the board price change for all product -for instance, a 10% price increase? If so, that could be done with a query. If you are talking about price changes on specific products, that also could be a parameter query, but someone, sometime is going to have to input the prices for all products to start with. I don't have enough info about the product pricing to tell you how I would do it... Set up my tables and forms and play with that idea to get you started. Damon wrote in message ... Thanks for that Damon, yes your correct a better way to look at this is to assume that each variable size is another product. I am still unsure as to how this table design you have proposed for me will relate the size of the product (window) to the price? Considering the client would like the system to up date the price automatically. This design could work but wouldn't the user have to have an external pricing scheme? or have I missed something thanks agian John -----Original Message----- John, It seems to me that a window, say 30 x 40, and another window, 30 x 60, are really two different products. But maybe the first solution is a unique id for each product in a table like this: Product ID Product Name Description SizeID PriceID and another table with all possible sizes: SizeID Ht Width and a third table with price info: PriceID Price So one product can have many sizes (one-to-many) and one product can have many prices.... HTH Damon "John G" wrote in message ... I want to design a database for a business that manufactures UPVC windows and doors. My initial problem is that each product (especially the windows)can be made in various set sizes with set prices dependent on height and width. I know creating a separate table for each product is ridiculous, epecially when products will also most certainly be added or removed and the prices may change over any period of time. A more reasonable solution is to create one table with each product's height and width matched with it's price, but this also seems a bit much when lets say you there's 100 products with 100 different set sizes and costs. I can accept that my thinking is probaly niave as I'm fairly new to database design, so any ideas on how to approach the problem would be appriciated. thanks John . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
searching for "join tables" and "join word tables" | Uncle Bill | Tables | 1 | June 11th, 2004 09:33 PM |
Limit to Number of Tables | T'Kai | Database Design | 1 | June 8th, 2004 03:44 PM |
How do I design a database based on the information that will be stored? - Copy of Tables and hirearchies.zip (0/1) | Jim | Database Design | 1 | June 1st, 2004 01:44 PM |
Separate database for tables? | Holly Clifton | Database Design | 3 | May 18th, 2004 06:20 PM |