Databases
StackOverflow2013
Postsdbo
POMYSQL - Database Design Large-scale real world deployment
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL - Database Design Large-scale real world deployment
    primarykey
    data
    text
    <p>I would love to hear some opinions or thoughts on a mysql database design.</p> <p>Basically, I have a tomcat server which recieves different types of data from about 1000 systems out in the field. Each of these systems are unique, and will be reporting unique data.</p> <p>The data sent can be categorized as frequent, and unfrequent data. The unfrequent data is only sent about once a day and doesn't change much - it is basically just configuration based data.</p> <p>Frequent data, is sent every 2-3 minutes while the system is turned on. And represents the current state of the system.</p> <p>This data needs to be databased 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.</p> <p>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 is currently about 100 of them.</p> <p>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 2000row x 200column table that gets accessed about 100 times a second so I need to cater for this in my initial design.</p> <p>I am also wondering, if there is any design philosophies out there that cater for frequently changing, and seldomly 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.</p> <p>I would also love to know how to split up data. I.e. 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).</p> <p>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 advise I would be very appreciative. Thanks heaps :)</p> <p>~ Dan</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USFaddishWorm
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMYSQL - Database Design Large-scale real world deployment
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMYSQL - Database Design Large-scale real world deployment
      1. USFaddishWorm
      1. VTFavorite
    1. COSome questions here: (1) Do you need to store the history, or simply the current state, of each system, in your "frequent data?" If you need to store the history, how far back? (2) do you need history for your config data? If so, how far back? (3) is there commonality in the "frequent data" between systems, or is each system unique? What does this data look like? (4) accessed 100 times a second? That is a a very fast access rate for any DBMS. Can you give more details about that?
      singulars
      1. POMYSQL - Database Design Large-scale real world deployment
      1. USO. Jones
    2. CO(1) No, only some things will be kept in history - and I database those in seperate tables through tomcat. (2) No history for config data is needed. (3) Yes there is commonality, in that each system will have most of the config keys, only the values will change in the key-value pairs. (4) 100 times a second may be an over-estimate, but If I aim to satisfy that then I know the server will stand up to almost anything.
      singulars
      1. POMYSQL - Database Design Large-scale real world deployment
      1. USFaddishWorm
    3. CO100 accesses per second (6000 per minute), if it's real, is one of those specifications that must drive your entire design. If it's real it will compel you to do a lot of optimization. If it's not real you'll drive up your costs and complexity for no good purpose. Many developers design in a way that gets things working, and then worry about scaling up. If you're late getting your system working, you'll never have the chance to scale it up. So be careful about overspecifying!
      singulars
      1. POMYSQL - Database Design Large-scale real world deployment
      1. USO. Jones
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload