CODEWITHSUNDEEP
mysqlsqldatabase-design
Snuur
Snuur

Reputation: 309

mysql order database use 2 tables for current and history?

I have a question about performance. I have a table that contains orderheaders, whenever an order is shipped and invoiced the row is moved from orderheaders to orderheaders_history. This was designed like this by someone else. Now im thinking that one column in the orderheaders table to define wether the row is current or history is more efficient. What way is actually better?

Thanks!

Upvotes: 0

Views: 166

Answers (1)

ApplePie
ApplePie

Reputation: 8942

Historical data in a separate table makes sense only if you have a lot of records. What "a lot" can only be determined by you. If your queries are getting slower and having historical data grouped with your more current data is not a business need then you separate them but generally speaking I would have a single table with both current and historical data and only if I can build a solid case of it would I go for an historical table.

Once you hit a critical mass of records and you need to put your records in separate places you can then start dumping your data in cheaper, slower hard drives (not to say less reliable) which will enable you to have a lower total cost of data storage while still having all data ready for use.

Finally, if you already have a working process that is automated around this setup I would not go and change it just for the sake of changing it. Changing a database is risky and you don't want to lose or corrupt data without good reason. If the process does create overhead and you do not benefit from the separate of these tables then I would suggest you to consider reverting back to a simpler table structure.

Upvotes: 1

Related Questions