How to handle dynamic data

[csutton]

Hi all,

I'm working on a program that each client site can create an electronic form to collect some information from a user. The form can change at any time, so to me, creating a table to hold the information may not work because fields may be removed or added any time. The old fields will still need to be kept for the future for searching.

I've looked at storing the data in a XML file, but because of multiuser issues of the XML file, I don't think that is the best way to go, as well as the speed for searching for data.

I was wondering if anyone had any recommendations or ideas?

Thanks!

 

[alvechurchdata]

When you don't know what sort of data to expect you'll have to force everything to character. One idea would be to have two tables:
[TT]
Main table
RecordType Integer
Field1 Char
Field2 Char
etc

Structure table
RecordType Integer
Field1Caption Char
Field1Type Char
Field2Caption Char
Field2Type Char
etc
[/TT]
For each record in the main table you have to use the structure table to see what caption to place on the form and what type of data to expect.

You might have to extend the structure information to include the type, size, and location of the data entry controls on the form, how to validate the data entered and what to do if validation fails. An object-orientated front-end helps here.

It's horrible. One of those problems where a pencil and a card-index beats a computer<g>.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[csutton]

Hi geoff,

Thank you for the response. The form display is being handled by another program so I don't need to worry about the form structure. What I need to store is the data the users type into the form.

Any suggestions?

 

[alvechurchdata]

I don't think you can design the data structure independently of the form. If every record has a different data structure then the form will osmehow have to know how to display it.

What languages are you using? Might help us get a better handle on the problem.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[csutton]

Currently VB 6.0, but will be moving to VB.NET. The ideal solution would be to store the data in XML because each record can have a different structure and the field names could be enclosed in tags. However, I'm not familiar with any technology that would make XML work properly in a multiuser enviroment as well as being able to index the data for searching..

 

[bassguy]

Hi,
Are you saying ClientA Might want the following fields:

FirstName,
MiddleName,
LastName

and ClientB Might want
FirstName,
LastName,
DateOfBirth


and are you saying as well the Labels to the textboxes on the form need to change?

Please give an example

bassguy

 

[alvechurchdata]

The ideal solution would be to store the data in XML

Do some tests before you make that design decision. Store a realistic amount of data as XML (10,000 records?) and check the speed with three or four users each trying to retrieve a given item over a network.

You're also right to worry about multi-user aspects. I think you'll have to lock the entire file and write your own routines to resolve contention.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ceh4702]

We have files on our mainframe where we have record types and different record types have different record discriptions or sturcture. We use a record type as part of the key for fast access. This is a kind of object oriented approach. You could think of a file structure with a common set of fields that all areas use and consider that a class and have sub-classes for the variable portion of the data. In Cobol we just would redefine the same area for each record type. This way each location has a different sub-record layout, but they use the same space.

On the form you could use different subforms. Then make the right one show in that space depending on a location code.

I am not sure how this would work in a database structure or how you would make a object oriented structure. I dont know if you can link different tables depending on a field (Element) in a table. There are probably some object oriented table structures in some databases.

I could see how you could do this with object oriented design using Java, but it is not a very popular programming language in certain circles. The real trick is having a join of two tables, or a parent table to subtables depending on a foreign key. It would be like joining one table to ten tables and only the table that matches would be displayed. Maybe if each site had a different view you could block out the other sites data elements.

If you do not like my post feel free to point out your opinion or my errors.