Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID ...).Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you'll write “Student Course”. StudentCourse is much better).Don’t use unnecessary prefixes or suffixes for table nam...

Database is one of the inevitable parts of a software application these days. It does not matter at all whether it is web or desktop, client server or peer to peer, enterprise or individual business, database is working at backend. Similarly, whether it is healthcare of finance, leasing or retail, mailing application or controlling spaceship, behind the scene a database is always in action.Moreover, as the complexity of application increases the need of stronger and secure database emerges. In the same way, for the applications with high frequency of transactions (e.g. banking or finance appli...

In modern web development, it’s standard practice to make use of a database abstraction layer, typically an Object-Relational Mapper based on either the Active Record pattern or the Data Mapper pattern. There are several pros and cons to this which are fairly well established, so I’ll spare us all from enumerating them all right now.One established pro worth mentioning is that these systems typically provide a high level interface for fetching data, often removing the need to ‘hand write’ SQL for anything but the most complex of query.As good as this sounds it h...

As I've said in a few talks, the best way to improve your systems is by first notdoing "dumb things". I don't mean you or your development staff is "dumb", it'seasy to overlook the implications of these types of decisions and not realize howbad they are for maintainability let alone scaling. As a consultant I see this stuffall of the time and I have yet to ever see it work out well for anyone.Images, files, and binary dataYour database supports BLOBs so it must be a good idea to shove your files in thereright? No it isn't! Hell it isn't even very convenient to use with many DB languagebindings...

Look, everyone makes mistakes. It’s true. But not all of us have the chance to make mistakes that end up costing millions of dollars in hardware and production support costs.Any one of the following five mistakes listed below will add additional costs to your company. It’s guaranteed. The costs could be hardware related (extra disk space, network bandwidth), which tend to add up quickly. The costs are also support related (bad performance, database re-design, report creation, etc.), and they add up very quickly.Want to save your company some money, some headaches for yo...

I have been playing around with Hybrid Word Aligned Bitmaps for a few weeks now, and they turn out to be a rather remarkable data structure.  I believe that they are utilized extensively in modern column oriented databases such as Vertica and MonetDB.Essentially HWABs are a data structure that allows you to represent a sparse bitmap (series of 0's and 1's) really efficiently in memory.  The key trick here is the use of run length encoding to compress the bitmap into fewer bits while still allowing for lightening fast operations.  They key operation from my perspective ...

Starting from a problemFive years ago when I was working at Tencent, I found that MySQL request speed was very slow in the pagination scenario. With only 100,000 data, a select query on a single machine took about 2-3 seconds. I asked my mentor why, and he asked in return, "In an indexing scenario, what is the time complexity to get the nth largest number in MySQL?"The pursuit of the answerConfirming the scenarioAssuming there is an index on the "status" column, a query like "select * from table where status = xx limit 10 offset 10000" will be very slow. Even with a small amount of data, there...

An important aspect to keep in mind with databases is the cost of cache reheating after a server restart. Consider the following diagram which shows several cache servers (e.g., memcached) in front of a database server.This sort of setup is common and can work quite well when appropriate; it removes read load from the database and allows more RAM to be utilized for scaling (when the database doesn’t scale horizontally). But what happens if all the cache servers restart at the same time, say, on a power glitch in a data center?We then have a cache reheating scenario. After the bounce the...