A database is used to store data. The most important design feature is that this data should have integrity i.e. should be correct and self consistent. A secondary consideration should be access efficiency; there is no point in building a super-fast database if it cannot be relied upon to give the correct results. To this end the data should be held in a normalised structures. It is important for the designer to know about normalisation and it's relationship to the business structures it must support. Once these structures are built to hold the source data other denormalised structures may be necessary for efficiency but this should be considered as redundant data and although should match the data in the source tables it should be possible at any time to rebuild them.
SQL server has a few system databases which should be considered as the domain of the DBA.
It is advisable not to include user stored procedures in any of these databases except Master into which should be placed stored procedures that you wish to be accessed from any databases without database prefix. These are usually monitoring stored procedures used by the DBA. Note that these need to be prefixed with sp_.
Note that a sp_ user stored procedure in master does not act the same as a system stored procedure. With a system stored procedure the copy in master is run in preference to a user stored procedure in the source database, for a user stored procedure the copy in the database is used.
Database Access - Stored Procedures
Database access should always be performed via stored procedures. This has several benefits.
The query plan is usually cached at first run and is used for future SP calls (dependant on nature of SP).
User access is given to the SP rather than the underlying tables (unless using dynamic SQL) so making security easier to maintain.
Testing is easier as you can run a stored procedure and check the results from query analyser without having the client interface distort the test. In fact I always include test calls to the SP in comment blocks in the SP.
The most important feature is the isolation it gives from the client. The database is a store of data. This data will be stored in a fashion that is convenient for the database not the client. Stored procedures give a means of bridging this gap so that the minimum amount of data may be returned. If it is discovered that the database is no longer efficient enough for the business - due to changing requirements or changing amounts of data then the database structure may be changed, the SPs changed to fit in with the new structure and tested then implemented - all totally transparent to the client application.
Development Procedures - SouceSafe, Scripts
(I use SourceSafe here because it is most common but this applies to any source control application)
Never create or amend objects via enterprise manager, this gives no audit trail and any changes can easily be lost.
Create a SourceSafe directory for the database with the following structure.
This is the source for the structure of any database.
The method of amending any object is
Check the script out of SourceSafe
Amend the script in query analyser
Save the script
Run the script on the development database
Check the script back into SourceSafe when complete
This has several advantages.
If the database is lost (possible due to testing the changes) then the changes are not lost as you still have the script.
There is an audit trail of changes.
Checking out of SourceSafe stops two developers working on the same object and losing changes.
It gives release control (see later section).