Design assistance requested

[CB]

I apologize for the length of this post. I’m trying to give as much info the
first time to help you understand what I need. I haven't done any design in
some time so I feel weak on normalization.

I am contemplating moving from Excel workbooks to an Access database for our
equipment comparison data. I **think** it is worthwhile and need
assistance/guidance with the best method for creating my tables. My questions
are towards the end of this post.

Our process to date … we have one regional standard (RS - consists of two
sensors) and many travelling standards (TS – one sensor). On a regular basis,
we compare the travelling standards against the regional standard and take
numerous readings. Currently, there is one workbook per travelling standard.
Within each workbook, there is one spreadsheet for each comparison and each
comparison has many readings.

All the info that is CURRENTLY recorded on a spreadsheet for a given
comparison, if I were to like it to a database table would be:

tblComparison
TSSerNum (actually not on each spreadsheet. It’s in the filename and header
of each worksheet)
CompDate
InstallTime
TechName
RdgTime1 to RdgTimeX (for first order normalization – just one field, i.e.,
RdgTime)
RS1Rdg1 to RS1RdgX (for first order normalization – just one field, i.e.,
RS1Rdg)
RS2Rdg1 to RS2RdgX (for first order normalization – just one field, i.e.,
RS2Rdg)
TSRdg1 to TSRdgX (for first order normalization – just one field, i.e., TSRdg)
Remarks

Additional info that I think would be necessary would be:

CompID - PK
TSModel
RSModel
RSSerNum

If I were to normalize some more I suppose I could have the following three
tables with their PKs as FKs in the comparison table.

tblTechs
TechID - PK
TechName

tblRS
RSID - PK
RSModel
RSSerNum

tblTS
TSID - PK
TSModel
TSSerNum

However, I’m hoping to add this to our existing calibration database where
there already exists a techs table and equipment table. Hmm, I suppose having
both the RS and TS listed in the equipment table will complicate things.

Anyway, I’m not sure what the best way is to go from here but I have a
feeling that I need some kind of Readings table. Should I have a separate
Readings table for each travelling standard? I’d appreciate any guidance you
could provide.

Regards,

Chris

 

[CB]

Hi again,

I forgot to add that there are situations where more than one TS is being
compared against the RS at one time. For example, today I compared three
units against the RS so I had three work books open. Consequently, the data
for the following fields was identical in all three books:

CompDate
InstallTime
TechName
RdgTime
RS1Rdg
RS2Rdg

as well as RSModel and RSSerNum were they to be added.

Unique data would be TSRdgs for each different TS, as well as the remakrs.

I'm sure this complicates things.

Thanks again!

Chris

 

[KARL DEWEY]

Should I have a separate Readings table for each travelling standard?
Just one for readings but with FK field for the 'many' side of relationsip
with TSID - PK.
tblRS_TS_Read --
RS_TS_ID PK
TechID - FK
RSID - FK
TSID - FK
ReadDate - DateTime
RdNUM - if you need it
Reading - each reading a separate record
RMKS

Set one-to-many relationships between the tables and tblRS_TS_Read.

I do not know what InstallTime is for. Does it have a bearing on the
reading? ReadDate will store date and time so no need for RdgTime.

 

[CB]

Hi Karl,

Thanks for responding so quickly. I'm on my way out the door so can't
saymuch at the moment but I will respond further later - if not this
afternoon (almost 10 a.m. in Manitoba right now) then on Monday.

We have be recording installation time as well as reading times since the
equipment must be allowed time to settle before comparisons are made. If the
tech starts taking readings too soon, the initial readings may be
questionable.

Thanks again!

Chris