5 Nov 2007

Federated Tables for Enterprises: The future is today?

(Please watch this youtube video to fully appreciate the "the future is today" joke)

What are federated tables?

The FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables.
(the link to the mysql site)
So federated tables are tables that lookup data from regular tables that are on remote server, meaning somewhere else over your network or internet. This is very interesting to me, because it technically saves you a lot of coding to update your tables manually from another server.
Here is an article from O'Reilly - MySQL Federated Tables: The missing manual which really does explain a lot about them.

Data Warehousing

From a data warehousing approach, this is really great. This means I can get data from many small databases located in many different places and create a combined data warehouse with a lot less work. And to add to that, MySQL is a lot cheaper then Oracle, so maybe I can advise some people to use MySQL for their next data warehousing project and spend far less money while still keeping a lot of the features that persuade people to use Oracle for data warehousing.
Now I'm not a data warehousing expert (yet, because the subject does interest me) and I don't know all the features that Oracle can give toward this challenge, but I will look into it.

Oracle Timesten

However, there is a TINY TINY thing I would be interested in:
I've seen Oracle Timesten being used for CRM system (customer relation management - usually used to give better customer service) where the Timesten database, loads data to its memory tables depending on what you asked it to look for.
For example, the customer service rep needs all the information and past purchases for John Smith for the last month. From what I understand, Timesten will contact the main Oracle database and load that specific requested data to the memory table and hold it there.

Giving MySQL the advantage

Now if federated tables would have some sort of built in cache to save the server to keep calling the remote server (which lets face it, can get slow), this would be a HUGE advantage. This would really unload a lot of stress of the main servers by simply "caching" data on local clients.

However, there is a good chance that the local clients already have some sort of caching to do this through the application. But I think it would be great if the caching was done at the database level here, because it can simply save the need to add caching or at least reduce it greatly for developers, making the MySQL a very attractive product.
Add to that, that sometimes the SQL database is the "over head" and some companies prefer to use MySQL embedded database to solve that over head problem.
So if you had federated tables + caching and you put them in small and light embedded databases, then you would have a way to connect to a remote database and caching system already built into your application and you didn't even do anything.


1) Federated tables are defiantly something to look out for.
2) Can someone please pass this link to somewhere on the MySQL developers forum, because I don't know where. Unless they already thought up this idea, in which case, ignore it.

Once again, thank you for taking the time to read my blog.

1 comment:

  1. This sounds like a great idea. I'm the guy who wrote the Federated Storage Engine, and I'm going to start working on it a bit more now and add this idea as one of the features Federated needs.