0

I am developing a desktop application and it will include clients, bills, banks, incomes&outgoings etc,

I am using mysql and delphi,

My question is do i have to cycle (create new tables and transfer needed data from old tables to new tables for new year) every year because i think database will have data for a year and it will adventually get slow, is there a way/is it possible to continue without cycle but without database get slow?

I want to do this because i want user be able to find/see client cards and bills of old years too

Thanks

Improve this question
3
  • please post details about your MySQL VMs, total database size and tables mentioned size and rows. Why you think it gets slow? Measured?
    – Sybil
    Commented Feb 22, 2017 at 10:56
  • At the moment i dont have tables created yet, i think it will get slow because every year more data will be placed in the database so the amount of records will be increased (for example 3 years later there will be 3 times more data than the first year) and i think will cause slowness on reports and queries, but i would prefer not to cycle every year and i want user be able to see all the past data, i dont know but how banks do it? Do banks cycling too their data every year?
    – Emre acikgoz
    Commented Feb 22, 2017 at 11:16
  • 1
    Your first concern should be to create a correct (3NF) data model that supports everything you need. It's hard enough to do that! Databases work really nice if you have a correct data model. Don't model your data around things you might think a database is not good at. MySQL can work with (hundreds of) millions of rows without changing the table structure, just by using indexes. And when that is not enough, you can e.g. use partitions, that can internally split up your tables into subtables for each year, STILL without changing your table structure. So again: just make a correct data model.
    – Solarflare
    Commented Feb 22, 2017 at 15:08

1 Answer 1

Reset to default
1

With rare exceptions, do not create identical tables split by year or month or customer or whatever. If a table is getting slower as it gets bigger, then you don't have the right indexes and/or you need to redesign the queries. Even a billion rows in a table is not 'unmanageable'.

Clients, bills, banks, transactions -- sounds like about 4 tables, plus some tables for Normalization. A bill might contain a client_id column, but not the client's name, address, etc.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.