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
Name
Category
Price
RRP
Vendor = Sam
etc…
or
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
Name
Category
Price
RRP
List of Vendors
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
Scenario 3 - not necessarily better for lookups, but something to consider. A product is really a different thing than its price/vendor/supplier combination
Product
(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
Price
Date range (prices change)
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
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”.
