Re: Database Design for Components and Interconnections

On 03/20/2011 09:25 PM, ray joseph wrote:
>> From: Andy Colson [mailto:andy(at)squeakycode(dot)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