Search code examples
oracledatabase-designdata-modelingcreate-tableaudit-trail

Database: Storing multiple Types in single table or multiple intermediate tables for Delta Tables

Using Java and Oracle.

We need to update changes in Email, UserID of employee to third party. Actual table is Employee and intermediate table we keep which we will use for comparison of changes before sending to third party.

Following are database designs coming in mind for intermediate table:

Only Single table:

EmployeeiD|Value|Type|UpdateDate

Value is userid or email, type will be 'email' or 'userid'. Update date is kept so to figure out that which of email or userid was different and update to third party.

Multiple Table:

 Employee_EmailID
      EmpId|EmailID|Updatedate

 Employee_UserID
      EmpId|UserID|Updatedate

Java flow will be:

  • Pick employee from actual table.
  • Pick employee from above intermediate table.
  • Compare differences. Update difference to third party.
  • Update above table with updated value and last update date.

Which one is consider as best way, single table approach or multiple table or is there any standard way to implement the same? There are 10,000 Employees in system.

Intermediate table is just storing Delta records i.e Records transferred to third party so that it can be compared next day.

Solution

  • Good database design has separate tables for different concepts. Using the same database column to hold different types of data will lead to code which is harder to understand, prone to data corruption and less performative.

    You may think it's only two tables and a few tens of thousands of rows, so does it matter? But that is only your current requirement. What you choose now will set the template for what happens when (say) you need to add telephone numbers to the process.

    Now in future if we get 5 more entities to update

    Do you mean "entities", like say Customers rather than Employees? Or do you really mean "attributes" as in my example of Employee Telephone Number?

    Generally speaking we have a separate table for distinct entities, and all the attributes of that entity are grouped at the same cardinality. To take your example, I would expect an Employee to have one UserID and one Email Address so I would design the table like this: 

    Employee_audit
      EmpId|UserID|EmailID|Updatedate

    That is, I have one record which stores the complete state of the Employee record at the Updatedate.

    If we add a new entity, Customers then we have a new table. Simple. But a new attribute like Employee Phone Number offers a choice, because an employee can have more than one: work landline, mobile, fax, home, etc. So we could represent this in three ways: a child table with a type column, multiple child tables for each type, or as distinct columns on the Employee record.

    For the main Employee table I would choose the separate table (or tables, depending on whether I'm shooting for 6NF). But for an audit table I would choose one record per Employee and pivot the phone numbers like this: 

    Employee_audit
      EmpId|UserID|EmailID|Landline|Mobile|Fax|Home|Updatedate

    The one thing I would never do is have a single table with type and value columns. It seems attractive because it means we could track additional entities without any further DDL. But in fact it becomes harder to re-assemble the complete state of an Employee at any given time with each attribute we add. Also it means the auditing process itself is more complicated (because it needs to determine which attributes have changed and whether it needs to audit the change) and more expensive (because changing three attributes on the same record entails inserting three audit records).