I inherited a MySQL 5.6 database that I am trying to optimize in terms of structure and speed.
Quick background: Users submit their daily time records for the day through a application which goes to a table
timeentry, but can use the same form in the application to also submit expenses which goes to a table
timeentry and and
expenseentry have after insert triggers that insert a copy of their row to a table
lineitems, with a column keeping track of which row came from what table - a column
type which is
1 if it came from
timeentry and 2 if it came from
expenseentry. In addition,
expenseentry have after update triggers to keep the columns in
lineitems updated to the current values.
lineitems table is then used to map to other business entities in the database, sales orders, purchase orders, etc, but based on it's
id column, not the
fk_itemId field which has both
expenseentry id's in it, so there are some duplicate entries because of id overlap between the two tables.
I was tasked with allowing mapping the data from the
timeentry table to a new business entity and saw that this is how things are currently structured in our database. Is this a common pattern? Or would it be more proper to just make these mapping tables from sales orders/purchase orders etc refer directly to
expenseentry directly instead of through the table
lineitems. I can see how
lineitems is useful for some queries, but looking at this was structured it wasn't intuitive to me and makes me think there might be a better way.