Table help

[Christi Lee]

I am going to ask this of everyone. I am building tables and relationships
for a company. This is the information. We have a product which has 3-5
other charges that go along with it depending if the charge is a cost to our
company or price to customer. Each vendor is the same. Is this particular
charge a cost to there company or a cost to the customer. The product is
also priced per mile and each vendor has a different price.
Do I need a table for each vendor with product cost, price, per miles, and
every other charge on it with there price? I am trying to condense this
into the easiest format and I am stuck. Thank you for your help

 

[Tom van Stiphout]

On Tue, 24 Feb 2009 11:24:08 -0800, Christi Lee

I'm going to rewrite your question. Is this what you mean?

I'm working with Products.
Each Product's price is made up of 3-5 components.
Each component is either a cost to the customer, or to our company,
and whether is't the one or the other is the same for all vendors.
One of the components may be a Price per mile (different per vendor).

Can you confirm the above is correct? Also give us some real-world
examples so it isn't too abstract.

-Tom.
Microsoft Access MVP

 

[Christi Lee]

Yes, that is better.
I have 5 vendors
1 product
8 different charges.
each vendor has a different price for each product

one of the charges is a mileage charge
each vendor shows. ex. 0-9 miles $6.42 per mile
10-25 miles $5.40 per mile
26-50 miles $5.90 per mile
my question is do I need to make a table for each vendor with their charges
seperately?
should I make combo box's?

 

[John W. Vinson]

Yes, that is better.
I have 5 vendors
1 product
8 different charges.
each vendor has a different price for each product

one of the charges is a mileage charge
each vendor shows. ex. 0-9 miles $6.42 per mile
10-25 miles $5.40 per mile
26-50 miles $5.90 per mile
my question is do I need to make a table for each vendor with their charges
seperately?

NO.

You need *one* table with fields for VendorID, LowMile, HighMile, and Charge.

should I make combo box's?

A combo box is a *data display tool*. It is not a storage medium. Get your
table structures right first, before you start worrying about how to display
and manipulate that data!

 

[Tom van Stiphout]

On Wed, 25 Feb 2009 10:18:13 -0800, Christi Lee

Curious. You first say "1 product", then "different price for each
product". That seems inconsistent. Precise language is important when
we talk about requirements and database design.

You CERTAINLY should not make one table for each vendor. That violates
important relational database design rules.

It *appears* that you may need several tables:
Products: ProductID (PK), ProductName, ...
Charges: ChargeID (PK), ChargeName
One of those charges would be Mileage Charge 0-9, another Mileage
Charge 10-25, etc.
In your previous post you seemed to indicate that some charges are not
passed on to the customer; that flag should probably go in this table
as well.
ChargesPerProduct: ChargeID (PK), ProductID (PK)
This is the junction table for the many-to-many (M:M) relation between
Products and Charges: each product has multiple charges, and each
charge occurs for multiple products.
Vendors: VendorID (PK), VendorName, ...
VendorPricing: VendorID (PK), ProductID (PK), ChargeID (PK), Price

(PK): This field is part of the Primary Key of this table.

-Tom.
Microsoft Access MVP