So what exactly is a “relational database”? Remember back when we introduced the concept of a database, and mentioned there may be many database tables inside a database, and that the tables may be related somehow? One of the early advancements in the database world was to progress from a flat-file (single table) database to a relational database (multiple “related” tables) with the aim to speed up operations and decrease storage cost by removing redundancies from a database system. Again, speed is the driver here.
Imagine you have a story for multiple (in this example 5) newspapers, and you will have to run multiple productions, each to a different newspaper. There might be a photo that will need to be produced to all 6 newspapers, and you want to keep track of your production. So in your database table you have a separate line for each time you produce that photo. It would look a lot like the top item in the diagram below. Even a cursory look will tell you that there is a LOT of redundancy in the rows, since actually they are identical except for the last 3 columns on the right. Since redundancy in a system generally slows things down, there must be a better way to handle this!
All the documents, being identical, have the same unique ID number in the column on the left (“12345”). This means we could leave just one row in our main database table with the data for 12345, and have a second table, which only shows the Production History (date, newspaper and production number). If we then carry over just the Unique ID for the document, and fill in the other 3 fields each time we produce it, we will have all the same information as in the example above, but even visually it is obvious we are using a lot less space, and almost no redundancy. Because the Unique ID appears in both our main table and in the Production history table, it ties them both together and they are considered “related”. Hence the expression “Relational database”: it lets us squeeze out redundancy and increase speed by having additional tables that are related together.
In future blog entries we’ll talk a little more about what goes on “behind the curtain”, as we discuss the architecture of your digital asset management system and how it relates to security and fault tolerance. We hope that you’ll be able to join in on the discussion.
Posted by David Tenenbaum
Flickr Photo by Simo ubuntu