Re: Database Design for Components and Interconnections - Mailing list pgsql-general

> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Sunday, March 20, 2011 9:01 AM
>
> On 03/19/2011 11:40 PM, ray wrote:
> > I am looking for some help in database design.  I would like to design
> > a database to help design alternative designs of a basic electronic
> > circuit design.  I have a list of components that will be
> > interconnected for a basic design.  Additional components and
> > associated connections are identified for different alternatives.  The
> > connections have properties that must be managed.
> >
> > The typical use is to implement a new design where a specific set of
> > components is identified and the associated interconnects need to be
> > managed.  Additionally, these two sets of data will be copied to
> > another application for analysis.  The connection information is a
> > matrix where the row and column 'labels' are elements of the
> > components table.  The matrix elements define the interconnections
> > between the components.
> >
> > In the simplest case, the interconnection matrix elements are just
> > either -1, 0, or 1, defining whether or not there is a connection
> > between the two components and the direction of the connection.  In
> > the more realistic cases, there are many properties of each
> > interconnection so this is a three dimensional matrix.
> >
> > As for performance, this database will be accessed by at most 20
> > people at one time where they are addressing disjoint properties.  The
> > number of components will be a couple thousand.  The average number of
> > interconnections of any one component to other components is 6 so the
> > matrix may be considered sparse.  I usually use a spreadsheet for the
> > component definitions and multiple spreadsheets (tabs) for each of the
> > tables in the third dimension.  Then save the needed interconnection
> > info as a CSV file for import into other applications.
> >
> > I will appreciate any suggestions, insights, questions and comments.
> >
> > Thanks,
> > ray
> >
>
> A few rows of your spreadsheets as example might help.
>
> Not real sure, so I'll just start basic, and we can discuss and improve.
>
Andy,

I really like your suggestions.
>
> You may, or may not, want a top level table:
>
> create table chips
> (
>     chipid serial,
>     descr text
> );
>
Yes, I see great value in a top level component table.  I am not sure how to
handle multiple instances of the same type of chip in different services.  I
think the idea is to give each instance a unique service description and or
tag number to tell them apart.  I don't want to use a description as a
differentiator as several components may contribute to, say, different parts
of an output function.

I see 'chips' as a catalogue.  I may use 2 of these, 4 of those on this
particular design.  Another design might have a different mix.  When a
concern comes up with a particular chip used in different designs, it would
be handy to identify all the designs that used that chip.  It would also be
useful to keep track of different versions of that chip.

Chips have package designs, they may have pins, flats, tabs, etc.  They
package they may have cooling requirements, mounting options, inventory
status, suppliers, etc.  Depending upon the particular application, package
types may be coordinated.

>
> -- Then we will create alternate designs for each chip
> create table designs
> (
>     did serial,
>     chipid integer,
>     compid integer
> );
I did not even consider the idea of a 'design' table.  This will provide a
catalogue of implementations and a great study object.  I do not know what
compid is and I would expect to include interconnections in the design.
Design may be for a particular application, study branches, customers, etc.

>
> -- The list of components
> create table components
> (
>     cid serial,
>     descr text,  -- dunno if you want this, or maybe model #....
>     voltage float  -- dunno... maybe
> );
I think this is a design component table; components used in a specific
design.  Is that the intent?  I would think this table should link to the
chip catalogue.

>
> -- Each component has interconnects
> create table interconnects
> (
>     iid serial,
>     cid integer,   -- component
>     input bool,    -- is there a different set
>             --- of input and output interconnects?
>     pintype integer, -- dunno, something describing the connection
>     maxlength integer
> );
Each pin might have a connection which could be in or out and it might be
power or signal, even type(s) of signal.

>
>
> Now lets create some data:
>
> insert into chips(descr) values ('math co-processor for 80386');
>
> -- design one has two components
> insert into designs(chipid, compid) values (1, 1);
I think we want cid rather than compid above, and similaryly below.  I am
guessing that this insert automatically gets a serial key generated.

> insert into designs(chipid, compid) values (1, 2);
>
> -- lets create the components
> insert into components(descr, voltage) values('PCI123', 1.21);
> -- and its interconnects
> insert into interconnects(cid, pintype) values(1, 1);
> insert into interconnects(cid, pintype) values(1, 0);
> insert into interconnects(cid, pintype) values(1, -1);
>
> -- another components
> insert into components(descr, voltage) values('PCI666', 1.21);
> -- and its interconnects
> insert into interconnects(cid, pintype) values(2, 1);
> insert into interconnects(cid, pintype) values(2, 0);
> insert into interconnects(cid, pintype) values(2, -1);
>
>
> Here is how the data looks:
>
> andy=# select * from chips;
>   chipid |            descr
> --------+-----------------------------
>        1 | math co-processor for 80386
> (1 row)
>
> andy=# select * from designs;
>   did | chipid | compid
> -----+--------+--------
>     1 |      1 |      1
>     2 |      1 |      2
> (2 rows)
>
> andy=# select * from components;
>   cid | descr  | voltage
> -----+--------+---------
>     1 | PCI123 |    1.21
>     2 | PCI666 |    1.21
> (2 rows)
>
> andy=# select * from interconnects;
>   iid | cid | input | pintype | maxlength
> -----+-----+-------+---------+-----------
>     1 |   1 |       |       1 |
>     2 |   1 |       |       0 |
>     3 |   1 |       |      -1 |
>     4 |   2 |       |       1 |
>     5 |   2 |       |       0 |
>     6 |   2 |       |      -1 |
> (6 rows)
>
>
> And I see a problem with the designs table, the id (design id = did), I
> was thinking one design had two components, but that's not what the table
> is describing.  But I think this is a good start.  It gets my
> understanding of the problem across.  Does it seem to match what you are
> trying to model?
Yes, this is going great!

>
>
> -Andy


I have a general question.  I see that you consistently use very short
abbreviations such as did and cid.  I have used short, medium and long.
Short are great for inputting but I am always looking up what my
abbreviations are.  This has been difficult as I have never had an efficient
way to look them up.  Medium gives me a hint as to what the meaning is but I
often get the spelling wrong since there is no consistency in how I shorten
names.  Long names with prefixes and suffixes are easily recognized but
lengthy to input.  With the write editor, auto completion might over com
some on the time consumption.

How do you manage this?  Just good memory?

Regards,
ray