This documentation is designed for MySQL Developers. While terms and references are MySQL specific, the guidelines, principles and practices can generally be applied to any Relational Database (RDBMS) with little to no adjustment.
Examples where possible are based on the MySQL Sakila Sample Database.
My first exposure to Databases, Design and Modelling in the later part of the 1980's, quite some time ago. Earlier references resonate as fundamentals, the quinessential "An Introduction to Database Systems" by C.J. Date, that I used at university, now in it's eighth edition. "The Ingres Papers: Anatomy of a Relational Database System" by Michael Stonebraker, as well as the writings and early theory from E.F. Codd, the father of Relational Database Theory.
Also, a special thanks to Mr Frank Jarvis, Lecturer at the then Darling Downs Institute of Advanced Education (DDIAE) that encouraged me to pursue this field. Refer to Appendix for this Obituary with typifies his character and nature.
mysql program.This documentation will make reference to syntax and functionality that is available in the current production version of MySQL as at April 2006.
MySQL 5.0 GA (5.0.20)
http://www.mysql.com/products/database/
As part of a presention at an Open Source conference, I referred to a particular point called, "The Hobbist and the Professional". In summary, my conclusion was that "Open Source can easily lead to poor design and programming practices". This comes down to the lack of use and understanding of a number of principles, one of these being standards. The ability now for anybody to learn a programming language then design and develop or contribute is exactly what Open Source embodies, that is "FREEDOM", and I am in full support of this ideal. This is sometimes confused with FREE, and in the same line with the monetery perspective, the legacy of this FREEDOM is a cost. This cost will only continue to grow, and it may indeed damage Open Source adoption in the enterprise marketplace as a forefront solution to business needs.
My area of expertise is Database Modelling and Large Systems Design, and in my time I've seen and been forced to work with some terrible designs.
So why is the Database Design most important?
There are two reasons, the first is the technology stack. Consider the LAMP Stack, Linux - The Operating System, Apache - The Application Server, MySQL - The Database, and PHP/Perl/Python - The Programming Language. In order of precedence for an organisation large or small, consider the term "DLOA". Database, Language, Operating System, Application Server. This is the order of change from least to most, in technology offerings within each component of the stack. The Database product is the least likely to change. Choosing the right database and using it appropiately can have the greatest effect on one of the most critical attributes of a computer system in the eyes of end users - Performance.
There are some caveats to this reasoning, this is dependent on the target industry or any specialised requirements which I will not discuss here.
When people wish to know more about what I do as a profession, but are not IT literate, I use the following example.
Consider building a home, a larger structure or even a high rise building. The most important and critical part of this is the architectural design of the underlying structure. Often what is not seen, but is necessary for the building to exist. The plan, the foundations, the considerations for all the important factors like power, water, heating etc, the capacity for the environmental setting, the weather, the wind, water, the support for the look and feel, are all dependent on a sound, solid and extensible design.
If you don't get the foundations correct, then you will forever be proping up additional work, spending valuable time, effort and energy when it could have been put to much more practical use if the job was done right from the start.
Another anology I heard recently and is also appropiate is this. "It's much harder to change once the concrete has been poured".
In database design, a fundamental flaw can have a huge effect if the system has already been designed and built. This is true in a traditional System Development Life Cycle (SDLC) project. There are now becoming more popular approaches to overcome this burden, and in my article "Better Productivity and Quality. An Agile Approach" I discuss the role and impact of using an Agile Development Methodology in Software Development. Again is this outside of the scope of this article, however relevent in the greater area of developing and supporting Computer Software.
⇑ TOPIt may sound stupid, but I'm still amazed regularly that the pursuit of Information Technologists is not applied against this simple and common sense principle that can be applied in many aspects including computer systems. It makes me think of the qoute "Any fool can write code a computer can understand, good programmers write code that humans can understand." from the book "Refactoring - Improving the design of existing code" by Martin Fowler.
Our pursuit should be always to make things simplier to use, understand and appreciate, rather then obfuscated to a point where the author can't maintain their own code after not looking at in three months. We are becoming more familar with using newer, better faster frameworks that are designed to simplify the process, but these can lead to spending more time writing code to support code, rather then writing code to solve problems. In pondering, it all seems a little pointless. At this time, you many wonder why I'm reflecting on programming practices when this article is for database practices, it is that each have a symbotic relationship. No enterprise application can survive without a database for persistence storage, and no database can serve any purpose if it's intent is simply to store information, but allow no user means to utilise the information for some better pursuit.
To perfect Database Modelling and Design, should be to provide a suitable (and often complex) system as simply as possible.
In Database Modelling, there is no one correct and right model. Indeed, there are many different ways one could consider standard and acceptable for the given environment and circumstances. There are however many wrong ways, and sometimes when it's not possible to determine exactly what is right, or what you want, it's much easier to identify what is wrong, what you don't want, and what will not work.
While I will be providing several alternatives in many areas of Database Design, I will be making a recommendation in most cases. This has come from almost 20 years of industry experience, and has worked in my profession.
⇑ TOPAs you have gathered, I will be working on providing a simple view, one part of this is the essential definition and use of standards, a set of rules that are met and followed, even religiously. There is a clear purpose for this, that standards enable many people to work together and there is a common understanding when individuals collaborate. While people, organisations and businesses may have differing standards, an experienced person if given an appropiate standard and having worked on varying standards, will quickly be able to communicate in a more concise means. In the area of Software Design, indeed the residual cost and time for most is spent in maintenance, and significant benefits can be made if appropiate standards exist.
Having solid and consistent naming standards will greatly benefit a systems' worth and longevity. It should be noted that while some standards may seem unwarranted for database design, consideration for database administration, programming and external review and support may be a considering factor, and this may not always be obvious until the entire view of the product, methodology and development lifecycle is considered.
In summary, Naming Standards will endeavour to: