Greetings,
I am designing a database which requires some of the fields in the transaction table to be defined by the “customer.” I have four UDF (User Defined Field) [UDF1 – UDF4]. There will be a user interface to specify the labels of these UDF fields. I could control these labels in the application layer but we may have external reporting tools hitting the db, so I prefer the UDF labels be stored in the db.
My question is what is the best design approach to implement this “meta layer?” I have seen similar concepts used for multi-lingual solutions but can’t seem to find resources describing how to implement this type of solution. SQL2005/2008 will always be used.
One idea I had was to use a “Terms” table (ex below) and then in a view or SP, I can link the user defined labels to the UDF columns in the trans table. But I am not sure if this is a reasonable approach.
Example:
Trans table:
structure: ID, VENDOR, DATE,UDF1,UDF2,UDF3,UDF4
Terms table:
structure: TABLE, FIELD, LABEL
data: trans, udf1, Contract
So in this example UDF1 = Contract.
Thoughts on best practices or links to resources are much appreciated.
Regards,
Mike
I am designing a database which requires some of the fields in the transaction table to be defined by the “customer.” I have four UDF (User Defined Field) [UDF1 – UDF4]. There will be a user interface to specify the labels of these UDF fields. I could control these labels in the application layer but we may have external reporting tools hitting the db, so I prefer the UDF labels be stored in the db.
My question is what is the best design approach to implement this “meta layer?” I have seen similar concepts used for multi-lingual solutions but can’t seem to find resources describing how to implement this type of solution. SQL2005/2008 will always be used.
One idea I had was to use a “Terms” table (ex below) and then in a view or SP, I can link the user defined labels to the UDF columns in the trans table. But I am not sure if this is a reasonable approach.
Example:
Trans table:
structure: ID, VENDOR, DATE,UDF1,UDF2,UDF3,UDF4
Terms table:
structure: TABLE, FIELD, LABEL
data: trans, udf1, Contract
So in this example UDF1 = Contract.
Thoughts on best practices or links to resources are much appreciated.
Regards,
Mike