I am preparing to do an upgrade of a fairly large program that is currently written entirely in MS Access, both GUI and data (code and data in separate files). One of the key features of the product is that end users can add additional columns to several of the tables at run time. Each new column can be one of several datatypes (string, date, lookup key to another table, integer, etc). The values in the columns can be a value directly entered in, or a formula based off of other columns. The databases are shipped with a few base columns, and the rest are all defined in the field by the various end users.
In the application, which will be either winforms or WPF to start, we will need to bind those fields to controls and collections of rows to grids. Furthermore, there is a table that contains the metadata about the fields.
The base table has a schema something like:
ID as integer Name as string CategoryID as integer Price as Decimal
There will typically be 70-90 additional columns defined by the user. The metadata table has the following (and more) columns for each field (using data for ID after each)
FieldName as String ("Name") Visible as Boolean (True) DisplayName as string ("Item Name") ReportName as string ("Name") DisplayWidth as integer (20) ReportWidth as integer (10) etc (20+ more columns)
All of the metadata is update-able.
Should I use the built in data table class to handle all of this, or would I be better off to go with a base class containing the known fields and dynamic code compiled at runtime that creates all of the appropriate properties. If I go with the dynamic class, should I then put all of the metadata into a custom attribute?