Database design, one table or many?

T

tanked

Hello, I am attempting to design a database that will record the clock-in, lunch and clock-off times for employees and hope use Postgresql + FreeBSD for this task. I've run into a problem regarding how to store the data for the times - from my reading it would seem that the accepted wisdom is the fewer tables the better, but if I just used one table to record the times then how could I prevent employees changing each other's times if they all have INSERT and UPDATE permissions on the same table?

The best ideas I can come up with are for each employee to have their own table (which obviously wouldn't scale well in an organisation with hundreds or thousands of employees), or the other solution being to have the one table but the client application authenticates on behalf of the user using a dedicated account unknown to anyone (apart from the admin) that would then record the time data including the employee's ID number.

On a related issue, once I have the database up and running how could I stop clever users from connecting to it using other tools e.g. pgAdmin III and directly editing the tables they have access to - in other words I would want them to only connect and write to the database using the client I design for it.

Thanks for help and opinions
 
This question really caught my attention. So, let's begin. Disclaimer: I'm a web developer so I see everything from web apps perspective.

What would I do with such task? I would set up a small web app using Django, Bootstrap, Nginx & uWSGI. Let me explain.

Django - modern web framework, written in Python. Difficulty - moderate. You need basic knowledge about web apps, basic knowledge of Python and also to complete Django tutorial (if you like Ruby you could use Ruby on Rails, but in that case deployment strategy changes). Django has basic auth, extendable admin and from version 1.5 - custom pluggable User model. Btw, Django has astonishing docs, where you can read all about models and other things. You can make separate user profiles, and no user could change another's info - just restrict it on the server side and always check it before saving to DB. Also, Django supports PostgreSQL (in fact, many Django devs recommend using Postgres).

Next, Twitter Boostrap. It's CSS framework from (as you might already guessed) Twitter devs. It was designed for rapid prototyping and nice look. With Django and Bootstrap you can build such app in a few hours.

Nginx (a webserver), uWSGI (application container server) are used for Django deployment (many howtos are available in the web). They are available in FreeBSD ports.

Some might argue, that Django is overkill, you should use simpler things like Flask, but I think for novice or someone without prior web development experience it might take lots of time to get used to Flask and it's components.

I don't know about any restrictions you might have, but in 2013 even fridges have browsers, so I think it is the fastest and easiest thing to implement. And in that case, nobody except one allowed user for Django-app will have access to PostgreSQL database.

If I missed something or you have questions about development feel free to ask.
 
Assuming you already have an employee table with employee_id as key and that the time values also includes the date:

A table time_type with the fields time_type_id and descr with 4 records:
  • 1 | check_in
  • 2 | check_out
  • 3 | lunch_start
  • 4 | lunch_end

A table times:
  • employer_id
  • time_type_id
  • time

Create some sample data for the times table, and try some SQL queries to see whether this table layout suits your recording as well as your reporting needs.

Let the application require a login, which determines the employee_id to use and you can use a single table for all employees.
 
actually, when you design a database, a lot of small tables are better than a few big ones, and not the other way around, because your aim to have as little data duplicates as possible.

table_employees_auth is like {employee_username(unique), employee_password }. employee_password probably should be encoded.

table_employees_info is like {employee_username(foreign), employee_name, employee_position, ...}

table_times has {entry_id(unique, autoincrement), employee_username(foreign), start_time, end_time, lunch_start, lunch_end}

then use a single admin account (no privileges to anyone except database admin) to make all modifications. employees authenticate using a web form (or php session or something similiar) with their username, password and times. auth against the data in table_employees_auth (don't forget to never store and transmit passwords in their raw format), and their times will be added to the table_times.
 
The way I would do it is to not try and authenticate the user with the database software directly.

Present a form for them to log into the database, store the username and hashed password in a table, and only allow them to modify entries in the table you have that match their username. So that's at least 2 tables before we start.


As far as restricting access to the database goes, I would allow access only from localhost (the web server) and using embedded, strong credentials. Do not give your users credentials to access the database service, only credentials used and stored internally within your database.

As above, multiple tables can help - generally if you have unrelated data to each entry in a table, it should be stored in a different table.

E.g. (simplified example), a time sheet entry may have something like name, clockon, clockoff. Login details may be stored in a table with name,username,password_hash. The two tables are linked via the "name" field via an SQL join statement.

Would recommend reading up on SQL before getting carried away with design too much.
 
Hello again, after further consideration it would seem the web interface route would seem to be the most appropriate for this. This does lead me on to another question though; lets say I include the concept of 'departments' into the database with the users being a 'member' of them (I might want to do this to be able to produce departmental statistics e.g. department with most sick absence etc..) I then thought it would be useful to delegate access to these departments to departmental managers so they could add remove users from them as well as change other properties, but I would want any changes to be audited with the audit information stored in another table or perhaps seperate database.

My questions are that if I use a dedicated account to access/write to the database then how could I delegate access to certain tables, and since I am using one account, wouldn't the audit information simply show the same account making all changes?

I appreciate any thoughts you might have.
 
Someone has suggested to me that the auditing can done on the web server side, though I still don't know how I would delegate access within the database if I'm just using one ROLE for database access.
 
With an app you should consider database only as a data storage. All permission system could be (and in my opinion should be) done in the web app. In this case, if some user tries to post some info and he has no permission to do so - this info will be simply dropped before accessing the database. All permissions are stored in the user profile in database.
 
I've been intensley reading up on the Postgresql documentation and studying books on database design which is why I keep looking at things purely from a database and ROLE authentication prospective, but it looks like I need to read up on web app frameworks, thanks.
 
Most web "apps" / frameworks that I have come across use a dedicated database user to access the database; this user (and password) is configured in the application somehow, and is only known to the application and the administrator.
 
tanked said:
I've been intensley reading up on the Postgresql documentation and studying books on database design which is why I keep looking at things purely from a database and ROLE authentication prospective, but it looks like I need to read up on web app frameworks, thanks.
Click to expand...

The ROLE (or USER) authentication in PostgreSQL is for the web applications or for actual software to authenticate to the database for data retrieval/storage. Very few times you would have actual ROLE or USER accounts in PostgreSQL that belong to an actual user (except for those for a database administrator).

What you want is at least the following: (Note '--' means a comment follows after in PostgreSQL)

Code: 
CREATE TABLE usercreds (
  id SERIAL, -- Assign each user a unique id
  username VARCHAR(31), -- make usernames a maximum of 31 characters
  password VARCHAR(31), -- store encrypted version of the password as given by the web app
  active BOOLEAN DEFAULT TRUE
);

CREATE TABLE departments (
  id SERIAL,
  department VARCHAR(31) NOT NULL,
  active BOOLEAN DEFAULT TRUE
);

-- Personnel type can be: Manager, CEO, Employee, Temp, Intern, etc. (For reporting purposes)
CREATE TABLE personnel_type (
  id SERIAL,
  description VARCHAR(31) NOT NULL
);

CREATE TABLE personnel (
  id SERIAL, -- assign each employee a unique id
  first_name VARCHAR(31) NOT NULL,
  last_name VARCHAR(31) NOT NULL,
  personnel_type_id INT NOT NULL,
  active BOOLEAN DEFAULT TRUE -- An employee can be active but have an inactive timetrack account
);

-- This table allows for an employee to be part of more than one department, which can show
-- as a select dropdown on the web app.
CREATE TABLE employee_department (
  id SERIAL, 
  employee_id INT NOT NULL,
  department_id INT NOT NULL,
);

-- Create a time type table to hold values like 'PTO, SICK, REGULAR'
CREATE TABLE time_type_id (
  id SERIAL,
  type_description VARCHAR(31) NOT NULL,
  active BOOLEAN DEFAULT TRUE
);

-- Note: Its better to store the start and end times together so that you can limit a
-- situation were there is more than 1 start entry without a corresponding end entry.  
-- Also, it makes it clearer to the application and to the database administrator which 
-- entries correspond together.
CREATE TABLE employee_timetracker (
  id SERIAL,
  employee_id INT NOT NULL,
  department_id INT, -- here department id can be empty
  personnel_type_id INT NOT NULL, -- this should be populated by the application based on the current value in the employee id.  This for situations were an employee goes from TEMP to FULLTIME for example.
  start_date DATE NOT NULL DEFAULT NOW(),
  end_date DATE, -- Our end date can remain empty until the user clocks out.
  time_type_id INT NOT NULL
);

The above is an example of how you would create the initial layout of the database and in no way complete. This depends on your initial business requirements. The important thing is to always think:

* If I need to add something to this, will the existing layout prevent me from doing it?
* Do I have duplicate data, apart from Integers that link records from different tables together?
* Can I query for the needed information easily?

Disclaimer: My background is system administration, but do play a database administrator on TV.
 
You must log in or register to reply here.
Back
Top