SharePoint Governance through Database Design Considerations
Governance is so key to deployment. I still hear of people that "throw a deployment over the fence" to an untrained, unexperienced IT guy or gal to "deploy" it for them. A few days later, WSS 3.0 is installed on a server or two and a web app provisioned with a site collection likely with a Intranet portal or default portal created. The business plays around with the site, asks for permissions adds a bunch of people and gets the IT person to allow them to create sites. They turn on SSC (Self Service Creation) and IT supports whatever happens next.
If the business wanted a database would they throw over a request and say... hey give me a table, and allow me to put in some data? I guess that happens too.
Here's one way to break down some concepts in relation to database design. These examples may help you work with your business to help them understand it's not install and walk away.
SQL - SharePoint
Integrity - Quota
Data Constraints - Columns
Data Types - Content Types
Tables - Lists
Database - Site Collection or Web Application
SQL Job - Timer Job
Stored Procedures - Workflow/Features or Solution Deployments
Trigger - Event
DBO - Site Collection Owner
Model Database - Site Template
Master - Site Definition
The business define business requirements that will help with the overall design. They should work with the IT Professional/Architect on the look and feel (Master Page), the navigation, the site topology, top level sites, work out what teams should have access to what roles/permissions levels. The SLAs (service level agreements), uptime requirements, supportability, accessibility, minimum performance levels, how often to backup and retention levels. There's obviously some give and take. Even in something like how many sites and the size of sites for example. These things do affect performance. For example, putting all the data in a single list will have impact on your performance. Don't forget to performance test your hardware, and your software. It's amazing how much a database driven custom flyout menu can impact page load performance for example.
One conversation that IT and the business should have is supportability around customizations. Building composite applications on SharePoint technologies can be extremely powerful, but what about upgrades? What about service pack installs and maintenance involved? It's amazing what you can first do right out the box, I know the blue bothers some people, read my post on that to change the colors and look and feel. Beyond that consider partner solutions that will support their application through upgrade, consider features vs. changing site definitions. Never change the out of the box site definitions or modify the database directly to maintain supportability. It is an amazing platform, I'm just recommending you think through this and even consider what portion of the buget will be retained for the upgrade of whatever app is being build on the server as an example.
So, as we look back at the database design as an example... You should think about the data and the structures of the data and understand your requirements before you jump in to throw your database out there. It's always easier to create the columns and data types so that you don't later have to truncate your databases and start over. How hard is it to later add your primary keys? Much better to start with these same concepts in your SharePoint environment... quotas, content types, and identify who should have rights to create sites and what site templates you'll support. Missing functionality?... yep features and solution deployments. That's governance.
