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 expenseentry. Both 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, timeentry and expenseentry have after update triggers to keep the columns in lineitems updated to the current values.

This 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 timeentry and 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 timeentry and 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.