Please validate the database design and help is there any better way.
I'm designing database for tracking history of a ticket. the ticket history will store the each change as entry in history table. Please ignore, I have not added created by, created date columns to all tables, that I will.
UI : Ticket form fields looks like below
Ticket Number(field)
Ticket Title(field)
Ticket Description
Ticket Status(drop down value comes from ticket_status[id, name] table)
Ticket Business(drop down values comes from Business[id, name] table)
Ticket Created By(drop down values comes from User[id, name] table)
Ticket Assignee(drop down values comes from User[id, name] table)
Ticket Table structure :
Table : ticket
+--------+-------+-------------+--------+----------+------------+----------+
| number | title | description | status | business | created_by | assignee |
+--------+-------+-------------+--------+----------+------------+----------+
| | | | | | | |
+--------+-------+-------------+--------+----------+------------+----------+
foreign keys :
ticket_status_id(foreign key reference to ticket_status(ticket_status_id))
ticket_business_id(foreign key reference to business(business_id))
ticket_created_by(foreign key reference to user(user_id))
ticket_assignee(foreign key reference to user(user_id))
Table : ticket_status
+------------------+------------+
| ticket_status_id | name |
+------------------+------------+
| 1 | Open |
+------------------+------------+
| 2 | Close |
+------------------+------------+
| 3 | Inprogress |
+------------------+------------+
| 4 | on hold |
+------------------+------------+
Table : business
+-------------+---------+
| business_id | name |
+-------------+---------+
| 1 | Target |
+-------------+---------+
| 2 | Walmart |
+-------------+---------+
| 3 | TJ Max |
+-------------+---------+
Table : user
+---------+---------+-------+
| user_id | fname | lname |
+---------+---------+-------+
| 1 | Aamir | Mckay |
+---------+---------+-------+
| 2 | Forrest | Bass |
+---------+---------+-------+
| 3 | Murray | Tate |
+---------+---------+-------+
Ticket Hisotry structure : Each field last value and new value will be recorded.
Table : ticket_history_fields
+--------------+-------------------+
|field_id | name |
+--------------+-------------------+
| 1 | TICKET_NUMBER |
+--------------+-------------------+
| 2 | TICKET_TITLE |
+--------------+-------------------+
| 3 | TICKET_DESC |
+--------------+-------------------+
| 4 | TICKET_STATUS |
+--------------+-------------------+
| 5 | TICKET_BUSINESS |
+--------------+-------------------+
| 6 | TICKET_CREATED_BY |
+--------------+-------------------+
| 7 | TICKET_ASSIGNEE |
+--------------+-------------------+
Table : ticket_history
+------------+--------+----------+------------+-----------+
| history_id | txn_id | field_id | last_value | new_value |
+------------+--------+----------+------------+-----------+
| 1 | 1 | 2 | server | AWS |
+------------+--------+----------+------------+-----------+
| 2 | 1 | 5 | 1 | 2 |
+------------+--------+----------+------------+-----------+
| 3 | 1 | 4 | 1 | 3 |
+------------+--------+----------+------------+-----------+
| 4 | 2 | 7 | 2 | 1 |
+------------+--------+----------+------------+-----------+
Questions :
1. In this design I'm storing last_value and new_value id's or should I store text like below, which one is good and why?
+------------+--------+----------+--------------+-------------+
| history_id | txn_id | field_id | last_value | new_value |
+------------+--------+----------+--------------+-------------+
| 1 | 1 | 2 | server | AWS |
+------------+--------+----------+--------------+-------------+
| 2 | 1 | 5 | Target | walmart |
+------------+--------+----------+--------------+-------------+
| 3 | 1 | 4 | Open | Inprogress |
+------------+--------+----------+--------------+-------------+
| 4 | 2 | 7 | Forrest Bass | Aamir Mckay |
+------------+--------+----------+--------------+-------------+
2. Is this is good design or is there any better design for history tracking? How enterprise application do the history tracking like jira.
3. Could you suggest me a book or site for database designing for beginners.
Before picking a solution, you need to think about WHY you're capturing history. Here are some common reasons:
- Auditing (who changed what, when)
- Error recovery (ooops, I just deleted the wrong row; get it back!)
- Change tracking (viewing the changes, comparing versions, etc.)
- Change management (the customer ordered product version X, current version is Y; ensure you ship them the version X spec)
You can then evaluate your needs against different designs. Here are a few high-level strategies:
Separate history tableConcept:
Every time you update/delete rows in TICKETS, you copy the previous values to TICKETS_HISTORY
create table tickets (
ticket_number int
primary key,
...
);
create table tickets_history (
ticket_number int,
status_date date,
operation varchar2(1) -- update/delete
...
);Pros: Main tickets table stays relatively small; you can store the history table on slower/cheaper storage if necessary
Cons: Two objects to manage; people may not realize the history table is there => table definitions can get out of sync, ...
Versioning within the tableConcept:
One TICKETS table, with version number/date columns. You disallow updates; instead you "update" a row by inserting a new row with an incremented version number.
create table tickets (
ticket_number int, ticket_version_number int,
primary key ( ticket_number, ticket_version_number )
);
Pros: You can have foreign keys from other tables to a given version number; change comparison is easy (select ... where version in ( 1, 4 ))
Cons: If a ticket typically has many changes, this table can grow fast. Can be hard to identify the current version of a ticket
Best for change tracking and management.
Flashback Data ArchiveConcept: use the built-in flashback functionality to capture all changes to the table:
alter table tickets flashback archive <archive_name>
Pros: Easy! After the initial setup, the database manages everything for you. And you can use flashback queries to see changes over time.
Cons: The flashback tables are managed by Oracle Database; this makes functionality like "rollback to version X" or "have a foreign key to version Y" are harder/impossible to do.
Best for auditing and error recovery.
So ultimately you need to decide: what is the history data for?
If you're looking for a place to get started with database design, I recommend The Database Programmer blog
http://database-programmer.blogspot.com/2008/09/comprehensive-table-of-contents.html The section on table design basics gives a good introduction to constraints and normalization.
The posts on table design patterns changed the way I think about tables completely, leading to better databases.
As Ken says:
a code grinder is going to figure it out, while a database programmer is going to spell it out