I'm developing a variety of SQL Server solutions across my SME. I've come to the conclusion that I need a single employee database that will store contact details, user IDs, access rights and other fields used by the apps. As a side benefit it could serve as a basic HR management tool.
Does anyone have some good examples of simple relational DB structures that would suit this need? I recognise that a single flat table could do the job, but it's going to be limited pretty quickly. However I don't want to go to the nth degree in developing a complex RDB with all the procs and views this entails, since my target is to develop the core apps, not spend ages on an enabling DB.
I'm using Active Directory at present to manage general access rights, but I reckon a SQL DB would provide a lot more funcitonality.
Examples of the kinds of queries I want to run on it would be:
UDFs that return employee name based on a primary key ID;
SPs that send automated emails or SMS depending on employee membership of a group;
views that return sales consultants whose territories cover given post (zip) codes.