From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | ray <ray(at)aarden(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Database Design for Components and Interconnections |
Date: | 2011-03-20 14:00:48 |
Message-ID: | 4D860890.9060006@squeakycode.net |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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'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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-03-20 14:04:38 | Re: Database Design for Components and Interconnections |
Previous Message | John R Pierce | 2011-03-20 10:25:23 | Re: cannot connect to server |