Table Design Ideas

[Steven19]

Hi

I'm looking to build a new element to my database, but need some ideas how to put paper to programming.

Concept:

I have a back office to my database. The Manager can create a Rates Sheet and in that sheet he has the following Fields:

• TariffID - PK
• TariffName
• TariffDescription
• IsActive
• TariffType (Combo Box to group Tariff Types for a report)

The Manager can add new rate items turn old ones off if they aren't relevant anymore.

Where I'm Stuck:


From the customer side I have 150 customers I need to be able to go to a "Tariff / Rates Page" and Add a Tariff. So what will happen is that I get the Above information open on the customer page, and there is an extra field where we can add custom values to each tariffname. Each customers tariffs are different, but not the structure of the tariffs.

How would I bet go about relating these or putting this together?

[mike60smart]

Hi
So each Customer would only ever have 1 Tariff?

Can you give us some examples of a Customers specific Tariff?

[Steven19]

To not give away company info I have given examples only:

Customer 1: (Wants a small product designed)

Labelling Service: £5.00 per Item
Graphic Design Concept Art: £650 per Mock-up
Printing Costs: £20p per page


Customer 2: (Wants a medium product designed)

Labelling Service: £15.00 per Item
Graphic Design Concept Art: £1250 per Mock-up
Printing Costs: £1.40p per page

So the services are the same but the costs are different.

[mike60smart]

Hi

So the service you provide for All Customers will only ever have the 5 sections:-

Labelling Service
Cost per Item
Graphic Design Concept Art
Cost per Mock-Up
Printing Costs

[Steven19]

Actually we presently have 39 services, but the manager may wish to add more as we grow or reduce them based on our growth, but we will offer the same services to all customers, but one customer may pick just 5 depending on their need.

[mike60smart]

Can you give some examples of other Services?

[Welshgasman]

I would expect you to have a table for services.
A table to join customers to services with costs for each customer, if prices can be different per customer?

[Steven19]

Correct prices can be different for customers. If one customer wishes to have specific designs or graphics used or bespoke graphics, that can cost more than someone wishing to use standard graphics.

[Steven19]

Website Design
Database Programming
Photography
Flyers
Menus
Billboards
Posters
Stationary
Endorsements
Online Advertising
Radio Advertisements

[Steven19]

So I would have 1 record for the customer then a junction table, then the table for services with multiple items in it.

Without going into every record manually and adding all the services to a customer, how could I populate the junction table either using code on the form or a function so that my customer gets access to every service?

I've seen a video on you tube from Richard Rost from Computer Access Learning Zone who has sort of given me an idea in his Customer Pricing video, but he manually adds the customer to items in the actual junction datasheet using the Foreign Keys there, I don't want that combersom task.

[Welshgasman]

Well, you could group the items as jobs. So your first example would add those 3 entries?
Any special work would be added manually, or a specific task not required deleted?

[mike60smart]

Hi

This is a simple structure to cover what you need:-

tblCustomers
-CustomerID - Autonumber - PK
- Customername
-(Other fields to describe the Customer)

tblCustomerServices
-CustomerServiceID - Autonumber - PK
-CustomerID - Number - LongIntger - FK (Linked to PK from tblCustomers)
-ServiceID - Number - LongIntegr - FK (Linked to PK from tblServices)
-Qty
-ServiceCost

tblServices
-ServiceID - Autonumber PK
-Service (Contains the list of your 39 Services)

[Steven19]

Thank you.

I have that all set up.

Before I get into it, how do I populate the junction table?

I have 150 customers, and each will have access to the 39 options. That is 5850 records to add.

There must be a way to automate this. I can create a button on a form to run an append query to add the ServicesID into the tblCustomerServices every time we go to the customer record and look to add the rates. However how do I get the CustomerID into the tblCustomerServices 39 Times automatically?

Regards

[Micron]

how could I populate the junction table either using code on the form or a function so that my customer gets access to every service?

The junction table isn't for all the possibilities, it's for what actually took place. So if you want it to contain history it may or may not be possible to do it with queries - would depend on your current tables. If you only want the table to contain records going forward, then you will be creating records in forms as you go. The junction table needs to relate customer table and service table records for their job/order. If they request 3 services, the CustID will be there 3 times for the order (and so would the order ID if you have that in another table), once per service. Likely you'll need to have a price field in the junction table since there's no standard price for a service.

That being said, in the OP you stated

Each customers tariffs are different, but not the structure of the tariffs.

I don't think tariff and customer are related so don't try to make it so. Unless I'm not understanding the terminology here, a tariff is a job/service and you'd list those. If the same tariff name has different set rates, you need a record for each combination. If you determine the rate on the fly, then you don't necessarily need a record for each combination, but it will make it easier to re-use the information that's being created.

The rest of it seems like it's the classic customer table related to orders table related order details table. The order details table would contain the tariff and the rate, which is either established or added on the fly.