If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design assistance requested
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 |
#2
|
|||
|
|||
Design assistance requested
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 "CB" wrote: 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 |
#3
|
|||
|
|||
Design assistance requested
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. -- Build a little, test a little. "CB" wrote: 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 "CB" wrote: 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 |
#4
|
|||
|
|||
Design assistance requested
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 "KARL DEWEY" wrote: 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. -- Build a little, test a little. "CB" wrote: 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 "CB" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|