I need to build a system whereby the user of a web map (the standard web map that ArcGIS Server Manager builds) can edit a feature class's attributes. The attribute will be a 'Comments' field. The feature class will be replaced quarterly but the 'Comments' field must persist across quarterly updates.
I am thinking to build a stand alone table in sde (MSSQL 2008) and let users of the web map update the feature class's 'Comments' field directly then have a db trigger fire which will write to the stand alone table.. the unique id of the record in the feature class and also the comments edit. This would be to retain the 'Comments' field apart from the feature class thus persisting the comments across quarterly updates of the feature classes.
I have already built the standard web map from AGS Manager and created the feature service of the feature class and tested the editing of a 'Comments' field. Now I am starting to build the persisted 'Comments' architecture.
Also, there may be multiple 'Comments' attributes inserted over time, I am uncertain as to whether I should have multiple 'Comments' fields in a stand alone table OR have multiple records in the stand alone table with each comment and the unique id and do a one-to-many join to return them in the web map. I also think deleting comments will be important.
Does anyone have any suggestions or tips or alternative approaches? If the T-SQL approach, are there any tips on T-SQL and SDE?