Re: Database Design for Components and Interconnections - Mailing list 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