Price Book website

Guy says, "I need a max 232" so he looks throught the catagories in our database and finds that there is no entry under "electronic, componet, active, level converters"

So he puts "MAX232" in a part number field and hits Go. We go out and rip all the distributors looking for matches on that part number (just like findchips.com)

After he sees the one he wants, he selects it and then we have to rip the price and qty breaks and put them in our database (more than findchips.com).

So... next time, a guy comes along and puts in max232 and we show our entry from last time. Then he can just take that, or ask for an updated search.

I'm hopeing that other small distributors will put in thier own products...Especially people from the PICList like this guy Ashly Rolls, he will add his version of the rs232 - ttl converter which is bilt into a db9 backshell. When someone is looing for a way to convert rs232 to ttl, his thing will come up beside the MAX232...

Also, he can link to the search engine from his web site and tell it to display only his products.

The point is that Ash and other people will put in their prices for different products, maybe even for some other mfgrs product like MAX232 chips. I want to eventually include shipping costs when somebody compares prices so that people who have a stock of parts next door will come out ahead.

Sales

As long as we are telling people to buy something from one place or another, why not help them place the order? We can provide one or two nice services here. If someone wants to place an order for multiple items, we can look at the likely shipping cost and figure out if it is better to order from one store or more than one store. I already have a spread sheet for each of the kits I sell that knows about minimum orders, shipping costs and quantity discounts for each part, then it figures out the absolute best set of orders, from one, or many different distributors, for a given number of kits I want to put together. At least for the major distributors, that sort of information could be kept up to date with out much work.

And the little guys would put in thier info in order to compete with the bigs. So if Ash wants to sell RS232 converters and someone else also sells RS232 converters but also sells power supplies and you happen to need both a power supply and an RS232 converter, even if the other guys price on the RS232 converter was a bit higher, and he was a little farther away, we might still recommend you order from him just because the shipping can be combined.

Another thing we can do is provide a shopping cart for the small guy. As long as he is listing his product with us, why shouldn't we collect all the info needed for the order (address, tax, preferred shipping method, etc.. and then xfer all that data to paypal for payment to the shopkeepers account? Just like the price book listing above, Ash could link from his web site and we would help with "would you like fries with that" type sales.

In fact, that sort of thing can be done with the bigs as well. It is entirely possible to have a "shopping cart" that is dumped to digikey. See: Digi-Key Fast Add For the "Free ICD"

Inventory

Now, Ash doesn't always have these units available. He has other things going on and can't always have a batch ready to go. So it would be cool if he could just put in how many he has available, and each time someone is refered to him from the site, we decrement that inventory. When he is out, we don't send people his way anymore. He will keep his inventory up to date if he wants us to reffer people to him.

If we are already tracking mulitple stores and where a single item can be found at one or more different stores, it doesn't take any more work to manage your local inventory. All you have to do is "pretend" to be a store and when you make a purchase, we transfer that part from thier store to your store. Your store is really just you local inventory, and it wouldn't be publically listed, but if we know you ordered it, why not keep track of it for you?

I have a few local friends who I know stock inventory of parts all the time. It makes no sense for me to order 50 capacitors (to get a good price) from digikey when Kelly, just 4 blocks over, probably has them and would sell them to me for the same price especially if he new that he could pick up some part that I happen to have when he is out and it is holding up a board he needs to get out. The best way to implement that sort of an "inventory sharing" system is to have everyone publish their inventory and providing a good inventory tracking service for free on a web server is the best way I can see to get that going. As it is, I call Kelly and waste his time, or he calls me and wastes my time asking if we have some little part.

The site could also help individuals keep their inventory counts correct by requesting periodic recount of popular parts. This take minimal time, could be made a part of the requirement for joining the site, and does an amazingly good job of correcting inventory issues over time.

Technology can also help, such as UPC labeling / reading, Laser line cross bin indication for faster inventory access / picking, and so on.

UPC items

We also have 160,000 UPC codes and product names from common grocery store items. So I want to eventually help people find the store in their area with the lowest price on a given item based on data that other people input about what that item cost them at the store they went to. Everybody inputs the data when they come back from the store and that helps them next time to find the best price... depending on how many stores they are willing to visit.

Mfgr makes a product and assigns it a part number or UPC.

Distributor sets a price to sell at.

The trick is finding out which distributor has the lowest price for the qty you want and what the shipping cost will be.

Database Schema

We have a database schema set up:

Notice there are no fields for items that

A) may not exist. For example, there is no field for "Mobile Phone" in the Contacts table because the person may not have a cell... if we need one, it gets added in to Tags, with an entry in COA via the para.asp?t=5&s=2 page.

B) will probably have more than one. Price for example, there is always more than one price. MSRP, wholesale, street, purchase, etc...

This database does a good job of setting up and tracking categories of parts and what attributes they need. E.g. All electronic components (passive or active) have a footprint. Passives like capacitors have a type, rating(volts) and value(farads). All digital components belong to a logic family (TTL, LS, CMOS, ...). You can't have space in a flat file database for every possible attribute for every possible component; a hex inverter doesn't need a farad value. So there is a table with a hierarchy of types, which I call "COA" or "TagTree"

- Name: May describe an item (capacitor, resistor, etc...) a category of items (active, passive, linear, etc...) or an attribute (footprint, logic family, value in farads, value in ohms, etc...)

- isA: Points to other records in the same table which are higher in the higherarchy. E.g. #35 "digital" points to #33 "active" which then points to #26 "component"

- Type: Attribute, Category, Option or Item. Attributes and Options are related but Attributes MUST be recorded for the Item or Category of item to which they apply and Options MAY be recorded. If an Attribute points to a Category, all the Items in that Category must have records that record the value of the attribute for the items.

There is an "ItemType" table with just the base information for each item:

- Store: who owns the part (me, you, radioshack, digikey)

- Unit: Each, roll, box

- Name: Part number (if applicable) and name. E.g. ""2N222 PNP Switching Transistor"", "2K7 5% 0.25W Axial Resistor" (when it's in my store), "660-CFS1/4CT52R272J Resistor" (when it is in Mousers store), "CFS1/4CT52R272J Resistor" (when it is in the KOA Speer store),

- Amount: not necessarily published. This is the value: Quantity discounts and other prices are handled with Tags (see below)

- Quantity: number actually on hand.

- UPC: included for those items that have one.

- Weight: for shipping purposes.

And then there is a separate table ("tags") that records attributes and options for the items. It just records:

- TagTreeID: points to the entry in the COA for which this value is being recorded. E.g. tag #2759 points to TagTreeID #150 which is "Use With": An Option whose value points to another part which most users will want to use with this part.

- string: a text value

- Value: a numeric value (Double)

- targetID: The item this tag is for.

In SQL, we have a stored procedure that pulls all the tags associated with an item, or all the tags that apply to a type of item or category.

It is trivial to add an Option tag for "Location" under all parts that would story the text description of a part. There is an existing web interface to do that. There is also a web interface to edit the item, and add any required or optional tags. It isn't pretty, but it works. Other things people want to track about thier items include:

• mfgr, distributor, replacement, equivilent and other part numbers,
• Extended descriptions, comments, notes, datasheets, instructions.
• Size, shape, foot print, form factor,
• Location address, building, floor, room, shelf, box, and bin number,

Comments:

• http://hamradio.cmcorp.com/inventory/Inventory.html would be an interesting test case.
• http://www.philpem.me.uk/temp/isis_erd.png a very different database setup.
• http://www.gaspricewatch.com/USGas_index.asp

  There's already a few specialized web sites that do this for one particular product. Have you seen the one for gasoline ?
  http://www.garnetknight.com/gas/spotprice.php
  

  +

Questions:

• Steve Lawton asks:

  Question is: How do I get access to MSRP for a few of the 160,000 upc database.
  it's for the PHP application i'm building, I have upc and decription in mysql.
  grocery stores like %011110%, %041163%, %078742%, %078742%, long term I hope to get access to POS data.
  
  

  James Newton replies: The database I have does not contain MSRP or any other pricing data. At least not yet.+

  Steve Lawton replies: For my tests I could use just realist pricing data. Any ideas? In your pricebook database ItemType.amount 'Money' do you have data yet? that could work I still don't understand how to use (access) this database application. In your schema a price benchmark (msrp like) may be useful. +

  +

David A Cary Says:

This pricing website sounds like an excellent idea. I especially like the "you may also be interested in ..." idea, so when someone searches on MAX232, it also gives a link to the "Level Converter in a DB9 backshell". It might also be nifty to link to a Massmind page that discusses "gotchas" and tips related to working with the component. I see that the ComputerComponent wiki is a vaguely similar website for listing prices and discussing hard drives and other computer components.