Taking a look at NoSQL
At work I have been tasked with writing a new version of the application server which is considerable different then the current version running. Since I want to restructure the database I have decided to take a look at how I can improve the Data Dictionary (that what defines what and how things should be put in the database).
The problem is as followed our security software knows many devices (many different cameras, intercoms, fire alarms, door locks and so on). We don't just support one vendor, but any vendor the customer wants. Of course each vendor has it's own method of implementing it's configuration. In the old software we had one huge table called "devices" (no points for originality there) that contained every device of the customer. The table contained not only the type and locations but also the various properties in a single row. There are two other alternatives.
One in which we create two tables where one holds the generic device description and the other which contains multiple rows for one device. This is an inefficient format.
The other alternative is that for each type and brand we create a new table. So we have a table for acti_cameras, axis_cameras, sony_cameras and so on. I think we support over at least a hundred devices so that is likely to go wrong.
So I have been looking at alternatives like NoSQL database. NoSQL is not a formal term but in general it means "Not Only SQL". It's a new development inside the database world and one I think that is certainly worth looking at. The first SQL was created in 1974 which in our line of work is really old. I also want to point out that it can only be so old because it has proven it's value. NoSQL on the other hand is from 1998 and the most common flavors are Key-value and Document store and graph.
Document store is in my opinion the coolest one as you can often just dump the data in any table. You don't need to look at the format. This would mean that we can still freely define types but at the same time we can also have different values and parameters.
There is only one downside: Eventual Consistency.
I understand the reason behind it (delaying writes while allowing reads) but there are plenty of situations where it is not acceptable. Sure you can catch it in your application layer but again that is not what you want.
Currently I think the best method is using MSSQL where a row also contains an XML document.