Wiki Home

User Defined Fields

Namespace: VFP
Problem: You want to provide your user with a database designed to allow the addition of optional or user defined fields.

Solution 1: In your given master file you add a fixed amount of fields to be used as optional fields.

Pros: An easy solution to implement. (not maintain or extend)

Cons: Unless you are prescient you do not know what kind of optional fields your client will need. If you attempt to give them say 2 character fields and 2 date fields you will most likely come up against the problem that they want 3 date fields, 1 logical field or some such. If you create all of the fields as character type you can store what you want (assuming you know what the size is) but you'll need to create unique conversion routines for each type.

Conclusion: Don't do this. Though if you've ever worked in a maintenance situation its likely you'll come accross this solution.

Solution 2: Create a table to hold the optional fields, and set a relation between the master table and this table.

Pros: Flexible, you can have as many optional or user defined fields as you want. You could create something as simple as a table with one primary key and a memo field to hold all of the optional values. If you use a CR after each value you can use Alines() to parse the data to an array.

Cons: You could create a table with a field for each type allowed, but this would be a waste of space. In any case you will still need to come up with a way of parsing the optional field if in a memo field, and defining what the field contains and what type it is.

Conclusion: Up until this point the best solution for this problem?

This is the format I'm currently using. I have a table called Client which looks like this:

iClient_id i (primary key)

And then I have a table called ClientInfo that looks like this:
iClientInfo_id i (primary key)
iClient_id i (foreign key)
iInfotype_id i (foreign key)
cValue c(30) (the actual value)

In addition I have to create a table to describe the contents of the ClientInfo table, and it looks like this:
iInfoType_id i (primary key)
cType c(1) (Logical, Character, Numeric, Date etc.)
cCaption c(30) (The caption for the field)
mValues (a memo field containing allowable values)

Solution 3: Similiar to the memo option of number 2 but use XML to describe the information.

Pros: You continue to enjoy the flexibility of solution 2, and you don't have to write a routine to parse the data. In addition the data can be pretty self descriptive with the right tags.
See 'Build a Generic State Manager with XML' by Rick Strahl in March 2001 Foxpro Advisor

Cons: ?. Performance hit?, you always need to parse the data before you can use it.

Conclusion: Prompted by Rick Strahl's great article I'm currently looking at this solution. The big advantage to this over the memo option of number 2 is that the data would be self describing both in content and type. Has anyone used it yet?

Solution 4: Use a single AttributeValuePairs table that can extend many entities

Solution 5: Create a table with the user fields as normal fields, created in code.
-- Peter Stordiau
Note : Solution 2 looks as if it is the same described, but I think it isn't.

What about just creating the table as how we do it ourselves as developers ? I mean, Create Table (when a first new field is needed) followed by Alter Table xxx Add Column for the primairy key (iClient_id), perform a Index On (once) and further adding columns for the user defined fields in the same way, each time a user field is needed. Of course the allowed values must be incorporated somewhere.
Though we use user-defined fields all over the place, we never did it this way. But IMO this should work and is more "real". The "user-tables" should have their name prefixed with the name of the original table, and the opening of the user-table must be performed somewhere in the framework.
BTW, this may be extended to the triggers etc. in the DB (I mean : have the value-domains overthere), though I don't know whether it is possible to create this in code (manipulate the Fox-tables ?).
Is this a solution 45 ? Am I really wrong somewhere ?
Note that the other solutions allow for different user-fields per client (!!) which a. is more flexible but b. is not so much for normalization. This solution 45 doesn't allow for that and may end up with the greatest common divisor (?) of the defined fields for all different clients. However, it may me the easiest way to map a runtime-created form onto it.

Pros: A rather normal way of working, optimal performance, allows mapping of forms easily, is generic, easily accessible for the end-user (SQL), allows for easy access by the developer to write coding on top of user defined fields (is that allowed ?), propably implies the less not-used bytes possible, is rather formal, allows for Indexing on user fields theoretically, can be integrated rather easy with existing statistics (by end-user or developer), allows for all the existing table-commands, field-types are formally there (of any kind). (note : a few overlap).

Cons: Needs a high level of integration with the framework (is that a con ?), doesn't allow for other fields per client (or whatever entity) (is that a con ?), needs extra stuff for the value-domains which may lead to another kind of solution (two other procedures is a wrong-way) unless it can be incorporated in the DBC via code.

Conclusion: Yes, I like this, but since I never tried, I don't know what I'll run into for the overall solution.
What about storing of data in the single memo field in the comma-delimited format? Just a few commands to get a data: 'STRTOFILE()', 'create cursor' and than 'APPEND FROM'. You require, however, to know a format of the data, that could be determined separately by the type of the data (type of record), or just stored in the same record in separate memo field ready for use by create cursor command.
-- Vlad Grynchyshyn
Vlad I haven't explored this option yet, but I think the XML solution might be better in that each field in the memo would be self describing as to its type. Michael Chean
You're correct. However, XML still require parsing that is slower than just direct import of data from the comma-separated text. -- Vlad Grynchyshyn
Thanks for the refactoring!
Category Data
( Topic last updated: 2001.05.11 05:47:45 AM )