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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
Oh yeah, thanks for the points!!!
s46.
s46.
regards