[Author Prev][Author Next][Thread Prev][Thread Next][Author Index][Thread Index]

gEDA-user: Parts DB (was: Re: Heavy Symbols and such)




Levente wrote:
> Dave N6NZ <n6nz@xxxxxxxx> wrote:
>> Big companies, small companies, and me myself in my garage.  Having been 
>> pickled in that process over the years, I've come to see it's value.  I 
>> made myself a small mySQL database that cross-references "my" part 
>> number to manufacturer part numbers, and manufacturer part numbers to 
>> Digi-Key and Mouser part numbers. 

> I did the same in the summer. Could you send me your code, maybe we could
> merge the two database system, and have just one better.

I'll take a look at what you have as soon as I get a chance.  My code is 
pretty simple minded at this point -- I have some simple report 
generators in C, and I've started on a GUI ap in Gambas to do a "real" 
user interface. (Gambas is great stuff, BTW, if you haven't seen it.) 
The Gambas ap is not very far along and I haven't worked on it in a 
while.  But a good front-end is what would make the database most useful.

I'm not database whiz, so my database is quite simple. I'll post my 
table definitions.  Maybe we can come up with a schema that is generally 
useful, and set up some scripts and a how-to that helps people get going.

-dave


This is my parts database.  I'm not saying it is the answer to 
everyone's problems, but it helps my personal sanity.  Things are not 
overly-normalized. I'm no database guru, but I did have a card-carrying 
database guru working for me for a few months several years ago in a 
past life.  She was extremely diplomatic about my thoughts on database 
design :) and taught me not to over normalize, along with a few other 
tricks that get me by.

The core of the database is in 7 tables.  I have more planned that I 
have never implemented.
mysql> show tables;
+----------------------+
| Tables_in_robotparts |
+----------------------+
| assembly             |
| inventorycount       |
| manufacturerpart     |
| manufacturers        |
| partdesc             |
| vendorpart           |
| vendors              |
+----------------------+
7 rows in set (0.00 sec)


Manufacturers maps a name onto an index number. It's main purpose is
to keep bonehead spelling errors from corrupting the database.
mysql> describe manufacturers;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| mfgrid   | int(11)     |      | PRI | NULL    | auto_increment |
| mfgrname | varchar(20) |      | MUL |         |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


manufacturerpart maps my part number onto manufacturer part numbers.
This is where substitutable parts show up, each with their own row.
mysql> describe manufacturerpart;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| partnum     | varchar(20) |      | MUL |         |       |
| mfgrid      | int(11)     |      |     | 0       |       |
| mfgrpartnum | varchar(30) |      | MUL |         |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

vendors makes sure I spell Mouser and DigiKey the same way
every day :)
mysql> describe vendors;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| vendorid   | int(11)     |      | PRI | 0       |       |
| vendorname | varchar(20) |      |     |         |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

vendorpart maps a manufacturer part onto an orderable part
number from a vendor.  Multiple sources show up as multiple
rows.  It also has fields to keep track of the quantity I
would "normally" order in, and the unit price at that volume.
mysql> describe vendorpart;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| mfgrid        | int(11)     |      | MUL | 0       |       |
| mfgrpartnum   | varchar(20) |      | MUL |         |       |
| vendorid      | int(11)     |      |     | 0       |       |
| vendorpartnum | varchar(20) |      |     |         |       |
| stdcost       | float       | YES  |     | NULL    |       |
| stdvol        | int(11)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

partdesc is where my part number is defined, and is really the
table that drives everything else.
mysql> describe partdesc;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| partnum   | varchar(20)   |      | PRI |         |       |
| partdesc  | varchar(40)   |      |     |         |       |
| value     | varchar(10)   | YES  |     | NULL    |       |
| smt       | enum('N','Y') |      |     | N       |       |
| package   | varchar(20)   | YES  |     | NULL    |       |
| footprint | varchar(25)   | YES  |     | NULL    |       |
| comment   | varchar(40)   | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


The assembly table allows me to have hierarchical part
numbers for assemblies and sub-assemblies.  This is where
I pull kit lists.  If refdes is non-NULL, qty is 1.  This
allows the BOM to be stored in two different levels of
detail.  In practice, any electronic parts tend to have
a refdes, and things like #6 machine screws hove no refdes
but have a count > 1.  An assembly part number can show up
as a comppartnum of a higher level assembly allowing
arbitrary hierarchy of sub-assemblies.
mysql> describe assembly;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| assypartnum | varchar(20) |      | MUL |         |       |
| comppartnum | varchar(20) |      | MUL |         |       |
| qty         | int(11)     |      |     | 0       |       |
| refdes      | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


an inventory table so that I can count things occasionally,
and know if I need to order something.
mysql> describe inventorycount;
+---------+-------------+------+-----+------------+-------+
| Field   | Type        | Null | Key | Default    | Extra |
+---------+-------------+------+-----+------------+-------+
| partnum | varchar(20) |      | PRI |            |       |
| qty     | int(11)     |      |     | 0          |       |
| asof    | date        |      |     | 0000-00-00 |       |
+---------+-------------+------+-----+------------+-------+
3 rows in set (0.02 sec)



_______________________________________________
geda-user mailing list
geda-user@xxxxxxxxxxxxxx
http://www.seul.org/cgi-bin/mailman/listinfo/geda-user