Why database column names should be globally unique

After years of working with relational databases (RDBMS's), I have just started to realise the importance of globally unique names for database columns. Generally I use the term 'globally unique' to mean unique within the database schema, but I it could potentially extend to being unique across all schemas.

The problem that arises from using repeating column names is when two or more tables are used in a single query. For example, if we have two tables: 'customers' (which has a column 'id') and 'customer_orders' (which also has the column 'id') when we perform the following query:

"SELECT * FROM customers c, customer_orders o WHERE c.name = 'tom';"

The "*" clause means we will return every column of both tables. So considering a single row returned from the query, what would be the value of 'id'? Would it be the 'customers.id' field or the 'customer_orders.id' field? What if our application needed to use both values? Of course, we could change our query and give aliases to the columns, which would also mean we would need to explicitly list the columns we wish to be returned:

"SELECT c.id customer_id, o.id order_id, c.name, o.value FROM customers c, customer_orders o WHERE c.name = 'tom';"

So we can now reference either the customers.id value and the order.id value. But this solution makes our query fragile and susceptible to schema changes - what if we later rename the column 'c.name' to 'c.first_name'? The query would then break. In addition, and for the purposes of consistancy of any 'data interface' we might define, we would be forced to use these explict column aliases in all our queries, which would reduce the readability of the SQL and introduce the same problems associated with any form of code duplication.

In reality, the first SQL query would be perfectly good had we used unique column names. For example, 'customers.cst_id' instead of 'customer.id' and 'customer_orders.cso_id' instead of 'customer_orders.id'. Here I have chosen a three character 'prefix' for column names that gives some clue to the name of the containing table. Therefore 'customers.name' would also become 'customers.cst_name'.

Implementing a simple column naming convention across the entire database schema will greatly improve the effectiveness of SQL queries and will reduce the likelihood of application errors that may occur by accidentally referencing the wrong data within an application.

Delivering software projects: Drupal, Joomla or just go bespoke?

Imagine you are building a house and the time has come to install the electrics. Which of the following do you do:

1. Buy an 'off-the-shelf', 'one-size-fits-all' complete home eletrical system and then hire an electrician to customize and tailor the system to fit your needs, or
2. Hire an electrician straight-off to build the electrical system for you?

The first option may seem like a good way to save time and possibly money by buying a pre-built solution and hiring an expert engineer to tailor the system. However, such pre-built electrical systems are not available in shops because customizing a system that does not do what it is supposed to can often be more expensive that simply building the exact system to requirements.

Importantly, the second option works. An expert is hired to use their skills, experience and tools knowledge to deliver exactly what a client needs - nothing more and nothing less. This is the general approach taken not just in small-scale or private engineering, but also in large scale commercial engineering projects. And this is also the reason why 'off-the-shelf', 'one-size-fits-all' systems are simply not available for purchase - nobody builds them because nobody would buy them.


So why is the world of software engineering projects so obsessed with using off-the-shelf one-size-fits-all software like Drupal and Joomla which almost certainly do not do what the client wants out of the box and will almost certainly need extensive customization and tailoring? Why not follow the rest of the engineering world and deliver client software by using expert engineers to build tailored applications that do exactly what is required using the most appropriate tools and techniques available?

I believe there are three reasons to explain the software engineering obsession with using 'off-the-shelf' systems:

1. Building systems like Drupal and Joomla is actually pretty easy, which explains why there are hundreds, if not thousands of these types of 'one-size-fits-all' systems on the market. And the sheer number of these systesm gives an overall impression that we should be using them.
2. Self preservation - if a software project fails, it is difficult to blame the guy that chose to use Drupal, whereas it is probably much easier to blame the guy that chose to build a bespoke system.
3. In a few cases, for simple systems like very basic websites an 'off-the-shelf' system can help speed things along if very minimal customization is required - which of course is simply not the majority of high-value business systems.

A fourth reason is the often stated benefit that building a system based on an off-the-shelf product will avoid technology lock-in and avoid the problems of supporting proprietory software. However, I believe this goal is rarely, if ever achieved. It is highly likely that the parts of an off-the-shelf system that have been customized and tailored for a particular client are also likely to be the parts of the system that add most value to the client (by the very nature of those parts having been written to satisfy a specific client need).  So in many cases, the most valuable parts of system are also likely to be 100% proprietory with no support available from either the off-the-shelf product team or the open source community.

It is unlikely that designing and building an RDBMS from scratch would be a necessity for a given software project, therefore it is likely to be a good idea to use an off-the-shelf solution for this type of specific system component. The same goes for other system components like computer languages, operating systems, communication protocols etc. However, depending on client needs, the design and implementation of a new type of RDBMS maybe exactly what is required. This is to say that even with a bespoke project a vast amount of functionality can be provided by pre-built technologies. However, if a key requirement can not be serviced by pre-built technology then it will have to be built from scratch - drupal or no drupal.

There are many great and often free tools that software engineers can use to build high-quality applications quickly and effectively. An experienced and expert software engineer should be able to choose which of these tools and technologies best fits a clients needs and deliver a bespoke system faster than using a 'one-size-fits-all' product.

I wonder if as the software engineering industry matures that we will see fewer 'off-the-shelf' 'one-size-fits-all' packages and move toward boutique software consultancies that delier high-quality solutions quickly and cost effectively. And more importantly that the software engineering industry follows in the foot steps of far more mature engineering industries.

Web usability and HTML: Uploading files and photos

More and more people use online document and photo sharing websites (dropbox, picasa, facebook etc). However, I am still amazed at the number of websites that require files to be uploaded and that do not offer a way to link to an online resource.

A classic example is adding a photo to an online profile or attaching a document to an application form. It would be really helpful to be able to either upload a file, or simply enter a URL to an online file. This is something applications developers could easily implement.

However, the ideal solution would be to change the HTML5 upload file selector to allow the selection of either a disk-based resource/file or a publically available web-based resource (via a url). This change would immediately make ALL web applications that perform file uploading more flexible with zero effort required from developers.

An obvious progression from this would be to allow browsers to access secured resources (private picasa galleries or files on ftp sites etc) - of course, only with the owners permission and with appropriate authentication.

Premium services and content - the price point is just wrong.

How much would you pay per month for access to Wikipedia? $100 per month? $10 per month? $0.10 per month? Nothing?

Wikipedia is probably not a great example because it recieves money via donations, not via subscriptions but the point is that most people would probably not pay $10 per month for Wikipedia. But when pushed people might agree that paying a few cents per month is so reasonable that even the most infrequent readers could justify the cost.

So if people are only prepared to pay a few cents per month for one of the worlds most innovative and used information resources, how can some services or websites charge significantly more? $10's, or even $100's per month? For example, the Wall Street Journal subscription starts at €1.50 - this is not a lot of money, but when compared with the vast numbers of free news websites and alternative sources, maybe this price point completely exceeds what a casual reader would want to pay?

However, great content can not be free. Currently there are two typical financing models for this content:

1. Somebody else pays - commonly via advertising
2. The consumer pays - commonly via a subscription or donation

 The first model is great if you like webpages full of adverts. The second model it still fairly immature, especially regarding price point. I would be happy to pay for high quality content, but I think individual websites should appreciate they are one small part of a very big Internet. There are hundreds of services and content websites and if they were all to charge €1.50 p/m we would end up paying €1,000s per year and in addition, we probably couldn't read/digest/use of the services or content that these sites provide.

I think the world of premium services and content could be revolutionised with micro-payments. I could see a service whereby I could see all of my website subscriptions listed in one place and how much I am paying for each. In addition, this service could act as a way to manage my subscriptions - both allowing me to cancel existing subscriptions and subscribe to new sites at the click of a button.

If such a system existed, it could also allow users to give feedback to the content providers, allow users to 'recommend' websites and share opinions about them.

This would be great for web service and content providers because they would not have to care about handling payments. And of course such a system would be great for consumers - having one place to manage all of their subscriptions.