Hi folks.

I have a problem with wrapping my mind into the 'right' wrinkles.
I need to come up with a database design in SQL/MSAccess,
since that's the tool that is available to me. The subject of the
database is an inventory of electronic instruments, and the
objective is to maintain a status log of these instruments.

The naive idea is:

- Instrument ID
- Instrument status (active / stand-by / inactive)
- Instrument location (room / shelf / position)

The above ought to be valid for all instruments. Then there
are a few instruments that need to be calibrated before use.
These need to have some additional fields:

- Calibration status ( OK / not calibrated )
- Calibration data ( varies with type of instruments )

The problem is the latter two fields. Only a few instruments
need to be calibrated at all; and the calibration data varies
with the exact type of instrument. A microphone might
need a gain factor from sound pressure to voltage; a
GPS position sensor might need an (x,y,z) location
plus orientation along three axes.

The above would be almost trivial to implement in an
object-oriented context (well, this si my first attempt at
databases at all - my experience is with OO programming),
but I don't see how to come up with a table-based database
design.

Any general ideas on how to design a SQL database around
such constraints?

Dr. C.

dr.coffee1 (AT) gmail (DOT) com wrote:

Those are mostly trivial data modelling problems. Have you read anything
on data modelling, normalization, joins?

On 8 juin, 17:46, dr.coff... (AT) gmail (DOT) com wrote:
Before doing any database design, you need to define first your
problem in a way you are not accustomed to. OO thinking is too
sloppy and imprecise to be easily transposed to database design.

On 8 Jun, 18:25, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
Yes, I have. Well, 'browsed' is a better term, as the
objective is to get a working demo system up in a hurry.
As age progresses, I'm more and more inclined to skip
reading what is not immediately percieved as useful, so
presumably I don't see the forest for the trees. Databases
are the solution to the problem at hand; I just don't have
the hands-on experience (yet) needed to come up with a
working system.

The problem is that I think in OO terms, like classes and
inheritance. Decades ago I used to work very hard to get
away from arrays and other non-OO data structures associated
with procedural programming, and now I am unable to revert
my mind to that context.

In particular, I don't recognize OO terminology from what
I read, and I am not able to recognize OO concepts from
the terminology I do see. As somebody correctly pointed out,
I am not used to the problem statement that needs to be used
in DB design.

So in the 'naive' problem statement I see an array of objects
of classes derived from a base class (in C++ I'd use
boost::shared_ptr to access the objects), while I read that
SQL is constrained to 'trivial' arrays. The problem is the
vast philosophical distance between the two problem statements,
that I am unable to bridge.

I'd appreciate any key words to look for when re-reading
the material. I'm using Teorey's "Database Modeling and
Design: Logical Design", 4th edition.

Dr. C.

dr.coffee1 (AT) gmail (DOT) com wrote:

[snip]

Then you will make the mistakes that newbies make.

You need knowledge first.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

In message
<bf7e8cca-4cbc-4a8c-b452-1bacb4846b8e (AT) g1g2000yqh (DOT) googlegroups.com>,
dr.coffee1 (AT) gmail (DOT) com writes
You also need to consider calibration state too. Calibration has to be
redone at intervals, you need to know when the current calibration will
expire.

But, getting back to the issue at hand. Database design generally starts
by establishing what types of thing you need to hold information about.
So "Instrument" is the first entity to design. You need a key for this.
Do the instruments have any sort of asset tag? If so then the tag number
is a good choice to use as the ID. If they don't already have an asset
tag then you need to find some way of permanently labelling the
instrument. If an instrument has a serial number record it here too.

Add a field that shows whether the instrument requires calibration. Add
another field to show the instrument's storage location. Add one for
active/standby/inactive status. Add any more fields that you need to
describe a generic instrument.

You also need a table that holds calibration information. You need to
make some decisions about how you are going to use the data. There is no
point in building a horrendously complex system if all you need is a
free-text note. You probably want a table that shows calibration events,
with a start and end-date for each calibration. An instrument is
calibrated if today's date lies between the start and end date of the
latest calibration.

Do you need any more than a simple text-field to hold notes about what
type of calibration was done? That's generic and could be applied to
pretty much any device. Do you need this data to have any more structure
than that?



--
Bernard Peek

dr.coffee1 (AT) gmail (DOT) com wrote:
Define 'working'.


Hands-on experience is no substitute for fundamental education. If we
had to rely on hands-on experience for multiplication, I doubt anyone
would have ever progressed beyond the 12 times tables.


Am I hearing you correctly? You worked very hard to force your brain to
think in terms of a single physical computational model? That's
unfortunate and must be very limiting. No doubt you will have to work
equally hard to overcome that impediment.


I am not sure what use you would make of OO terminology. It's all rather
nebulous, overloaded and imprecise. None of it is any good for
communicating much of anything.


Philosophy? An n-dimensional relation of arbitrary domains is 'trivial'?
And all these years I thought the number of golf balls on the moon was
trivial.


Keywords? Introduction, preamble, prologue, chapter 1. I would start
with one of those. Not sure which terminology Teorey uses in the 4th
edition, but I am sure you will find something suitable shortly after
Table of Contents.

On 9 Jun, 08:49, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
A demo system based on databases where some basic functionality
is up and running, to demonstrate how to solve administrative
tasks that are handled manually these days.

The techies are asked to keep track of the instruments, and
somebody somewhere else do occasionally want to know the
status of the inventory or calibration data for some operational
instrument. I'm sure you'll get some impression of the problem
from the sketch in my first post.

Instead of making a phone call ordering the techies to dig
out that info manually and send back by email, my goal is
to get a skeleton MSAccess database and MSExcel interface up
to demonstrate how those who want to know can get the info
without requiring manual interference from other people.

Everybody are happy and time-wasting stress factors are
removed.

I know. But I'm doing this on my own, and am just starting out.
I have a few books lying around that I need to make sense of.
Naively, I thought asking experts for help would be useful.

Well, my basic programming training (I'm not a programmer but a
data analyst by vocation) was in terms of assembler-style GOTO
constructs and procedural programming. After a while I found
that these styles or models were far too limiting for the
types of problems I worked at. I had to make a serious effort
to learn OO in general and C++ in particular. These days I think
in terms of class hierarchies and policies.

Your name and location suggest your native language is English.
If correct, you likely do not know a second language very well.
Bilingual people will know that different languages tend to be
useful for expressing different things. My native language is
not English, but all my professional training was in English.
Which has had the effect that I am unable to think about
professinal questions in my native language. I find that I
always refer to English terms when discussing work.

OO and policies has had the same effect, and have worked well
enough that I haven't needed to look back. Until I encountered
databases and SQL.

Ever heard of a 'class'? 'Supertype'? 'Specialization'?
Teorey uses UML to communicate problems and solutions
in the context of databases. Just as lots of folks do
with OO programming. The concepts are the same everywhere.
Terminology differs.

Yes, philosophy. It seems you are young enough that your
basic training was in terms of OO concepts. I can assure you,
when your basic training is in terms of procedural programming,
the transition to OO is one of philosophy.

No. The individual tables are trivial, not the overall relation.
This relation is essentially the philosophical difference I was
talking about: Procedural programming can represent anything the
OO model can (as I understand it, OO programs are implemented as
procedural constructs) but the details are understood by human
users in different ways.

Complex relations, that are trivial for the human to comprehend
when expressed in OO terminology and concepts, are not at all so
when expressed in terms of basic data structures like tables and
arrays.

This is the philosophical difference some of the posters here
seem not to understand but nonetheless are gloating about - like
in the 'laugh of the day' a couple of days ago: What database
professionals and experts take for granted is at least percieved
as at odds with the basic training of general-purpose computer
practitioners like myself.

Referring back to natural languages, it is a very humbling
experience to arrive in a place where you have no language
in common with the natives. One is essentially set back to
the level of a toddler, struggling to interact over the
simplest things. As English becomes more and more wide-spread,
native English-speakers would not have this experience.

Now, it makes no sense to mock people who ask about databases
because they do not know English. It makes no sense to mock
people who want to get help with basic terminology because
they do not know basic terminology.

The database community can handle this in a couple of ways:

1) Close in on yourself and mock people who do not have the
insights you have.
2) Recognize the problem and help sort out the misunderstandings
and snags.

Over the past couple of days I have got a clear impression about
what approach seems to be preferred by the regulars here.

Dr. C.

<dr.coffee1 (AT) gmail (DOT) com> wrote

some combination of easy and unimportant.

SQL tables are very easy for those who have learned to think in terms of
them, to understand data requirements from a data-centric and set oriented
point of view, and to implement tables that will be easy to keep current,
easy to maintain data integrity, and easy to write queries against. The
results you can acheive with SQL tables are far from unimportant. I read
recently where somebody replaced sometihng like 20,000 lines of object
oriented code with about 300 lines of SQL, and sped up the weekend update
process from 45 hours to 45 minutes. The object oriented code in this case
may have been clumsy. There are many clumsy designs where an OO language is
used to merely code up a software design that is fundamentally procedural.
When you do that, you get all the overhead of OO run time systems, without
most of the power.

Something analogous happens with database design. People who begin
designing databases based only on prior experience with indexed files, and
who expect to put all the business logic in the application, end up with
databases that are clumsy, slow, and fairly inflexible. People who know
what they are doing can acheive outstanding results. Unless you are far more
intelligent than I, mere "browsing" works on database design is not going
to make you a competent designer.

Just as you moved away from thinking procedurally when you migrated to OO
thinking, so likewise most of us who started with procedural languages and
migrated to SQL moved away from procedural thinking. To you, thinking in
terms of tables or arrays looks like a return to procedural programming.
Believe it or not, coding up all the intelligence in a business system in
Java looks to me like a return to procedural thinking. I would prefer to
design a database that is relevant, flexible, fast, and powerful. I think
I'm not alone in this perception.

One key difference between procedural thinking and SQL thinking is thinking
in terms of sets of data rather than breaking a set down into a loop that
processes elements one at a time. At some point, the SQL query optimizer
may have to do that, in order to come up with a strategy for carrying out
your query. But outthinking the optimizer is a singularly wasteful use of
brainpower. It's much better to come up with logical data structures that
are relevant to the problem at hand, and to implement physical data
structures that capture the essential features of the logical data
structures.

Back when I learned SQL, we tended to think of SQL as "relational". Since
that time, in places like this newsgroup, I've learned about some flaws in
SQL, when viewed as an implementation of the relational model of data.
Those flaws are real. Nevertheless, SQL allows you to do a lot of the
things that led Codd to propose using the relational model of data for large
shared data banks. There is a learning curve here. You can shorten it, but
you can't skip over it.

Snipped

for a design exercice but you need that help to be done according to
the principles you believe are universal (whatever these may be). All
I have seen so far are goodwill people who wanted to help you by
advising you to do some reading necessary to understand some basic
principles of database design. In a word, you ask a question and
because you don't like the answer you now imply that the *regulars*
have closed themselves and mocked you.

Don't you think this is a hasty harsh judgment on your part ?