How to design a Master-Detail Sharepoint 2010 application?

I am in the process of migrating an Access application to Sharepoint 2010 (Enterprise). I would like to use as much Sharepoint "out of the box" funcationality as possible, but I am not opposed to creating some Web Parts.

I am struggling with the design of the "master" table in this application. The application is used to track employee productivity. Daily, about 50 users access the application and basically enter how many "Widgets" they completed that day. There are about 30 types of these "Widgets" and they don't change very often.

The table is designed with individual columns for each of these Widgets. This makes creating the Reports very easy, since all you have to do is select all the fields from the table and dump the result set.

The downside to this approach is obvikously the fact that the schema is "hard coded" (static). I have been asked (in the sake of time) to just normalize the table as much as possible (with CustomerIDs, EmployeeIDs, etc), but keep all the "Widget" fields in there.

I had proposed that we create a Master Detail type relationship where the users would Add a Row (perhaps in a GridView), select the "Widgets" they created that day (from a drop down) and enter their quantity. They generally only make 1 - 3 types of Widgets per day.

The users hate this design and want me to give them a data entry form with ALL the widgets displayed so they can just click in the box (beside the Widget they created that day) and enter their qty and then click save.

I know I could still create this type of Data Entry For开发者_运维问答m with a Master-Detail type of relationship, but I am pretty sure I couldn't using the SP Out of The Box forms. I would probably have to create a Web Part with a GridView and just populate the GridView with all the possible Wisdgets, then let the user enter the proper Qty(s) beside each widget they are made that day. Once the form is submitted back, I would then have to go through it and find any Qtys that are valid Numbers and add a (child) detail record for that Master record. (The Master Record would contain date, employee, customer, etc. etc.) The "edit" form would also have to work in a similar way.

This is a pretty "ugly" solution and I was looking for an alternative.

If I can't come up with a good alternative (and convice my manager that the code won't be too difficult to maintain or add too much development time to the project to complete it on time) then I will have to bring over this ugly, existing schema with all its wasted space and have "hard coded" stuff thoughout the application. (For instance, if I provide them with a SharePoint View to see how many Widgets of a certain Type were created, I will have to "hard code" all those values in the Drop Down and "Sum" the correct/matching database column. YUK.

Another consideration is the reporting. Right now all the reports just contain a column on the report for every widget. To preserve the look of these reports (if I use a Master/Detail relationship) will require "fancier" queries (Stored Procedures) to buld the proper result set in a "columuar" format. (And I am not sure how I would tackle laying out the SharePoint Views of the data in a similar fashion.)

It certainly would be "easier" to just leave the schema as is (and have all that wasted space in the table). I just hate developing an application that anytime we need to add a new "Widget" to the application, we have to change the application in several places and rebuild. (Although, my manager isn't concerned about that, he just wants to push it out, ASAP...sigh...)

Any help/recommendations on how to do this type of application (specifically how to create the data entry forms and views) in SharePoint would be greatly appreciated!

Shayne

Have you looked at these ideas:

http://paulgalvinsoldblog.wordpress.com/2007/12/24/implementing-master-detail-relationships-using-custom-lists/

http://blogs.msdn.com/b/alexma/archive/2006/04/10/610934.aspx

In my opinion you should be storing the data in list rather than SQL server. If you decide to use SQL server, look at BCS to build Master child view.