I have a table called 'Fields' that contains application fields. In this table I store metadata for each fields (TableName, ColumnName, JoinType (inner/outer).
I have something working but it's not as clean as I'd like.
Has anyone tackled this before?
I'm looking for some ideas around better practices.
I wrote a query generator in the past, that could join tables automatically and put things together to generate dynamic reports.
I think, today, I would take NHibernate. Although it is a ORM, it manages joins and creates the queries according to the mapping data that is quite similar to your meta data.
Of course you still would have to do specific things that are not covered by NHibernate. But it's an excellent start.
Dynamic data / fields are always fun.
The way I've approached this before is to have a table which defines the fields I want. A very basic example is:
GroupId int, <- allows me to group fields together for a common purpose
FieldId int, <- unique identifier for joins
FieldName varchar(100), <- obvious
DataType int <- joins on a table which contains available types such as text, phone, email, that may have special processing characteristics.
DisplayOrder int, <- what order will the fields be shown on the screen.
Then I have another table to hold the actual data: EntryId int, <- Groups the values to a unique entry point. GroupId int, FieldId int, Value varchar(max) <-use whatever number you feel will contain the data; or max if you have the right version of sql server
Finally, I dynamically generate views which turn the actual data 90 degrees for easier reporting.
By doing it this way, you have a lot of freedom on what you're collecting without modifying the underlying code.