A Question About Architecture

[Roger Wright]

After a brief break to play with GDI+ I'm back to the equipment database I've been toying with. I'm now looking at the structural alternatives to support record keeping for transformers, specifically test results. Transformers are tested at varying intervals, with different tests performed at different times. We test for water content in the insulating oil, for dissolved gasses (about 10 of them), for dielectric strength, and sheer strength, also sometimes we want to look at the turbidity and color. That's just for the oil! We also have to periodically test the insulation strength of the entire assembly (Doble tests), bushing BIL, impedance, CT ratios, and various other tests. At first glance I was considering one humongous table called TransformerTests to record the dates, types of tests, and results, but this would be ridiculous to manage, and extremely wasteful, as most of the record fields would be empty. My second thought was to create tables for each type of test, then in the TransformerTests table simply enter the date of each with the type and record number of each test performed, using a query on the detail tables to retrieve test information and build reports. The second way makes a lot of sense to me, but I'm wondering if there's a third, smarter way that someone here can suggest? Thoughts?

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

[Mycroft Holmes]

The problem with having a table for each test is that when you need to add a new type of test you are off into the database and code to extend you system. Try something like this Transformer (TransformerID, etc) This should actually be an equipment table with Transformer as a type TestType (ID,name) basically a placeholder for attributes TestAttr (ID, AttrLabel) these are varchar and are basically name value pairs TestTypeDefault(TestTypeID, AttrID) see notes TestTable (id, transormerID, TestTypeID dates, pass/fail, comments, user) only the common fields for ALL tests TestResults(TestID, AttrID, AttrValue) When you create a testtype you link the default attributes to the type. Creating a Test (attached to a transformer) will get a copy of the default attributes into the testresult table and allows you to add additional attributes to this test This will allow you to create new test types and tests without any programming. The major drawback is that all your data is in varchar fields and will need converting if you need to manipulate it properly. I've done this one in the 90s for a manufacturing system and it worked well, comparing test results across a period of time was a PITA because of the formatting. You could put a datatype indicator in the attribute table to help the UI do some validation and grow it from there.

Never underestimate the power of human stupidity RAH

[Roger Wright]

Interesting approach, though I don't understand all of it. The attribute bit stumps me - what's that got to do with data and tables? I've only used Access, Reflex, FoxPro and Paradox in the past, so I tend to think of database structures in those terms. I'm going to have enough to do just keeping up with variations on equipment types. For instance, transformers can have Load Tap Changers (LTCs) which effectively act as regulators to change voltage in response to load changes. If one is so equipped I'll need to keep track of a separate object, along with its own tests and reading history, but never let it get disconneted from its parent transformer. Other transformers are associated with regulators as separate devices, and these can be separated from their parent transformers and installed in other locations. They also use different methods of oil protection - some are sealed, some have pressure tanks of nitrogen to maintain positive pressure, which has to be monitored. Others have oil reservoirs, and the levels have to be recorded in both the transformer and reservoir. I think for now I need to keep it simple, if somewhat inefficient, just to avoid driving myself nuts. Fortunately there aren't a lot of tests that are done, and this industry hasn't changed in 50 years. They still think solid state relays are risky, new-fangled toys. It might be another 50 years before I have to add a new test, and I really won't give a damn by then. :-D One of my primary goals will be to incorporate trend reporting in the test results, once I get the inventory tracking functions in working order. A DB is only as useful as the money it saves you, and it's a lot cheaper to anticipate a transformer failure before it happens. Gas testing in particular is remarkably valuable, though the level of dissolved gasses is less important than the rate of change. This information must be easy to manipulate in the future in order to easily generate views with meaningful content, and while the tests haven't changed, the interpretation of the results has been steadily evolving. I'll look into using attributes, though. It sounds extremely flexible, even if the supporting code has to be more complex. Thanks!

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

[Mycroft Holmes]

Roger Wright wrote:

. Fortunately there aren't a lot of tests that are done, and this industry hasn't changed in 50 years.

In which case by all means create a table for each test type. While not technically elegant it WORKS WELL, the main criteria for any system. I live in a world where the test can change from month to month and they want to restructure the universe every 6 months so I need the flexibility to change rapidly.

Never underestimate the power of human stupidity RAH

[Roger Wright]

:laugh: :laugh: :laugh: I well understand! I started out as a test engineer, developing automated test systems for missiles. When I started, they were requiring us to model the Known Universe at each interface, then simulate every possible thing that could happen there. I argued that we could far more cheaply test the components in-circuit, on the theory that if the designer knew what he was doing, his circuit would work if all the components were within tolerance. If not, the circuit should be changed, along with the designer. In time I succeeded in convincing the US Navy of the merits of in-circuit tests. So they added in-circuit testing to their requirements, but kept the requirement to functionally test everything using the Known Universe model. Go figure...:~

"A Journey of a Thousand Rest Stops Begins with a Single Movement"