James Newton wrote:
> Phil, I'm VERY interested in that. I've been wanting to write something like
> it for years, and have actually posted about it on the PICList some time
> ago. I want to host it as an application that many people can use and use to
> share information (e.g. part name from number, replacement part when one is
> obsoleted, etc...) and possibly to buy and sell between one another to
> reduce shipping / time when possible.

Hmm, not my original plan, but certainly doable.
But I'd do that as a separate project instead of integrating it into ISIS.

> I have developed a database that 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"
(snip)
> In SQL, I 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.

Nifty.

I've been using MySQL for the database, and designing the database with 
DBDesigner 4 (which takes an entity relationship diagram and turns it into SQL 
code). Here's my DB design:
<http://www.philpem.me.uk/temp/isis_erd.png>

Basically you have a number of core tables:
   - Manufacturer
   - Supplier
These store details about manufacturers and suppliers and have basically the 
same format. Name, address, etc. ManufacturerAlias and SupplierAlias store 
aliases for the manufacturer/supplier records, so you can (e.g.) have a 
manufacturer record "OPTEK" that is aliased to "TT Electronics/OPTEK", so that 
when you do a Digikey import, all the stuff for TT/Optek gets filed under 
Optek. That way you can save typing when doing a mass import too - for e.g. 
Fairchild, add an alias 'Fch' and when you enter the manufacturer 'Fch' in the 
Mass Import function's manufacturer field, the new part will be marked as 
being manufactured by Fairchild.

Next up is the Parts table - the core of the database. This stores details on 
a given part - local stock code, description, manufacturer's part number, 
manufacturer, number of pins, package, and any associated notes.

Then you have the Locations table. This stores details on the parts in a given 
location. Pretty simple - the part ID matches (relates to) a part in the Parts 
table, then you have a bin location and quantity. This means you can have 123 
XYZ123s in box A, and another 41 in box B. You can also have more than one 
different item in the same box - e.g. 12 XYZs in box A, five ABCs in box B, 
and six QAZ123s in box A again.

StockEvents store data on previous stock transactions -- items that have been 
added to or removed from stock. This is basically an audit trail - if 
something is used, you can find out (quite easily) what it was used for.

Datasheets stores all the datasheets for all the parts. A part can have as 
many datasheets as you like, and all of them are tied into the Part record.

BOM contains the per-BOM details - the project name and description. BOMLine 
contains the actual lines of the BOM - the BOM the line is associated with, 
the part it references, and the quantity required.

All pretty simple and pretty standard.

> I would love to work with you to integrate what you are doing and what
> wouter is doing with what I am doing. It seems like we have taken very
> different approaches.

You're welcome to do so - know any PHP? :)
I'm going to see about getting it onto Sourceforge soon (maybe), and put a 
demo online at some point today, once I've got the new server set up.

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
piclist@philpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.
-- 
http://www.piclist.com PIC/SX FAQ & list archive
View/change your membership options at
http://mailman.mit.edu/mailman/listinfo/piclist