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
|
|||
|
|||
Relationship for Market at Customer-Product Level
I am creating a database that among others it has these tables:
Product (Primary Key ProductID, Product Description) Customer (Primary Key CustomerID, Customer Description) Forecast (Primary Key FcstKgID, CustomerID, ProductID, KG, Date) I want to be able to produce forecast at market level, the market is defined at customer-product level, my questions a 1. Should I create a table for Market wth the following fields: MarketID, Market Description, CustomerID, ProductID? 2. How should I create the relationship with this table and the FCST Table? Any advice is welcome. Thanks, |
#2
|
|||
|
|||
Relationship for Market at Customer-Product Level
On Thu, 30 Oct 2008 07:34:34 -0700, Mr.LuckyMe
wrote: I would create tblMarket (CustomerID PK, ProductID PK, other fields) Don't create too many autonumber PKs, especially not in junction tables that are not used in too many other tables. Is it really that much more efficient to use one integer than two? No. I don't understand what Forecast.KG is. Then I would argue that each market has a forecast for each date, so tblForecast would have the PK of tblMarket, and also DateOfForecast and ForecastAmount. Note: don't use "Date" as a field name because it is a reserved word. -Tom. Microsoft Access MVP I am creating a database that among others it has these tables: Product (Primary Key ProductID, Product Description) Customer (Primary Key CustomerID, Customer Description) Forecast (Primary Key FcstKgID, CustomerID, ProductID, KG, Date) I want to be able to produce forecast at market level, the market is defined at customer-product level, my questions a 1. Should I create a table for Market wth the following fields: MarketID, Market Description, CustomerID, ProductID? 2. How should I create the relationship with this table and the FCST Table? Any advice is welcome. Thanks, |
#3
|
|||
|
|||
Relationship for Market at Customer-Product Level
Tom,
Thank you for your answer. ForecastKg = ForecastQty Could you please elaborate on "don't create too many autonumbers". Currently all the autonumbers I have are coming from different tables and I created them as Primary Keys. The forecast input is monthly at customer-product level, from there you can easily make a summary at product or customer level, the issue here is that the market is based on the product-customer combination, for example, product A for Customer 1 could be for food market, and the same product for customer 2 is consider distributors market. Thanks, "Tom van Stiphout" wrote: On Thu, 30 Oct 2008 07:34:34 -0700, Mr.LuckyMe wrote: I would create tblMarket (CustomerID PK, ProductID PK, other fields) Don't create too many autonumber PKs, especially not in junction tables that are not used in too many other tables. Is it really that much more efficient to use one integer than two? No. I don't understand what Forecast.KG is. Then I would argue that each market has a forecast for each date, so tblForecast would have the PK of tblMarket, and also DateOfForecast and ForecastAmount. Note: don't use "Date" as a field name because it is a reserved word. -Tom. Microsoft Access MVP I am creating a database that among others it has these tables: Product (Primary Key ProductID, Product Description) Customer (Primary Key CustomerID, Customer Description) Forecast (Primary Key FcstKgID, CustomerID, ProductID, KG, Date) I want to be able to produce forecast at market level, the market is defined at customer-product level, my questions a 1. Should I create a table for Market wth the following fields: MarketID, Market Description, CustomerID, ProductID? 2. How should I create the relationship with this table and the FCST Table? Any advice is welcome. Thanks, |
#4
|
|||
|
|||
Relationship for Market at Customer-Product Level
On Fri, 31 Oct 2008 07:06:00 -0700, Mr.LuckyMe
wrote: Too many autonumbers: take the classic M:M relation of Orders and Products, via the junction table OrderDetails. The typical design has OrderDetails with: OrderID PK ProductID PK Too many autonumbers would be: OrderDetailID Auto OrderID ProductID (hopefully a unique index on OrderID+ProductID) That autonumber only makes some sense if it is used as a foreign key in several other tables. -Tom. Microsoft Access MVP Tom, Thank you for your answer. ForecastKg = ForecastQty Could you please elaborate on "don't create too many autonumbers". Currently all the autonumbers I have are coming from different tables and I created them as Primary Keys. The forecast input is monthly at customer-product level, from there you can easily make a summary at product or customer level, the issue here is that the market is based on the product-customer combination, for example, product A for Customer 1 could be for food market, and the same product for customer 2 is consider distributors market. Thanks, "Tom van Stiphout" wrote: On Thu, 30 Oct 2008 07:34:34 -0700, Mr.LuckyMe wrote: I would create tblMarket (CustomerID PK, ProductID PK, other fields) Don't create too many autonumber PKs, especially not in junction tables that are not used in too many other tables. Is it really that much more efficient to use one integer than two? No. I don't understand what Forecast.KG is. Then I would argue that each market has a forecast for each date, so tblForecast would have the PK of tblMarket, and also DateOfForecast and ForecastAmount. Note: don't use "Date" as a field name because it is a reserved word. -Tom. Microsoft Access MVP I am creating a database that among others it has these tables: Product (Primary Key ProductID, Product Description) Customer (Primary Key CustomerID, Customer Description) Forecast (Primary Key FcstKgID, CustomerID, ProductID, KG, Date) I want to be able to produce forecast at market level, the market is defined at customer-product level, my questions a 1. Should I create a table for Market wth the following fields: MarketID, Market Description, CustomerID, ProductID? 2. How should I create the relationship with this table and the FCST Table? Any advice is welcome. Thanks, |
#5
|
|||
|
|||
Relationship for Market at Customer-Product Level
Tom,
Thank you very much for your answer "Tom van Stiphout" wrote: On Fri, 31 Oct 2008 07:06:00 -0700, Mr.LuckyMe wrote: Too many autonumbers: take the classic M:M relation of Orders and Products, via the junction table OrderDetails. The typical design has OrderDetails with: OrderID PK ProductID PK Too many autonumbers would be: OrderDetailID Auto OrderID ProductID (hopefully a unique index on OrderID+ProductID) That autonumber only makes some sense if it is used as a foreign key in several other tables. -Tom. Microsoft Access MVP Tom, Thank you for your answer. ForecastKg = ForecastQty Could you please elaborate on "don't create too many autonumbers". Currently all the autonumbers I have are coming from different tables and I created them as Primary Keys. The forecast input is monthly at customer-product level, from there you can easily make a summary at product or customer level, the issue here is that the market is based on the product-customer combination, for example, product A for Customer 1 could be for food market, and the same product for customer 2 is consider distributors market. Thanks, "Tom van Stiphout" wrote: On Thu, 30 Oct 2008 07:34:34 -0700, Mr.LuckyMe wrote: I would create tblMarket (CustomerID PK, ProductID PK, other fields) Don't create too many autonumber PKs, especially not in junction tables that are not used in too many other tables. Is it really that much more efficient to use one integer than two? No. I don't understand what Forecast.KG is. Then I would argue that each market has a forecast for each date, so tblForecast would have the PK of tblMarket, and also DateOfForecast and ForecastAmount. Note: don't use "Date" as a field name because it is a reserved word. -Tom. Microsoft Access MVP I am creating a database that among others it has these tables: Product (Primary Key ProductID, Product Description) Customer (Primary Key CustomerID, Customer Description) Forecast (Primary Key FcstKgID, CustomerID, ProductID, KG, Date) I want to be able to produce forecast at market level, the market is defined at customer-product level, my questions a 1. Should I create a table for Market wth the following fields: MarketID, Market Description, CustomerID, ProductID? 2. How should I create the relationship with this table and the FCST Table? Any advice is welcome. Thanks, |
Thread Tools | |
Display Modes | |
|
|