Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Metcalf
Yak Posting Veteran
52 Posts |
 Posted - 2008-08-20 : 10:52:34
|
| My company has recently hired several refugees from a now-defunct competitor; one of them brought along a DOS application written in 1986 that they had used to record test data/generate reports for a specific customer. As the 'programming guy' I've been tasked with recreating the application and making it generally more useful. That brings me to this point; the underlying DB for the application consists of a single table, 65 columns by 13000 records. The structure is basically:ID, Date, Part Number, Test1, Test2, Test3, Test1A, Test2A, Test3A, Test1B... and so on. Each record in the table pertains to a battery of tests performed on a single part, generally two/three measurements for each test. Not every part undergoes every test, but if it DOES undergo a test then all fields related to that test will be populated. Front-end data entry is split into 8 sequential screens, more out of real-estate constraints than for logical division. What I'm wondering is, when recreating this app should I:A) Not break the table down at all, each record represents one Test Process so stick with it as it is?B) Break the table down to a 1:1, with one entry in a main table relating to one row of test data in a second table (consisting of data from all tests)or C) Break the table down to a 1:many, with one entry in a main table relating to one row in a table for each genre of test?Also, I will need to do calculations behind-the-scenes based on the first measurement, second measurement, third measurement for some tests, for min/max on others, and for mean on others... so... If I go with either option B or C above, would it be better to go with an IndividualTestX structure ofPartID, TestResult, TimeStamp or stick with the PartID, TestResult1, TestResult2, TestResult3 established in the first table? Forgive me for rambling & driving all over the road, I'm just looking for something to kick-start the process here. I can do the front end for this in like a minute, but I want the underlying DB to be perfect, in case we need to add conditions, etc. and having mostly worked with OPP I'm a little hesitant to commit to the idea I have in mind (option C above). |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-20 : 12:01:31
|
| How about something like.....Table : PART ([id] (Surrogate Key), <unique info for parts>)Table : partTests ([Id] (Surrogate Key), [partId] (foreign Key to Part), [testDate] DATETIME, <unique info about test>)Table : CheckType ([Id] (surrogate Key), <uniqe info about this type of check>)Table : CHECKS ([Id] (surrogate Key), [partTestsId] (foreign Key to partTests), [checkTypeId] (foreign Key to checkType), <unique info about that particular check>)as a table layout -- I'm not sure you need checks and partTests to be two separate tables.-------------Charlie |
 |
|
|
Metcalf
Yak Posting Veteran
52 Posts |
Posted - 2008-08-20 : 13:15:41
|
quote:
Originally posted by Transact Charlie
How about something like.....Table : PART ([id] (Surrogate Key), <unique info for parts>)Table : partTests ([Id] (Surrogate Key), [partId] (foreign Key to Part), [testDate] DATETIME, <unique info about test>)Table : CheckType ([Id] (surrogate Key), <uniqe info about this type of check>)Table : CHECKS ([Id] (surrogate Key), [partTestsId] (foreign Key to partTests), [checkTypeId] (foreign Key to checkType), <unique info about that particular check>)as a table layout -- I'm not sure you need checks and partTests to be two separate tables.-------------Charlie
I appreciate your feedback on this - your idea is essentially what I had in mind; the parts are components to roadway bridges. ASTM has published several standards that govern these types of things, and each of these standards establishes various tolerances and characteristics that prospective materials must meet to be considered compliant. This application comes in as we perform each of the underlying tests of a given standard; at runtime, calculations are performed based on the test results and then are spit out as a report verifying that on such & such a date, this material was tested against this standard with these results. For liability issues we have to retain all test data for some crazy term, I may be exaggerating but I think I heard 40 years. The previous version of this app lasted 22 years, I think it would be pretty cool if whatever solution I come up with is designed well enough to last through the retention period :) |
|
|
|
|
|
|
|
|
Not a 'DB Guy' - what should I do with this?