Mysql – Plan for managing financial transactions in an easily and retrievable manner, indefinitely

database-designinsertMySQLrdbms

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the rows as financial transactions. Clients need to be able to view these rows and/or retrieve a report containing details about the transactions through an online system (going back as far as possible).

My question is this: Is it feasible to allow these rows to accumulate and simply SELECT them from the table when preparing a listing, even accounting for significant growth in volume? The traffic would be relatively low.

We've thought of implementing a clever scheme of separating the table into years; however, that would add significantly to the complexity of the project. Assuming the table was carefully optimized and indexed adequately, would the table still be fast enough at 1,000,000+ rows per year?

I hope this is not too vague or specific to warrant an answer and would like to thank those who take the time to consider responding.

Best Answer

With no growth, you're looking at 1.8 million rows in 10 years. With 100% growth per year, 93 million rows in 10 years. I'd estimate your actual upper bound to be 66 million rows after 10 years.

It's practical to keep that many rows in a transactional table if you partition the table. (You don't have to do that for a while.)

We've thought of implementing a clever scheme of separating the table into years

Millions of people have thought of this. Don't do it.

Use a single, unpartitioned table until you have a performance problem. Then partition the table. (You have a lot of time between now and then to test and practice.) Finally, after you tune the server for the new workload, consider upgrading hardware. (Banks don't run on shared web servers that cost $2.95 a month.)

Also consider switching now to PostgreSQL. It has a better query optimizer than MySQL; it has more indexing options, including function-based indexes and partial indexes; and in my experience seems to handle 100-million-row tables better than MySQL.

Related Solutions

Sql-server – Examples of SQL transaction procedures for sales tracking or a financial database

Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.

Any examples would be very basic as every database schema would be different. This is where having a database or data architect comes in handy for a project.

Mysql – big db for managing user files and settings

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas. Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

Best Answer

Related Solutions

Sql-server – Examples of SQL transaction procedures for sales tracking or a financial database

Mysql – big db for managing user files and settings

Related Question