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'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?
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
