Re: Object-oriented thinking in SQL context?

In message
<bf7e8cca-4cbc-4a8c-b452-1bacb4846b8e_at_g1g2000yqh.googlegroups.com>, dr.coffee1_at_gmail.com writes
>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.

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