Re: Database Design for Components and Interconnections - Mailing list pgsql-general

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