Relationship for Market at Customer-Product Level

[Mr.LuckyMe]

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 are:

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,

 

[Tom van Stiphout]

On Thu, 30 Oct 2008 07:34:34 -0700, Mr.LuckyMe

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

 

[Mr.LuckyMe]

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]

On Fri, 31 Oct 2008 07:06:00 -0700, Mr.LuckyMe

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

 

[Mr.LuckyMe]

Tom,

Thank you very much for your answer

On Fri, 31 Oct 2008 07:06:00 -0700, Mr.LuckyMe

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