• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Examples of employee database structures

Hi experts.

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.

Thanks
jc
0
johnclarke123
Asked:
johnclarke123
  • 3
1 Solution
 
pcsentinelCommented:
Just have a look at the demo database thats installed with SQL Server called Northwind

regards
0
 
solution46Commented:
jc,

if you're going down this route, you will probably quite quickly find you need to include organisations in your structure. This will...
- allow a single table of addresses that can be linked to people / orgs
- give a unique id for each entity in your organisation and so on.

I have worked on this sort of structure quite a few times (my 'specialist area', if you like, is infrastucture systems) and would suggest something along these lines...
www.solution46.com/ee/PerOrg.GIF

[For the moderators and whoever else... I'll leave thisimage available for the next month or so; if anyone requires a copy of it and it is no longer available, please e-mail ee@solution46.com. Note, I will NOT respond to unsolicited e-mails for assistance.]

Note the optional 1-1 relationships between [PerOrg] and [Person], [Organisation] and [Team]; this allows the [PerOrg] table to hold a unique set of IDs, with expansion in one of the three 'child' tables.

The User / Group / Permission and related tables define atomic group-level security within the application (every action in a sproc has an associated permission; this is checked before the user can carry out the action) and may not be relevant to you.

The ContactDetail tables define things like e-mail address, telephone numbers and so on. Many people prefer to include this as set fields in the [Address] table; we have chosen this format or its increased flexibility and have accepted the hit of additional complexity.

The Organisation and OrganisationType tables allow you to define Customers, Suppliers, internal organisations and so on. If you want to include a hierarchical organisation structure, you can do this with a ParentOrganisationID in the Organisation table (join type 1 ParentOrganisationID - many OrganisationID). If you have a situation where one organistion may be of more than one type (say, both a supplier and a customer), you will need to play around with these tables a bit - there are a number of solutions, which one is better depends on the specific circumstances.

Anyway, that's the bulk of it covered; I hope it is of some use to you. Any questions, please ask.

s46.
0
 
johnclarke123Author Commented:
Thanks for that very comprehensive response, s46.  Of course I do have one or two questions!

Can I check my understanding on these 1:1 relationships?  Is the idea that PerOrg is a unique reference for any entity in the db, be it an individual, organisation or team.  And if so, that the Organisation, Team and Person tables each refer to a subset of PerOrg?  So that, for example

PerOrgID 1,2 and 7 are orgs; 3 and 4 are teams; 5,6,8 are people
So PerOrg would have primary keys of 1-8; Organisation would have primary keys of 1,2 and 7; Team of 3 & 4 and Person of 4,6 & 8.

... and only PerOrg's PKs are set to Identity?

That makes sense to me, but the only time I've tried to put in 1:1 relationships like that, I've always got in a mess with the server trying to impose a constraint that all records in the parent table must have a corresponding record in the child table... if that makes sense, what may I be doing wrong?... could you post the example table scripts if it's about getting constraints right?

I guess also that you'd ideally never actually delete a user, just flag 'deleted date' and / or remove their membership of a group to revoke permissions?

Typically do you find that this kind of structure adds a lot of overhead to queries that reference it?  How do you manage that?

pcsentinel - thanks for pointing me towards the Northwind example - the 'ReportsTo' and 'Territories' parts are useful.

To manage the complexity, I guess I should create some views on this db that are routinely
0
 
solution46Commented:
jc,

sorry for appearing to ignore your post for so long. My pooter keeled over a while back and I've lost all my e-mail back to 23 March (everything else was backed up but apparently not my pst file).

Anyway, to answer your questions...
*** about the 1:1 relationships. Yes, you are correct. PerOrg holds a comprehensive list of all people, teams and organisations. Each of [Person], [Team] and [Organisation] hold subsets of the PerOrgID, but supersets of the actual data held about each PerOrg (i.e. name, DoB and any other details may appear in these tables but clearly not in PerOrg).

*** only PerOrg has an identity? Again, correct. All the 'child' tables use the ID generated in [PerOrg]. A standard 1:1 join (in the diagram window, drag from the PK in [PerOrg] to the PK in [Person], ensure 'enforce for inserts' and 'enforce for updates' are checked) should allow you to enter what you like in [PerOrg] and only related rows in [Person], [Team] and [Organisation]. A rough script would be (air code so check for typos!)...
CREATE PROC spPersonInsert
    @PersonID int OUTPUT,
    @Name varchar(50)

AS

BEGIN TRAN -- inserting into two tables here so we need a transaction in case one insert fails
INSERT INTO [PerOrg] ([PerOrgTypeID])
VALUES (3)    -- assume Person is type 3; this value depends on the contents of [PerOrgType]

IF @@Error <> 0 OR @@RowCount <> 1
-- represents an error; handle it however you wish
BEGIN
    ROLLBACK TRAN
    RETURN -2   -- or whatever you want to do
END
 
SET @PersonID = @@IDENTITY   -- assumes you have no triggers on PerOrg - I've never needed them
INSERT INTO [Person] (PersonID, [Name])
VALUES (@PersonID, @Name)
IF @@Error <> 0 OR @@RowCount <> 1
-- represents an error; handle it however you wish
BEGIN
    ROLLBACK TRAN
    RETURN -3   -- or whatever you want to do
END
ELSE
-- everything worked ok
BEGIN
    COMMIT TRAN
    RETURN -1   -- I use -1 as default 'OK' (0 is a bit of a null return and gets passed even if no RETURN statement is used)
END


*** deleting users: I find it is almost always better to mark a row as deleted rather than physically deleting it. The only real exception I can think of is when you're archiving very old data.

*** process overhead
This does add a little overhead, but comparing it to the amount saved trying to resolve, for instance, address tables for eahc of Person, Team and Organisation would be a lot harder. In most cases, you do not need to link to the [PerOrg] table. For instance, to get the address for a person, you would use something like...

SELECT * FROM [Address]
WHERE [PerOrgID] = @PersonID

The inserts are a little slower but by building both INSERT statements into the same sproc (see above) you minimise this.



Right, I think that's it for now. Please feel free to ask any more questions; I'll answer them despite the question being closed since it's my fault I've not answered them before!

Regards,

s46.
0
 
solution46Commented:
Oh yeah, thanks for the points!!!

s46.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now