Advanced Data Structure Question

Hey All,

I have a question regarding database structure in relation to some large price lists my users will use.

The price lists are from different suppliers and some clients may use the same pricelists.
The clients using the same pricelists may have different prices.

For Example we may have Big W, Walmart and Cosco all with pricelists 6000 items long
Steves Toys and Bobs Gadgets both use walmart as a supplier however Steve buys more so his pricing is cheaper on some items.

Both Steve and Bob get there prices in the way of a CSV file.
In the CSV file is has the product number, category, sub category, price, RRP, name and description

What is the better way to structure this.
Do I have an item “Product” and each user has their own 6000 long pricelist
List of “Product”
Item No
Vendor = Sam


should I have 1 in total of each pricelist and then attach a Item to “Product” with the user pricing details

List of “Product”
Item No
List of Vendors

Vendor Name = Sam
Price = Sams price

These materials will be displayed and searched in a repeating group by the user.

My thinking is
In scenario 1 it may take more time to search as i could have 100’s of thousands of products to search through, however bubble indexing may make this bearable.

In Scenario 2 I may only have 20,000 products to search through however I will still need a fast way to access the particular vendors price of which there still may be 100’s of thousands

Anyone want to weigh in

Scenario 3 - not necessarily better for lookups, but something to consider. A product is really a different thing than its price/vendor/supplier combination

(fields including supplier? or can the same product be from different suppliers?)

Vendor (is that the same as client?)
Vendor id
Vendor name

Vendor Product Price
Vendor id
Product id
Supplier id
Date range (prices change)


Yes there would definately be a supplier field

Vendor is indeed client

What would be the best way to refer to the clients price.
Adding them as children will mean they are loaded any time the parent product is loaded. Slowing things down for all users.

I was thinking possibly have the product as a parent in the price object and not have it as a child then

do a search for “price” where vendor = this vendor and parent “product” = this product

However the I’m doing many searches

Do you mean this?

Price table, fields:
Parent Product - field type: Product

When doing a search for Price, all matching Products will also be loaded to the page, sounds like it makes sense for your page if you are going to reference the Product fields as well, or have already loaded them.

Will your page have a focus on one Vendor? Will it use a paging RG to browse the 6000 matching product/price entries?

I think this has promise, as it would seem the most likely access route is … “I want this thing” … “what are the cheapest options…” … “who sells it at that price”.