[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