Re: Database Design for Components and Interconnections

> 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