Recently I was having a discussion about how coding and design patterns have changed in RDMBS and if we can still call it RDBMS anymore. Very early in my career while attending an interview I was asked what a relational database is, my answer was “anything that stored data in a row and column format and dependencies can be established between these tables would be a relational database”. The Interviewer laughed at my answer and said by my definition EXCEL would also be an RDBMS to which I replied yes, even though I knew it wasn’t, I didn’t get through, but 10 years later worked for that company as a consultantJ. While the interview obviously let me know that my fundamental understanding of RDBMS was flawed I couldn’t help but ask myself why isn’t Excel an RDBMS, I can certainly do a lot of the stuff I do within SQL Server within it.
Coming back to my discussion we mainly talked about how PK and FKs are rarely implemented anymore, how CRM applications create copies of the tables and how consistency isn’t as important as it was before. As we discussed further I started comparing the databases and Entity models created now with Excel and here are some of the comparison I made:-
-An excel work book is a database it can be copied, migrated, upgraded, it can be backed up and password protected (not exactly TDE but still better than nothing).
– A Worksheet within a work book is like a table, it stores rows and columns and column headers.
– You can have computed columns within the worksheet by adding a formula to the column, data in the tables can be grouped, sorted (order by) and filtered (where condition).
-You can JOIN data from two worksheets using VLOOKUP function
– You can create a view by building a Pivot table on top of the existing data from a worksheet.
– You can create PK and FK type relationships within Excel by using a Drop down list and referencing the dataset off the main worksheet.
-You can have programmatic access to data within the worksheets using MACROS which can be saved just like Stored Procedure, the Macro is specific to the Workbook just like procedures are specific to the database.
– You have checkpoint like behavior with Auto save.
– You have atomicity in Excel by default since DML to any cell within the workbook is all or nothing and each cell is its own transaction.
– Isolation is achieved because when you open a Network shared Excel Workbook it opens in read only mode. Think of the database transactions as being is serializable isolation level.
– Durability is achieved because unlike Auto save the actual commit is implemented by pressing the save button itself, more like an explicit commit in the DB
– Consistency – While not truly following consistency, the data still returns to consistent state if you rollback ( Ctrl +Z or Undo) , circular references and PK and FK issues raise error thus preventing consistency issue to begin with.
With ACID making way for BASE it’s acknowledged that Traditional RDBMS rules have been too strict to work well within the design challenges of a highly concurrent distributed landscape we have today. While Excel isn’t BASE I think the future of databases is Excel-Like data stores that provide developers with the flexibility the need to store any kind of data any way they see fit. In other words the rules of the RDBMS are decided by the developer using the database at design time and not a constraint within which the developer has to code.
References
Please Consider Subscribing
