Friday, 5 January 2018

What is SmartDB

What is #SmartDB ?

This question came up in the twitter discussion about #SmartDB, and all the advantages it brings (link to twitter).

Over the last year or so (and way before that, with the Helsinki-declaration in 2008), Toon Koppelaars has given us the reasons and guidelines for #SmartDB, and it boils down to “do the work in the database” (correct?)
So the shortest description, IMHO, would be : 

Any IT system using a database should do as much as possible of its processing inside the Database, and as little as possible of its processing in other layers.


TL;DR ? Read no further.  ;-)

Background of this approach is that this would lead to least-complexity, least-components, least round-trips, least-overhead, and least-complicated troubleshooting (only one component to examine and fix… ). 

Also, my too-short definition doesn’t include (yet) the need to apply sound database-practices. Good IT systems start with good (system) design (based on requirements). It also  includes things like 3NF, ACID, notably resilience, and adequate security based on minimum-privs and minimum exposed surface. Then there are “scalability”, “upgrades” and “monitoring” to allow the system to remain in action over longer periods of time, and under various loads. Sustainability, if you like.

To me, all the above still makes sense. And I feel comfortable to design/build an IT system given those guidelines. 
Of course, some are not content with an extremely short definition, and others demand a more elaborate description or a how-to cookbook-guide. All of that, Toon and Bryn are trying to provide in various presentations, videos and white papers on the subject.

I’m going to add a a suggestion.

To better define and describe SmartDB, I suggest to follow these steps:
- Requirements, 
- Reasoning, 
- Recommendations.
Those three steps should lead us to a better description and thus to a better way to “evangelise” the SmartDB concept. 

To Elaborate each step:

The (list of) Requirements should state why SmartDB is needed, and which problems the concept tries to solve.

By following logical Reasoning, with the knowledge and technology available, we can explain how each requirement is addressed in what is the most efficient way known to us.

To finalize, we create a list of Recommendations (if not: Directives!), on how to implement SmartDB. The recommendations should, at first, not be connected to any particular database or programming language. Those details can be filled in later. Each "vendor" should do this for his own product, and hopefully stay within the concept of SmartDB.

The result of this exercise should be (yet another) white paper, 10 page max, and some presentation material that we could throw at Developers, Architects, Managers and even Dev-Ops and UX wizards to explain and convince them.

Some history: 
Long time ago, in a cubicle-space far far away, an OFA-standard was defined via a similar process in the early 90s
I am that old. I recall how this Oracle Flexible Architecture was created and "explained" (OFA was the mother of all Oracle Standards - link needed).
OFA was created roughly in those three steps: Requirements, Reasoning and Recommendations (Directives!) I’ve not checked the original paper for years but this is what I recall…

This proces of problem identification and reasoned solutions was clear and could be explained to most users (Sysadmins, DBA’s) at the time. The standard was widely adapted and cited in the Oracle-DBA world. 

So, to recapitulate, I think we should follow a similar path to define the SmartDB concept: Requirements, Reasoning, Recommendations. Those three steps should lead us to a better description and thus to a better way to explain, promote, and verify the SmartDB concept. 

(I know, I know, Bryn is going to say that all of the above is covered 11.2 years ago with EBR, and we only need 4 schemas… but still, it wont hurt to re-assert some items…)

Additional notes:

note1: I have barely mentioned Oracle (or PostgreSQL, or MySQL, sukkel-Srvr, or even sp-hna). The SmartDB concept should not be locked into a particular product.
note2: I have not mentioned any theoretical or academic knowledge, but I would assume IT ppl to be familiar with handling requirements, IS-design, ACID, 0+12,  UX, some OOP, various methods of testing, etc… 
note3: I have not mentioned any procedural language, but SmartDB does imply the use of some stored-procedure dialect

note4:… there is much more, but it needs to be discussed. 


John said...

To ensure that the data conforms to the business rules 100% of the time.

A changeable database is mutable state. Simultaneous access to the database results in parallel processing of this state. The combination of parallel processing and mutable state results in non-deterministic behaviour. A non-deterministic system is essentially broken. In order to fix this broken non-deterministic system either the database needs to be made immutable or the parallel access to the mutable state needs to be synchronised to make it single threaded. Since an immutable database is not possible in most systems, concurrency control mechanisms need to be included to synchronise access to the mutable state. The mutable state includes both the data and any business rule that could be formally expressed in the logical model of the database.

The business rules are implemented within the DBMS declaratively where possible; assuming the implementation of the declared rule includes such concurrency control mechanisms as required to make all changes to the data within the database deterministic.
Where a declarative implementation is not possible the business rules are implemented procedurally, also explicitly including such concurrency control mechanisms as required to make all changes to the data within the database deterministic.
For an example procedural implementation in Oracle please see the examples Advance Mathematic for Database Professionals.

Toon Koppelaars said...

Hear, hear, John.
You should love SQL assertions, once they arrive (if ever).


PdV said...

John, Toon,

Thx for your reactions. I'm resurrecting this topic because, well.. "Good Architecture" remains an issue.

I will be presenting in Riga (RigaDevDays) and Bulgaria (BG-OUG) on #SmartDB. I noticed Bryn Llwellyn is also speaking at RDD, and I know he has specific ideas on #SmartDB.

@John: I Agree with your statements, but I have the impression your reasoning-paragraph describes "ACID" and then some. I would add that #SmartDB is more then just ACID. It is also a concept whereby you maximize the work/logic/code IN the database and minimize the work/logic/code "Outside" of the database. In the extreme case (Bryn?) this gives you a database that only exposes schemas that can do a given number of "whitelisted" calls to PL/SQL. Security as an added bonus.

Any other thoughts out there?

John said...


I understand that #SmartDB is more than just ACID. Having had some discussions with many intractable MVC/OO developers the #SmartDB advocates will need to come up with some very compelling reasons to move to this architecture.

I liked your 'Requirements, Reasoning, Recommendations' approach and this was my attempt at a first requirement. A number of these will need to be formulated in order to stand any chance of convincing others to use this architecture. Both to move the work into the database and then expose functionality via a PL/SQL (Oracle) or stored procedure (Other) shell.

To me, correctness is the ultimate requirement and so is the one I commented on. There are other requirements - performance, security, etc - which should have their own 'Requirements, Reasoning, Recommendations'.

Even having proved the incorrectness of concurrently processing data using unit tests in MVC/OO architecture - and also its correctness in a #SmartDB architecture; and having presented on the mathematical and logical basis underlying the relational model (to which, in my experience the vast majority of Database Developers are ignorant) I have still failed to convince management to move to a #SmartDB approach.

We will certainly have a hard-time convincing others of the benefits of the #SmartDB architecture in the current climate.

Marie Redington said...

Yes, the Oracle database is supported on Windows 7. You can submit SQL statements either as a client to a server or run the entire database on Windows 7, such as Oracle Database Express Edition 11.2.

managed services support