Thread: Database Design for Components and Interconnections

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

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

Ray,

You seem to have a fairly good understanding of the model you are working
with.  I'd suggest simply finding some technical SQL resources, install
PostgreSQL, and fire away.  Learn by doing.  When doing the design focus on
minimizing the amount of non-key repetition that you model (find and read
stuff regarding database normalization).  You can use queries and views to
de-normalize the data as needed for processing.

While you want to keep in mind HOW you plan to use the data it is more
important to focus on simply efficiently representing the data using the
model.  You can never fully predict how you will want to use data but if it
is modeled well most use cases can be implemented without too much
difficulty.  If you cheat to make working with a specific use case easier
you are likely to find that, in the future, a new use case has to
deconstruct the data before it can be used.  It is much harder to
deconstruct data than to construct more complex data from simpler parts.

Lasty, remember that learning takes time and energy (though the bright-side
is that actual cash outlay is minimal if you can provide enough of the other
two items)

David J.


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of ray
Sent: Sunday, March 20, 2011 12:40 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Database Design for Components and Interconnections

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David,

Thank your for the your thoughts.  I have psql installed and I am currently
trying to use pgADMIN III.  I have used it to make and drop Databases and
tables.  I have not yet successfully built a table from SQL.  I have taken
one of my Excel tables and used it to build a CREATE table script.

I have built a couple dozen small scale databases in Access and I have read
a lot about normalization.  But I am not sure how far to take it.  I am
intrigued by the idea of using queries and views to de-normalize data.  But
I don't have a clue as to a process.

I appreciate your comment on designing for efficient representation rather
than use cases.  I am guessing that means normalization?

ray

-----Original Message-----
From: David Johnston [mailto:polobo@yahoo.com]
Sent: Sunday, March 20, 2011 9:05 AM
To: 'ray'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Database Design for Components and Interconnections

Ray,

You seem to have a fairly good understanding of the model you are working
with.  I'd suggest simply finding some technical SQL resources, install
PostgreSQL, and fire away.  Learn by doing.  When doing the design focus on
minimizing the amount of non-key repetition that you model (find and read
stuff regarding database normalization).  You can use queries and views to
de-normalize the data as needed for processing.

While you want to keep in mind HOW you plan to use the data it is more
important to focus on simply efficiently representing the data using the
model.  You can never fully predict how you will want to use data but if it
is modeled well most use cases can be implemented without too much
difficulty.  If you cheat to make working with a specific use case easier
you are likely to find that, in the future, a new use case has to
deconstruct the data before it can be used.  It is much harder to
deconstruct data than to construct more complex data from simpler parts.

Lasty, remember that learning takes time and energy (though the bright-side
is that actual cash outlay is minimal if you can provide enough of the other
two items)

David J.


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of ray
Sent: Sunday, March 20, 2011 12:40 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Database Design for Components and Interconnections

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

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

>>
>> 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.
>

Yeah, maybe chip was a bad name.

>>
>> -- 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.
>

See below

>>
>> -- 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.
>

As you can see my naming convention was not very good.
And yes, a serial is an auto-inc column, if you dont specify it, it'll be generated for you.


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

With simple databases I keep the names simple.  When they get more complex I name the columns more complex.  I started
withcid, but then changed to compid and chipid, but, of course, forgot to change some. 

You also have to worry about your users.  I have a payroll database, and I'm the only one who really writes code for
it,so names are a little more terse.  I have a much bigger database, with lots of end users who are not programmers...
soI make the names much more descriptive.  Most of the time, I choose names just long enough to be unique. 

Most of the problem with my layout is lack of understanding of your terminology.  Hopefully it gets my ideas across
aboutsplitting up the tables.  (You can safely assume I dont know anything about EE... cuz I dont :-) ) 

-Andy

> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Sunday, March 20, 2011 8:48 PM
> Subject: Re: [GENERAL] Database Design for Components and Interconnections
>
> >>
> >> 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.
> >
>
> Yeah, maybe chip was a bad name.
Andy, I was not suggesting that the 'chips' name was not inappropriate, I
was only expanding on the idea in consideration of possible normaiization.

>
> >>
> >> -- 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.
> >
>
> See below
>
> >>
> >> -- 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.
> >
>
> As you can see my naming convention was not very good.
> And yes, a serial is an auto-inc column, if you dont specify it, it'll be
> generated for you.
>
>
> >
> > 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
> >
>
> With simple databases I keep the names simple.  When they get more complex
> I name the columns more complex.  I started with cid, but then changed to
> compid and chipid, but, of course, forgot to change some.
>
> You also have to worry about your users.  I have a payroll database, and
> I'm the only one who really writes code for it, so names are a little more
> terse.  I have a much bigger database, with lots of end users who are not
> programmers... so I make the names much more descriptive.  Most of the
> time, I choose names just long enough to be unique.
>
> Most of the problem with my layout is lack of understanding of your
> terminology.  Hopefully it gets my ideas across about splitting up the
> tables.  (You can safely assume I dont know anything about EE... cuz I
> dont :-) )
>
> -Andy

I really appreciate your time and efforts in producing all these comments.
Is there a FOSS tool that will graphically display the table design?

ray

On 03/20/2011 09:25 PM, ray joseph wrote:
>> From: Andy Colson [mailto:andy@squeakycode.net]
>> Sent: Sunday, March 20, 2011 8:48 PM
>> Subject: Re: [GENERAL] Database Design for Components and Interconnections
>>
>>>>
>>>> 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.
>>>
>>
>> Yeah, maybe chip was a bad name.
> Andy, I was not suggesting that the 'chips' name was not inappropriate, I
> was only expanding on the idea in consideration of possible normaiization.
>
>>
>>>>
>>>> -- 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.
>>>
>>
>> See below
>>
>>>>
>>>> -- 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.
>>>
>>
>> As you can see my naming convention was not very good.
>> And yes, a serial is an auto-inc column, if you dont specify it, it'll be
>> generated for you.
>>
>>
>>>
>>> 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
>>>
>>
>> With simple databases I keep the names simple.  When they get more complex
>> I name the columns more complex.  I started with cid, but then changed to
>> compid and chipid, but, of course, forgot to change some.
>>
>> You also have to worry about your users.  I have a payroll database, and
>> I'm the only one who really writes code for it, so names are a little more
>> terse.  I have a much bigger database, with lots of end users who are not
>> programmers... so I make the names much more descriptive.  Most of the
>> time, I choose names just long enough to be unique.
>>
>> Most of the problem with my layout is lack of understanding of your
>> terminology.  Hopefully it gets my ideas across about splitting up the
>> tables.  (You can safely assume I dont know anything about EE... cuz I
>> dont :-) )
>>
>> -Andy
>
> I really appreciate your time and efforts in producing all these comments.
> Is there a FOSS tool that will graphically display the table design?
>
> ray
>
>


Yeah, google knows:

http://www.google.com/search?q=postgres+ER+tool

-Andy