Separate Databases?

[jk42]

Hi All!

Thanks for all of the help in the past month or so. I still have a lot of learning to do, but I think that for now, I can actually use the database. My next question is just a general access question. I created a database that has all of our project information in it, and in that database there are details about some of the staff (not all, only those who actually work on the projects). Now my company wants me to create a "Human Resources" database so that we can store employee information. Does this mean that I should have an entirely NEW database file, that's completely separate from the project one? I'm not familiar with how that would work, as I have literally been spending months just trying to figure out how to put this one together. Will it be that every time they task me with a new subject for a database, for example, tracking contracts, will it be a completely separate file? Does my question even make sense?

Thanks!

 

[DavidAtWork]

If the HR data held is going to be fairly basic, I'd just create new tables in your existing DB as it sounds like you already have a good base to start on. It really depends on how big the current DB is, but from your brief above it doesn't sound too big.
You need to consider what is required from the HR data aspect in terms of reporting and database functionality as that will be your key to the required table design.
Some of the HR data will be sensitive, so you'll need to consider the security aspect and how to control who has access to what data.
The HR side can be considered as an additional module to your existing DB where HR related forms are only visible/enabled to certain users

David

 

[Galaxiom]

Always be careful to resist the temptation to continue with what you have without stepping back and looking at the new specification as though it was a completely new project. Otherwise the potential to restrain one's thinking can result in a compromised design.

Even if this process does suggest a completely new data model there will be many pieces of the old design that are easily adapted especially if your previous work included measures to aid its portability.

Short changing the fundamental data analysis in favour of adaptation can sometimes be a disaster.

Probably the best evidence on the planet for this fact is the Queensland Health Department Payroll Disaster which failed completely after a decision to adapt an existing application to something its data model was never intended to do.

They took a nine-to-five payroll system and tried to adapt it to a health system workforce where the ground staff worked bizarre hours and much of their remuneration was based around shifts and special rates for being on call etc.

I honestly believe that there are many here among us who could have advise against the strategy on the basis of less than a week's consultation.

Where they failed was starting with something they had instead of focus on the fundamental data model. As we see here, getting that wrong results in ever increasing clumsy workarounds.

In this prticular case so much was already invested that nobody dared to say anything until the bibble burst on the first payday. Man doctors and nurses went unpaid for weeks.

Literally billions of dollars went into this development. (Remember this is a payroll system.) The failure and the investigation both cost many millions more.

Sadly everyone still talks in terms of "fixing" it.

 

[Galaxiom]

The backends for databases with personal informtion such as HR really should never be hosted in Access because users can simply copy the file and take it offsite. Then they can try to break in at their leisure.

Moreover, if the password is stored in the front end it takes less time to get the backend password from the file than it takes to open the real database.

This kind of data should be hosted on a database server such as MS SQL Server which not only prevents any unauthorised access to the file but allows fine-grained control of user rights.

Individual tables and even individual fields can be set to deny read, read only, update, insert, delete on the basis of the user or user group. Then it doesn't matter if you mix the purposes and referential integrity can be maintained properly.