Re: [GENERAL] Database Design for Components and Interconnections
> From: Andy Colson [mailto:a...@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 ca
Re: [GENERAL] Database Design for Components and Interconnections
> From: Andy Colson [mailto:a...@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,
Re: [GENERAL] Database Design for Components and Interconnections
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:pol...@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-ow...@postgresql.org [mailto:pgsql-general-ow...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to Create Table from CSV
I appreciate all the information Thank you, ray -Original Message- From: Dimitri Fontaine [mailto:dimi...@2ndquadrant.fr] Sent: Sunday, March 06, 2011 3:19 PM To: ray Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to Create Table from CSV ray writes: > I would like to create a table from a CSV file (the first line is > headers which I want to use as column names) saved from Excel. I have You have to manually create the table and its columns, as other said. The tricky part that is hard (or impossible) to automate is deciding which data type to use for each column. Once you've done that, actually importing the data is a matter of using the COPY command or the pgloader tool. In PostgreSQL 9.1 you will be able to use CREATE FOREIGN TABLE to achieve that in one step, see: CREATE FOREIGN TABLE http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html http://developer.postgresql.org/pgdocs/postgres/sql-createforeigndatawrapper .html http://developer.postgresql.org/pgdocs/postgres/file-fdw.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for Suggestion on Learning
Matt, Thank you for your insightful view. I do not have a design for any of my design opportunities. This is one reason I was looking for a design tool. I have many work processes that are inter related, generated by different groups that must transcribe data from each others artifacts. I do have Visio but I have never used it for this purpose. Since one of my objectives is to learn about db design, maybe I can find some training material using Visio. I wonder if Visio will generate SQL. If I recall, only the enterprise version of Visio produces SQL, so I would like to find a FOS tool for this. A tool and associated tutorial would be great. BTW, I do use Notepad++. I have used gnome.org/dia, but I find it much weaker than Visio. I did not realize that PHP admin required a web server, but I have recently installed Apache for SVN. I looked at PHP admin even though my preference is Python. I have also looked at Maestro but have had a similar problem with tutorials. Thank you for the link to the 'docs' site. I have been there many times over the past couple of years but now I see it in a new light (I'm slow). When you hand code SQL with Notepad++, how do you launch the code? I really appreciate your efforts. ray _ From: urlu...@gmail.com [mailto:urlu...@gmail.com] On Behalf Of Matt Sent: Saturday, February 05, 2011 10:19 PM To: ray joseph Subject: Re: [GENERAL] Looking for Suggestion on Learning I too am in a similar situation. My company currently uses M$ Access and the solution is no longer viable and needs to be dealt with. Form what I have been reading and learning the last few weeks trying to compare Access and PG is like comparing a go-kart and a race car. They both do the same thing more or less but the race car is capable of much more but also needs more attention. I am assuming that you already have a structural design for your database, tables, keys etc. If not this is a good place to start and is where I am currently at in my project. On Sat, Feb 5, 2011 at 9:22 PM, ray joseph wrote: > On Saturday, February 05, 2011 9:30:13 am ray wrote: > > I have built a few databases with MS Access and I would like to learn > > how to use pgsql. I have found some examples but they have been too > > complex to follow or to abstract with no specific details. Use the online documentation at http://www.postgresql.org/docs/ for a basic tutorial on how to create tables, queries, and the like. The docs go much further in detail then that but this is a good place to start. It gives real examples of working with tables that are easy to follow and it doesn't require previous knowledge. > > > > I would like to find a simple example that would take me from an open > > source design tool to a simple method to implement the design. What do you mean by a design tool? Are you looking for a program to help you map out the table structure of your db? Are you looking for a GUI to access your db and modify it? I am using Viso to create my maps at work right now but you may want to check out Dia http://projects.gnome.org/dia/, it is a good piece of software but I haven't used it for this purpose yet. When you install PG it comes with the GUI PGAdmin that gives you basic control over some aspects of your db and allows you to implement various things. I am using PHPAdmin myself, as this project is entirely based on the net and I also have a fondness for php. This can be acquired through the stackbuilder app included with the single file installer for PG. It does require you to run a webserver though, so this may not be the route you wish to take. As far as building the db itself I hand code the SQL in notepad++ http://notepad-plus-plus.org/. I am not a big fan of IDE's for small scale or single file projects so this editor is great. It provides a tabbed interface and has syntax highlighting for many of the most common languages and is fairly lightweight. > > > > I would like to find a simple guide, tutorial or example and will > > appreciate any help. Being more specific as to what you are looking to learn may help people to suggest the right guide for you. I have had great luck here in the last few weeks with recommended books and articles. > > > > ray > > It will be difficult to find a simple drop in replacement for what you had > with > Access. The closest thing I can think of is OpenOffice/LibreOffice Base > (http://help.libreoffice.org/Common/Database_1) and that is not as well > integrated. Most Open Source development tend to use chains of tools, > admin/creation --> driver/middle layer --> GUI design, with each aspect > handled by a different program. I tend to handle admin/creation with text > files run through psql. I work with Python so my database driver is > psycopg2. This n turn get
Re: [GENERAL] Looking for Suggestion on Learning
> On Saturday, February 05, 2011 9:30:13 am ray wrote: > > I have built a few databases with MS Access and I would like to learn > > how to use pgsql. I have found some examples but they have been too > > complex to follow or to abstract with no specific details. > > > > I would like to find a simple example that would take me from an open > > source design tool to a simple method to implement the design. > > > > I would like to find a simple guide, tutorial or example and will > > appreciate any help. > > > > ray > > It will be difficult to find a simple drop in replacement for what you had > with > Access. The closest thing I can think of is OpenOffice/LibreOffice Base > (http://help.libreoffice.org/Common/Database_1) and that is not as well > integrated. Most Open Source development tend to use chains of tools, > admin/creation --> driver/middle layer --> GUI design, with each aspect > handled by a different program. I tend to handle admin/creation with text > files run through psql. I work with Python so my database driver is > psycopg2. This n turn gets used by a framework. For desktop apps I use > Dabo > (http://dabodev.com/). > Since the final output is determined by mix and match it is hard to find a > 1-2-3 tutorial. My suggestion is to make a list of your needs and work > bck from there: > > 1) What OS(s) do I want to deploy on? > 2) What programming language(s) do I want to work with? > 3) Where do I want to deploy, desktop/Web? > 4) What do I want to build, simple SOHO apps enterprise apps? > > With answers to these questions it would be possible to narrow the field a > bit. Unfortunately, it is one of those good news/bad news situations. > Good news, Open Source is about a variety of choices. Bad news, Open > Source is about a variety of choices. > > -- > Adrian Klaver > adrian.kla...@gmail.com Adrian, Thank you for the clarifications. I would like to address the guiding questions you presented: 1) What OS(s) do I want to deploy on? Windows, right now XP. 2) What programming language(s) do I want to work with? Python. 3) Where do I want to deploy, desktop/Web? Desktop at first. 4) What do I want to build, simple SOHO apps enterprise apps? SOHO at first. My responses represent what I expect a learning path to take. Although I will probably not be programming for web or enterprise, what I build may be a prototype for such. I am not looking for the tools to build the big apps, I just want to learn the basics, or more important right now, the simple. Please help me understand what you mean by " I tend to handle admin/creation with text files run through psql." I looked at Dabo and it looks like it is for developing applications. Is there a tool for designing a database? Ray -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Defining a Connection String in Windows XP
> -Original Message- > From: Susan Cassidy [mailto:scass...@stbernard.com] > Sent: Monday, December 13, 2010 2:06 PM > To: ray; pgsql-general@postgresql.org > Subject: RE: [GENERAL] Defining a Connection String in Windows XP > > >Ray, > > > >Thank you for responding. OK, that was my first trip into DSN setup > >and I got lost: > >The first step is to choose and existing MS driver for an Office app > >or 'Add' one from this 'User DSN' tab. The other tabs are: > >System dSN, File DSN, Drivers, Tracing, and Connection Pooling. > >I did not see a pgsql listed in any - what is the next step (or two)? > > > >ray > > I usually use a System DSN. If, when you click Add, you don't see > 'PostgreSQL ANSI' as one of the choices, you don't have the ODBC driver > installed. If you do, just fill in the boxes with the database name, > password, etc. > > Susan C. > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general This is great! Just for reference, the bat file would not run because there was not an existing pgsql driver. The msi file worked right off. I used a System DSN and it worked. Now to figure out what to do with it. BTW, what are some of the considerations for choosing DSN types System, File, User? Thank you very much, ray -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to test my new install
Raymond, Thank you very much. This is exactly what I was looking for. I'll jump right into it. Ray - Original Message - From: "Raymond O'Donnell" To: "ray joseph" Cc: Sent: Monday, February 01, 2010 7:08 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 12:21, ray joseph wrote: > > Raymond, > > > > Thank you. Yes, that sounds like a great step. I am new to this so I could > > use a little help: What do you mean to connect to it and how would I do it? > > psql is the command-line client for Postgres, which lets you connect to > databases and run SQL commands directly against them. You can read about > it here: > > http://www.postgresql.org/docs/8.4/interactive/app-psql.html > > On the XP machine, open a command prompt and type > > [path to PG install dir]\bin\pgsql -U [username] [databasename] > > ...and see if you can connect to the database. If you can connect and > run queries, then the DB server is running. > > If your app stack is connecting from a different machine, I'd then try > connecting from that machine to see if there are any network issues that > would prevent the connection. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to test my new install
Raymond, Thank you. Yes, that sounds like a great step. I am new to this so I could use a little help: What do you mean to connect to it and how would I do it? Ray - Original Message - From: "Raymond O'Donnell" To: "ray joseph" Cc: Sent: Monday, February 01, 2010 6:17 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 10:48, ray joseph wrote: > > Raymond, > > > > Thank you for responding to my question. I am sorry that I wasn't clear. > > > > My concern is that when I start up my tool stack and it doesn't work, that I > > will have an entire chain to troubleshoot. If I could isolate each tool as > > I install it, and validate that it is working, it will be easier to assess > > any other difficulties. > > Well, to see if PostgreSQL is working, the simplest thing is to try > connecting with psql and see if it responds. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to test my new install
Raymond, Thank you for responding to my question. I am sorry that I wasn't clear. My concern is that when I start up my tool stack and it doesn't work, that I will have an entire chain to troubleshoot. If I could isolate each tool as I install it, and validate that it is working, it will be easier to assess any other difficulties. Ray - Original Message - From: "Raymond O'Donnell" To: "ray" Cc: Sent: Monday, February 01, 2010 3:33 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 01:15, ray wrote: > > I have just installed 8.4 on an XP. My intent is to use it with Trac > > and Apache. > > > > I would like to validate the installation of pgsql. What would be a > > good method to make sure that pgsql is in there right? > > Not sure what you mean. I'd imagine the thing to do is to run your > application against it in a test setup and check tat everything works as > you expect. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
