Help with Database Design

[wazawak]

This is my first time working with Access, and I'm finding it a little more difficult than I expected. I’m not sure what the best way is to organize these tables or even how many tables I want to make. I work for a company that sells goods to retailers, and this database is to keep track of special promotions (like 10% off for Father’s Day, for example).

I’m thinking of creating 3 tables- one “Customer” (ie, Wal-Mart, Meijer, etc), one “Promotion” (including event name, start and end dates, estimated cost, etc), and one for “Product” (including product line and model, retail price, promotional price, etc). However, the price of each product and the specifics of each promotion vary customer by customer- a promotion that lasts a week with Meijer might last two weeks with Wal-Mart. Is there any way I can organize this database so that my promotional and product data can change with each customer?

Any help you guys can offer will be much appreciated… I’ve been banging my head against this problem for a full day now and I’m getting sick of it.

 

[georgedwilkinson]

Yes, there is a way. You need more tables.

Please look up "many to many", junction table, and associative table on google. It will take you a long way. When you're done, post back and we'll give you specific help that you should then be able to understand.

 

[dz2k7]

think what is what.

What is a customer? - name, address, phone number, representative and stuff

What the product is - product line, product code or model and a list price (no promotion prices).

What a promotion is:
It's a combination of event name, customer and product.
So put in you table event name, date start, date end, customer, product and a promotional price for that event for that product for that customer.


If your promotions go by product lines and discount % of List price - then it's even better.
You put in the last table event name, date start, date end, customer, product line and a discount %.

Then you make a query or report, connect tables and figure out your promotional prices for all products of the product line for any customer and any event based on the list price from the product table and a discount % from promotion table.

Take it easy

 

[wazawak]

Got it! I think this project is really starting to come together now. Thanks very much for the help.