Timesheet solution for SharePoint 2010 - Part 1

Part 1 - Design & data storage (This blog post)
Part 2 - Building the UI
Part 3 - Installation Steps
Part 4 - Final Part

Problem:  A common requirement on Intranets is to have a timesheet template on SharePoint.  There are solution for company timesheets.  I wanted to build a timesheet application that was scalable, reportable and friendly for SharePoint. 

Initial Hypothesis:  There are timesheet solutions for SharePoint and most are portable to SharePoint 2010.  Often the timesheet solutions integrate with Outlook as well.  If you have outlook this is often the best solution as users seem to accept Outlook based solutions fairly easily.

I came across http://www.pointbeyond.com/, there timesheet solution was built for WSS3/MOSS, this looks like a solid solution based on SQL Server so their is no real reliance on SharePoint lists however, SharePoint surfaces the timesheets, administration and reporting requirements.  I have not implement the solution but it appears solid and expandable.

In WSS3 I think it would be very difficult to build a detailed timesheet application based on SharePoint lists due to the list size issue and the list relationship complexity required for a timesheet application.

Resolution:  I want a SharePoint Foundations solution that can be used on all SharePoint 2010 farms that can capture and administer timesheets for a business.  SPMetal has greatly improved application development when using SharePoint lists to store data in SP2010 however, due to the number of relational lists and the support for transactional CRUD operations I believe it is better to use SQL Server and store the data in relational tables.  Custom connected web parts will allow users to enter their weekly timesheets.  The solution allows for the use of BCS and External Content Type (ECT) in SP2010 for administration and reporting.

The data access technology choice is between Linq to SQL and using the Entity framework 4.0.  As SharePoint 2010 supports .NET 3.5, a separate Visual studio project/business layer would be required.  Additionally my take on these 2 competing Data access technologies is, if it's simple then use Linq to SQL, if it's complex and could change down the line use the entity framework.  I choose to use LINQ to SharePoint as I am using 7 tables to store all my timesheet task related data.  Below is the Entity Relationship Diagram (ERD) for the timesheet application.

Part 1 - Design & data storage (This blog post)
Part 2 - Building the UI
Part 3 - Installation Steps
Part 4 - Final Part

More Info:
Updated: 25/02/2011 Laura Rogers wrote this article on the time card that is useful.
http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=113

BCS Limitations

A good post by Chakkaradeep Chandran on the limitations of BCS

SharePoint 2010 BCS workshop with Scot Hillier

Overview:  attended a workshop on Business Connectivity Services (BCS) given by Scot Hillier in Den Haag (The Hague).  I have used BDC in MOSS and the BCS in SP2010  briefly.  I can honestly say I am now a BCS convert.  I know BCS was an improvement on BDC but looking at the tooling and going building advanced BCS solutions is really powerful and covers a lot more that I understood or had seen in a previous demo.

Summary:

• Office can use BCS - pretty obvious but I didn't realise the client application goes straight from the client application i.e. work/outlook to the SQL/WCF/.NET data type directly.
• Double hop issue with NTLM.  Scot laid out a table to direct the options available for various scenarios that I will hopefully post here.
• Secure Store Service is extensively used for authentication in BCS.
• SPD has a good tool set for BCS.  Probably the place to start.
• SQL 2000 doesn't work as an External Content Type (ECT) - this needs verification.
• BCS doesn't support transactions and is not strongly typed however data access options can be intermingled to get transaction functionality if you need it. 

http://uscatu.wordpress.com/2010/09/30/business-connectivity-services-deep-dive-mdvwrk1-%e2%80%93-my-notes/  A far better summary was written by my fellow attendee Razvan-Petru.

BCS versions feature table

BDC is replaced by BCS in SP2010

• Business Connectivity Services (BCS) is the replacement for Business Data Catalog (BDC) (MOSS 2007). Brings external data into sharePoint. External data source can be SQL, or Web Services, so it is pretty easy to have CRUD Access to databases, ERP or third parties.
• BCS has read and write support.
• Both SPD (SharePoint Designer) & VS 2010 have tooling support for entity modeling to connect your line of business application (LOB).