Interesting Table Design Idea for Critique (1 Viewer)

[dynamictiger]

In my current application I am trying to open up the options to the user. I am trying to decide the best approach to this issue.

The user can set up a group of water tests they want to make on any swimming pool, these could be Standard, Green, Stain etc.

Any of these groups could require analysis of any chemical parameter, standard ones like Chlorine and pH although I want the user to be able to define their own, so we could have Phosphates or Sulphates or what have you.

Using a table structure like:

TestDate
Text1
Text2
Text3
...
ReportType

I can use ADO to iterate through the appropriate SQL and change the field names to suit.

However, the problem with this approach is that the subform showing past records can become out of sync with the main data entry form. This is even more pronounced if I requery the subform for the report type chosen, as the order of fields may change, but the data does not.

So I got to thinking about alternative approaches - and this is where you come in.

I could ignore the anomolies and say okay this is allright most of the time - don't worry about the odd out of sync.

Alternatively perhaps I should build tables for each report type and tie back to them and only record the relevant records in the relevant tables rejoining the form and subform as required to the appropriate recordsource.

Or perhaps I should set up a table with 6 common fields bound and only give the user control over two or three fields.

Or some other alternative I have not thought of.

I would appreciate anyones thoughts, ponderings, musings, or wonderings on this.

 

[neileg]

Depends how you are going to use the data you collect, but I think you are going the wrong way by changing the field names. Also, chaining the results as a string of firelds in your record is very messy, especially as the number grows.

I would suggest you look at holding the test name and the value of the test as values in a table, e.g.

TblTests
ID ; This will link to your pools table, or inspections or whatever
TestID ; This is the name of your test. This could be freetext, or a list box
TestResult ; The value of the test

This enables you to have an unlimited number of test types and an unlimited number of tests per pool inspection.

You could have a 'halfway house' where you have a few set tests in your main record, and the user selcted tests in the separate table (but I would'nt).

 

[The_Doc_Man]

There is such a thing as being TOO dynamic, tiger.

If you change the field names, you kill every report built using the field names.

I would take this approach for your problem.

1. Table tblPool
--PoolID (prime key)
--particulars of the pool such as owner, address, size, etc.

2. Table tblTestDef
--TestID (prime key)
--TestLowGoodValue
--TestHighGoodValue
--TestName
-- Labor cost of this test (but see later for materials costs as a separate break-out).

3. Table tblTests
-- TestID (foreign key linked to tblTestDef)
-- PoolID (foreign key linked to tblPool)
-- SchedDate (when the test is/was scheduled)
-- ActualDate (when the test occurred or nil if hasn't happened yet)
-- Test Result
-- Billing flag (Yes/No, has the customer paid for this yet.)

Now, you can link up the pool with a test in the future (ActualDate 0) and look up all past tests on a given pool (ActualDate not 0). To add a new test, you make an entry in the test definition table. If your working form of possible tests is based on a drop-down list keyed from the test definition table, then adding a new definition to that table makes it available for being scheduled the moment you update the definition.

This would also allow you to build another few tables....

4. Table tblTestProc
-- TestID (foreign key linked to tblTestDef)
-- StepNum (Autonumber or your choice of numbers)
-- text describing step.

The prime key for tblTestProc is the combination of TestID and StepNum

5. Table tblTestSupplies
-- TestID (foreign key linked to tblTestDef)
-- StepNum (foreign key linked to tblTestProc)
-- InvNum (perhaps linked to a general inventory table) to name the supply item needed.
-- Amount needed for test step as noted.
-- Cost for use of item

The prime key for tblTestSupplies is the combination of TestID, StepNum, and InvNum.

6. Table tblInventory
-- InvNum (prime key)
-- description of the product

From here, you can get into such exotica as a supplier table listing all of the places you can get each inventory item or consumable supply item. You can write out a bill of materials based on the join of the tblTests entries with an Actual Date not 0 and a billing flag set to No JOIN tblTestSupplies, with appropriate breaks. You can write out a checklist of tests based on a join of tblPools and all tblTests with 0 ActualDate, group by (and Break Report On) PoolID, each test in the step becomes a detail item.

Of course, I'm noted for some rather profligate designs, often more than some people would care to implement, but you asked how we would do it. There's your answer.

 

[dynamictiger]

Thanks for the replies.

However, I have not perhaps been clear in communicating this design and how it impacts on the balance of this application.

The reason for Text1,2, and so on is that the records are not bound to a name at all. The name is only manipulated in ADO not the control source.

In attempting

TblTests ID ; This will link to your pools table, or inspections or whatever
TestID ; This is the name of your test. This could be freetext, or a list box
TestResult ; The value of the test

I find myself in a dynamic cross tab query situation, which I do not think can be done, to display the past results based on Client, as a subform.

Doc_Man I already have a three quarters working structure, and I assure you it is much more elaborate than the example you show. I appreciate the input.

However, I am still wondering about how to display in a subform, datasheet the previous results if I follow the path as laid out.

 

[neileg]

Mmm...

You are asking for help in solving your solution, not in solving the problem. You asked for alternative approaches, which you received! I'm sorry this involves scrapping what you have already done.

It reminds me of a joke:
An Englishman was driving through the Irish countryside and became lost. He stopped and asked directions from a local man, saying "Is this the right road for Dublin?"
The Irishman replies "Sure, if I was going to Dublin, I wouldn't be starting from here."

 

[Pat Hartman]

dynamictiger, you are fixated on a spreadsheet type approach. Take the advice offered by the other posters and switch to a normalized relational table structure. You will have a lot less trouble in the long run. You can always use crosstab queries to make reports that look like spreadsheets. The Solutions database even has a sample for a dynamic crosstab report.