Database design help - I need Pro's opinions for a Personnel Information System
I'm currently trying to build a Personnel Information System using Java, and Apache-Derby as my database. The system is composed of three parts - the database application, the server application and the client application. The client applications run on different machines in a LAN, and they could access the database thru, and only thru the, server application with an embedded apache-derby database.
My problem here is that I am not a pro in database design and I just want to know If I am doing the right thing with my database design concept. So, let me illustrate how I want the system to work.
Before a user can access the database, of course, he must log in first by providing his username and password. Each user has his own access privilege, and for all entries that are recorded in the database, I want to record the name of the user who entered it. All entries of these users cannot be considered as valid entries without the verification of a user with a higher access privilege.
Now let me proceed to the tables in my database. I will only show a few tables just to illustrate the concept:
The first table is the users table, it contains, the user name, password, and access privilege
USERNAME PASSWORD ACCESS_PRIVILEGE
paul fjdksajo 1 //level 1 access
jean eim32d 1
cathy vio334fff 2 //level 2 access
max fjdoe3344 3 //level 3 access
Here is what the access privilege levels mean:
level 1 - can enter information, but entries will not be valid until verified by
higher level users.
- cannot delete entries but can mark entries for deletion, and
all entries marked for deletion will only be deleted once verified by
higher level users
- cannot grant access privileges
level 2 - can enter information, and approve entries of lower level users
- can delete entries that are marked by lower level users
- cannot grant access privileges
level 3 - can do anything.
The following is a sample table in the system. It is the table that stores information about the current status of the user. It is a log of users' status.
ID STATUS BEGIN_DATE ENCODER VERIFIER
001 full duty 28-Nov-2011 paul cathy
003 full duty 12-Dec-2011 paul cathy
001 on leave 28-Nov-2011 jean cathy
003 hospitalized 10-Nov-2011 paul max
005 full duty 28-Dec-2011 jean NULL
The following is the explanation for the columns above:
ID - The ID of a personnel in the organization. This column references the
the primary key of a personnel table in the system which contains
other information about the personnel(ex: name, lastname, age, etc)
STATUS - The status of the personnel which started on the date specified in
the "BEGIN_DATE" column. Only certain values are allowed on this
column as specified in the table's constraints clause.
BEGIN_DATE - the date when the status began. Not when it was logged.
ENCODER - the username of the encoder, referencing a user on table USERS
VERIFIER - the username of the verifier, referencing a user on table USERS
As you can see from the table above, in the first entry, the status of personnel with id "001" was logged by user "paul", and that entry is verified by "cathy". On 28-Nov-2011, personnel "001" changed his status to "on leave" and it was logged by user "jean". Said entry was verified by "Cathy". Personnel "005" was on full duty status begining 28 Dec 2011, and his status was logged by user "jean". The entry was not yet verified so the column for that entry is NULL. Entries with NULL verifier will not be included in the reports.
The system will be used in a place where frequent power failures occur. That means the computers will not be running all day long to log personnel information changes. This is the reason why the dates should be entered manually and we cannot use time stamps because information recording will not be done in real-time.
Well, my question is did I do the right thing when I added the encoder and verifier columns in the STATUS table? I mean, is this the proper way of storing the information about who entered the information and who verified the information? Or is it even right to put the users' logon information as a table inside the same database? Because I've read about LDAPs or other authentication services but I don't know how to use it and that if I use such services, how would I integrate it to the database especially if I want to record who entered the data and who verified it. Please give me some advice or tips because I am not confident enough with this kind of design especially in terms of security.