Database design

[SASHA_D]

Hi Everyone in this lovely forum!......


I've been given a task to design a pricing/products database and I am looking for some assistance...
The purpose of the database is basically to let the user view the best prices of thousands of products from different suppliers.

The basic structure at the moment is that I have a table (Virtual products) that will remain fairly static-this lists all the possible products that are available. It contains product ID, product description, plus many other fields like product class, type, strength etc. This table does not contain a supplier reference or ID. There is then a second table called Actual products-this contains the actual products that we are using-it contains similar info to the first table, however for each virtual product there are many actual products by different suppliers. The description of the products varies by supplier, so I need the description and other data from the first table and then the supplier ID from the second table.
I then have around seven or eight tables which are imported each month from excel spreadsheets which we recevie from the suppliers-these give us their prices that they are offering on all the different products.
Basically what I need the database to do is ultimately let the user view prices currently offered on different products and ideally have a history of the prices, which would then let the user see how the prices offered have changed over time.

I know this is a bit hazy at the moment, but I am unsure how to get started with this-Has anyone had experience of designing a similar database?


Thanks in advance,

Sasha

 

[Ziggy1]

Start a new query and bring in both tables. I'm going to assume if I understood correctly that the product code in both tables are the same, if not please clarify.


now click on the product field from the list for the first table and drag a join to the other product (table list)

If another line already exists between the 2 lists then delete it,you can have more than one but it's best to start like this.

Place your fields into the grid below from both tables

This query when run will return all the matching records in both tables.

You can modify the join by right clicking on it (exactly on line) and selecting join properties here you can choose which table to be the primary table thereby returning all from one and only those that match from the other.

not sure if this is what you need but I thought i'd give you something to work with.


Ziggy

 

[SASHA_D]

Thanks for the reply Ziggy!

Sorry, I wasn't very clear in the first post, to clarify...
I have what I call a 'virtual products' table-this lists all the products with a kind of 'generic' product description.
I then have a second table which contains all the actual products we use. One 'virtual' product has many different actual products from different manufacturers. There is no common 'ID' between the two tables, however linking on three fields at once-'Prodcut Family', 'Packaging Type' and 'Strength', will match the right 'virtual' products with the actual products.
Further to this I then have 7 or 8 price lists each month from different suppliers.
I need to be able to develop a system that will let me view products using the 'generic' product descriptions-and then bring back all the different prices offered for a particular product from the different suppliers price lists. Problem being is that there is no ID or field that links the 'Generic' table with the suppliers price lists-that's why I need the 'Actual' products list-this has a 'Product ID' field which links to the price lists. This product ID is unique to the product and supplier, so the same 'Virtual' product could have many different Product ID's , depending on how many suppliers there are for the product.

Is this making any sense?

Thanks Again,

Sasha

 

[Ziggy1]

First I think you would want to get all the vendor price lists into one table. You could do this by making a table with the required fields and then append (query) the 8 spreadsheets into one table.

Then you will have a master supplier table, make sure each vendor has it's unique identifier (supplierID).

You said you can link to the actual products table, so do this in a query (as I indicated in my first post).

Now take that query and make another query and link it to the virtual table.

You indicated that the 3 fields:
'Prodcut Family', 'Packaging Type' and 'Strength' will give you the match so do this.



It's hard to visualize your design exactly, but hopefully you'll get something out of my response.

Ziggy