I would love to hear some opinions or thoughts on a MySQL database design.
Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of these systems is unique, and will be reporting unique data.
The data sent can be categorized as frequent, and infrequent data. The infrequent data is only sent about once a day and doesn't change much; it is basically just configuration based data.
Frequent data is sent every 2-3 minutes while the system is turned on, and represents the current state of the system.
This data needs to be stored in a database for each system, and be accessible at any given time from a PHP page. Essentially for any system in the field, a PHP page needs to be able to access all the data on that client system and display it. In other words, the database needs to show the state of the system.
The information itself is all text-based, and there is a lot of it. The config data (that doesn't change much) is key-value pairs and there are currently about 100 of them.
My idea for the design was to have 100+ columns, and 1 row for each system to hold the config data. But I am worried about having that many columns, mainly because it isn't too future proof if I need to add columns in the future. I am also worried about insert speed if I do it that way. This might blow out to a 2000 row x 200 column table that gets accessed about 100 times a second so I need to cater for this in my initial design.
I am also wondering, if there are any design philosophies out there that cater for frequently- and seldom-changing data based on the engine. This would make sense as I want to keep
INSERT/UPDATE time low, and I don't care too much about the
SELECT time from PHP.
I would also love to know how to split up data. If frequently changing data can be categorised in a few different ways should I have a bunch of tables, representing the data and join them on selects? I am worried about this because I will probably have to make a report to show common properties between all systems (i.e. show all systems with a certain condition).
I hope I have provided enough information here for someone to point me in the right direction, any help on the matter would be great. Or if someone has done something similar and can offer advice I would be very appreciative.