Обсуждение: 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