In search of database flexibility

Initially, to refresh my knowledge of ASP.NET and to get up to speed with SQL and relational databases (RDBs), I built a simple web site capable of storing information book and CD information. (Mainly this was based on the examples given in this book: Beginning ASP.NET 2.0).

However, even at this stage, I was beginning to realise that it was going to be difficult to expand the RDB tables to provide a generic means of storage. To solve the problem I tried applying some object orientated design, creating a base table, to contain all common information and from which all others would be derived. However, it doesn't appear that relational databases lend themselves particularly well to object orientation, and I was still left with the situation were a new table would need to be created for each new type of item to be stored.

To categorize the items added to the database, and as a means of navigating, I used a three level hierarchy of Department, Section and Category. So, for example, "Eyes Open" by Snow Patrol was stored into "Entertainment - Music - Indie". This seemed to provide a fairly sensible breakdown for music and books, but seemed that it might prove a bit restrictive for other sorts of item. It forced items to always be placed into a category (i.e. items couldn't be stored into departments or sections) and categories couldn't be broken down any further to give more specific classifications.

Indeed, when I came to try to store more esoteric information, I soon discovered that the rigid nature of my database made it very difficult to add anything that didn't conform to a 3-layer classification scheme. For example, I wanted to add information about buildings but, knowing nothing about architecture, I had no idea what parent category ("Department" in my classification scheme) architecture would belong to, nor what sub-categories it would contain.

Now, whilst I could have gone off and studied more about architecture to know how it should be classified, this wasn't really what I wanted. Instead I wanted a database that would give me the flexibility to add items at any level, plus the ability to reclassify items and insert hierarchies as my knowledge of a subject increased. Clearly my intial database wasn't up to the job. As a result, I've now set off in search of a database structure that can give me the flexibility I require.