Category Archives: MySQL

E/R Zero or One to Many relationships

While 1:n, 1:1 and n:m relationships are thoroughly used to design logical database models, there another another kind, {0|1}:n that may come in handy in some cases. Lets analyze this relationship carefully.

Imagine a relationship between students and classrooms. Every classroom can have N students, meaning this zero, one, or many pupils. From the other side, every student must have a classroom, otherwise she won’t be allowed to study. This is a classic 1:n relationship, used in most of the real cases that we may encounter.

But what if, let’s say, a student had finished her studies and, however, wasn’t deleted from the database? (just to keep the records and other sensible information). In that case, the statement ‘every student must have one and only one classroom’ would be wrong. Instead, we could say ‘every student can have one classroom or none’. Now we are talking about zero or one to many relationships.

Continue reading E/R Zero or One to Many relationships

Implementing a database cubes system on MySQL

I’ve already talked about how I solved the problem of managing huge amounts of data in my last post. Now, I’m going to explain how to implement one of the solutions found in order to comfortably face this continuously increasing avalanche of information.

Let’s imagine that, as I explained before, I have separated data input into tables coded by a station ID. All of them are stored in the same database for maintenance simplicity sake. So, we have the ‘database’ in which there are hundreds of tables called ‘Station_XYZ’. Every table has the same structure: to simplify, SensorID, Time (UNIX), Value. All right then, time to make cubes!

Continue reading Implementing a database cubes system on MySQL

Managing big data sets with MySQL

I’m currently working on a database which stores, literally, a million of new data registers every day. They come from several hundred weather stations spread along the world, and need to be processed on real time. That’s a huge amount to be managed with a simple – but powerful – MySQL server, so I’ve had to design and implement a few tricks in order to succeed.

In this post I’d like to talk about the main three techniques we’ve used to engage our high performance database, and the reasons to choose these solutions.

First of all, MySQL innodb tables start degrading at some million registers  (7-10M on my current server, but not tested further), so the first though I had was: we need to divide if we want to conquer. Then, after some thinking about, I decided to split the big table into small chunks, and use a table for each station. That made the whole problem more affordable, and tables smaller enough not to become scary: the new problem was how to store about 5 M registers per year per table in our database.

Of course, some logic in both the application controller and the data input server was needed to correctly place data registers. Additionally, tables get often fragmented so some cron jobs were needed to keep the database running smoothly. But there was still the problem of data growing. As long as registers were separated depending on the station, the scalability problem was solved: more tables can be added, even more servers with ranges of stations and a hash function to calculate what server is a given station hosted in. Once again were we talking about several million registers per year per table, and with a standart MySQL 5.1 server (the main problem with an start-up company tends to be funding, so we didn’t have anything better).

Continue reading Managing big data sets with MySQL

Creating Triggers in MySQL from PHP

On my last project, I needed to create tables periodically on a database, to partition data in several identical tables, named according to the year quarter. Instead of using MySQL 5.1 automatic partition, I preferred doing it in a more manual way, to allow accessing individual partitions if suitable to speed up queries.

So I wrote a php script that would run with a cron job and every three months, create a new table, add it some triggers, and remap a MyISAM merged table on another database. Clear and easy, at the beginning. Everything went right until I went for the triggers creation…

Continue reading Creating Triggers in MySQL from PHP