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

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.


You may, or may not, want a top level table:

create table chips
(
    chipid serial,
    descr text
);


-- Then we will create alternate designs for each chip
create table designs
(
    did serial,
    chipid integer,
    compid integer
);


-- The list of components
create table components
(
    cid serial,
    descr text,  -- dunno if you want this, or maybe model #....
    voltage float  -- dunno... maybe
);

-- 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
);


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);
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'snot 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? 


-Andy