Wiki Home

All Table Fields In One String

Namespace: VB
I am creating a new database for my company. The data we receive is in text files (SDF format). We have many different customers that send their data in different formats. In order to remain flexible, my idea is to create tables that store the data from the text files as one long string for each line in the file (no lines exceed 254 chars), together with a key for each record. Then I have a data dictionary table for each file type that would identify the positions and lengths of each field. This would allow customers to have custom defined fields as well as add and remove fields as needed.

My question: I am using views to break the string into fields when viewed by our support people. VFP is very fast at breaking down the string into fields. But can anyone think of a simple way to update the original string when someone edits a field in the view? I am trying to make the views as flexible and responsive as possible, so I would like the ability to add, delete, and update records in the view and have these changes reflect in the original tables. Thanks.

Sebastian, I have two ideas:
#1, Get inventive, and use a Cursor Adapter
#2, If you can get "exclusive" access to the file, then you can just delete the file, and use COPY TO ... SDF
However, you really need to be more specific - are other users accessing the file at the same time, what kind of file size are we talking about, how are you presenting the data, etc. The only way I can think of to interface directly with changes to a "view" is a Cursor Adapter, but you must have some method of presentation that can be "hooked into". -- Peter Crabtree
I agree with Peter: Cursor Adapter. You will want to use the Stuff function.

However, I don't think your overall plan is good. What is the advantage of storing all the data in one table if it has a different structure/fields?

Here are a few ideas:

1. one big table - all fields that are used by any customer, store null if they don't supply data.

2. 3 little tables - Parent, Child, Lookup.
Parent holds data common to all records: PK, company, etc.
Lookup is a list of Field Names (all field names that are used by any customer) - 2 fields: PK, Field Name
Child holds the data from the files: PK, Parent_FK Lookup_FK, Value.

3. 1 Main table and a seperate table for each structure (company.)

Thanks for the tips. Since I am going to VFP 8, I think I will use the Cursor Adapter. I'll have to read up on it though. I was a little concerned about storing the data this way. But I have found the following advantages to outweigh any other alternatives I have come across:

Flexibility: Each mfg can have different different file formats (e.g.. B73 vs. R73, etc.) without the need to create different tables. Since they send in their data as text files the data can be loaded directly into the tables, with no or very little conversion.
Ease of Maintenance: Fields can be added and removed with no table structure changes. The customer could even add or remove fields on the fly using a web interface.
Performance: Each file table will only have 2 or 3 fields. Once loaded into a table, VFP can parse the data very rapidly. Views can be used to break the string into fields. Initial performance testing indicates that a view of around 100,000 records can be loaded in under a second. Upgradeability: This simplified data format is ideal for upgrading to SQL Server, while still keeping the system tables in VFP.

This application will eventually interface with the web and I need maximum flexibility as far as data formats and file formats. If anyone sees any flaws with my thinking here. Please let me know. This is a pretty radical change for my company and I want to be sure I don't go the wrong direction. Thanks again.

Sounds OK to me. While I prefer more "complex" formats for advantages like variable-length fields (memos), etc, for a simple fixed-length 2-5 fields, with a CR record delimiter, it's a format that is easy to write to, easy to read from, etc. However, if you are in complete control of the data, you could just use a bunch of free DBFs, and use ALTER TABLE commands to add fields, etc. -- Peter Crabtree

I have 2 problemes with your thinking:

1. "...without the need to create different tables" - but you need to create different views (right?) I am guessing new edit forms too.

2. "no or very little conversion" - the conversion is done in the view.

3. you have to convert edits back into the 'native' format, something that would not be an issue if you stored the data in the converted format.

Given the headaches that you are taking on (complex views and who knows how you will update the data), I am not sure I see any trade off - What are you gaining? So far, I only see: Fewer disk files. - cfk
Category Data Modeling