?
Solved

Examples of employee database structures

Posted on 2005-04-01
5
Medium Priority
?
500 Views
Last Modified: 2008-02-26
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
Comment
Question by:johnclarke123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13681655
Just have a look at the demo database thats installed with SQL Server called Northwind

regards
0
 
LVL 9

Accepted Solution

by:
solution46 earned 2000 total points
ID: 13687253
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
 
LVL 4

Author Comment

by:johnclarke123
ID: 13693933
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
 
LVL 9

Expert Comment

by:solution46
ID: 13737394
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
 
LVL 9

Expert Comment

by:solution46
ID: 13737406
Oh yeah, thanks for the points!!!

s46.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question