S
Sebastian
Hello,
I'm setting up a database so that I can manage the "installed base" for a
customer.
The customer provides: (Raw data via ftp)
There are over 30 columns on the file, but the relevant ones are:
Country Code: Code for the country where the equipment is located.
City: Name of the city where the equipment is located.
Material Description: Description of the equipment installed.
The contract provides:
Pricing for each "material description" item.
Coverage for each "material description" item. This can vary from country
to country (Item A will be covered in Mexico, but it will no be covered in
Argentina).
zone information for each city. This is to determine what the response time
for service is (depending on the distance from countrie's main office).
I need:
A way of identifying each "material description" on the raw data file and
assigning it its respective information:
Coverage -> Whether the product is under the maintenance contract or not.
Pricing
Zone
From here I'll be able to output the reports that I need to send to billing
and the different help desks so that they can provide support in the
different countries.
Here's what I have so far:
tbl_detail (raw data from customer - linked to remote folder)
30 columns (the relevant ones are ctry_code, city, material description)
tbl_countries
ctry_code -> Primary key
ctry_name
tbl_price
product_ID -> Primary key
price
tbl_zones
ctry_code --> (Index of ctry_code&city = primary key)
city
zone
And here is where I'm stuck... I need a table that will allow me to
determine whether each product is under coverage or not. The issue that I
have is that there are 47 countries and 130 products, and any product can be
under coverage for all/some/none of the countries.
Am I stuck with having a table like this?
tbl_coverage
ctry_code
material_description
coverage
The problem with this one is that I would have to have 47 rows of data for
each of the products on the list (one for each country_code).
Is there a better way of setting this up?
Thanks,
Sebastian
I'm setting up a database so that I can manage the "installed base" for a
customer.
The customer provides: (Raw data via ftp)
There are over 30 columns on the file, but the relevant ones are:
Country Code: Code for the country where the equipment is located.
City: Name of the city where the equipment is located.
Material Description: Description of the equipment installed.
The contract provides:
Pricing for each "material description" item.
Coverage for each "material description" item. This can vary from country
to country (Item A will be covered in Mexico, but it will no be covered in
Argentina).
zone information for each city. This is to determine what the response time
for service is (depending on the distance from countrie's main office).
I need:
A way of identifying each "material description" on the raw data file and
assigning it its respective information:
Coverage -> Whether the product is under the maintenance contract or not.
Pricing
Zone
From here I'll be able to output the reports that I need to send to billing
and the different help desks so that they can provide support in the
different countries.
Here's what I have so far:
tbl_detail (raw data from customer - linked to remote folder)
30 columns (the relevant ones are ctry_code, city, material description)
tbl_countries
ctry_code -> Primary key
ctry_name
tbl_price
product_ID -> Primary key
price
tbl_zones
ctry_code --> (Index of ctry_code&city = primary key)
city
zone
And here is where I'm stuck... I need a table that will allow me to
determine whether each product is under coverage or not. The issue that I
have is that there are 47 countries and 130 products, and any product can be
under coverage for all/some/none of the countries.
Am I stuck with having a table like this?
tbl_coverage
ctry_code
material_description
coverage
The problem with this one is that I would have to have 47 rows of data for
each of the products on the list (one for each country_code).
Is there a better way of setting this up?
Thanks,
Sebastian