Is DELETE necessary?

A  delete is a very destructive operation for a database. An operation which requires a lot of  thinking and preparation and most of the time is irrevocable. You can get in a lot of trouble if you delete the wrong set of rows and lose all that very important data!

Maybe the DELETE command should be banned from SQL. It was probably invented when the disks were tiny and it might not be suitable for our times, especially nowadays when we talk about things like BIG DATA.

Why do we have to delete anyway? We should invalidate and age out data and never destroy it. I am one of those who would say ‘keep it’ and never lose it. Even a typo, an error while entering somebody’s name in a text field, tells us a story. The number of attempts to get it right, the number of characters typed which were wrong, the time the wrong entry took place and much more, is data. Temporal databases come to mind. Why delete it?

But, one can argue that not deleting can be dangerous, too! One can claim that by refusing to delete we might end up with a lot of old, duplicated, bad quality, uncleaned, irrelevant and untrustworthy data.
 

Maybe the worse thing that can happen to data is to refuse to delete it enough. Perhaps because of this lack of enough deletes and fear of deletes we end up with all the 'bad data' found in some  database systems today.

Whatever you choose to do, delete or not delete, one thing's for sure and that is that you should know what you are deleting or not deleting. Choosing confidently what to delete comes from understanding and knowing your data model, and the implications a delete will have on the data model and never because the data is not required for your project! 

Salesforce basic data model

All applications store data. Data is stored in database tables - or objects in case of Salesforce.  In this post I will try to describe in simple terms how to read and Entity Relationship model or sometimes called a Data Model. I give reference to further reading at the end of the post.

Data is related with other data based on relations and associations. For example, as in Salesfoce's data model One Account can have Many Contacts. In the model these relationships/associations are indicated by drawing lines from one object to the others as seen in the diagram below, where the one side line is a single line whereas the many side of the line has multiple lines like crows feet.

Below is how this is shown in Schema Builder application of Salesforce

This is a standard way of showing relations between entities in databases and thus objects in Salesforce.

Walking through some of the Standard Objects and their relations to other objects in Salesforce

There are 2 dozen Standard objects in Salesforce, below I have drawn with Schema Builder few core standard objects of the core CRM implementation, I list them here:

How to read the Entity Relationship (ER) diagrams

Lets start by looking at the above Entity Relationship diagram from Salsforce.

The Lead object is kind of standalone object is not related to any other objects in one way or another. There are no lines! Usually Leads are not associated with any Accounts or Contacts until they are converted into one of them.

The Account standard object is related with the Contact standard object. Follow the line from Account to Contact in the picture. The Account object can be related to other objects too, you can even relate it to Custom objects (your own objects created and added to the model). The model can be extended depending on what application you are trying to build. To read the model and understand it, you just follow the lines, starting from the Account object to the other objects and looking at the end of the lines. If you see a Crow's feet like 3 lines at the end of the line, that is  the 'many' side of the relationship and if you see single solid line it is the 'one' side of the relationship. As you see in the model Account relates to Opportunity and Quote objects too.

All you have to do is to look into one object and follow the lines coming out of that object.  A tip: is best to start looking at the relationship between objects from the 'one' side and not the 'many' side. Try on your own, check how the Product object is  related QuoteLineItem object.

The implementation of relationships between the objects is done by special fields (columns) which are  called keys. In databases are called Primary Keys and Foreign Keys. The posting/copying of these keys from one object to the other is what creates the relationships and establishes the Associations. 

Data modelling and Entity Relationship diagrams are processes used in Software Engineering to analyse business requirement and build a 'database' Information System. Salesforce is such an Information System.

For further reading on Entity Relationship diagramming you can see links below. 

• http://en.wikipedia.org/wiki/Entity-relationship_model - Wikipedia
• http://www.databasedesign.co.uk/bookdatabasesafirstcourse/chap3/chap3.htm - Database course

A search for 'Entity Relationship Diagram' in Google will return a plethora of other information.

Salesforce done a great job with Schema Builder. To obtain a Salesforce free developer account and have a look at it and start creating objects and see relationships go to: http://developer.force.com/

Oracle Apex 4.2 JQM Smartphone UI

Put a bit of a swing in your Oracle Apex mobile apps by using the JQM smartphone UI application interface now available in Oracle Apex 4.2.

The interface enables you to put page transitions to your apps, define JQuery lists and much more. Here is the option:

I have been playing around creating mobile apps in Oracle Apex for a while now trying various ways such as adding Javascript to HTML content regions, running javascript from PL/SQL, you name it. The JQM smartphone UI is a great improvement. See for yourself. Below is the JQM smartphone version of my famous :-) beachmap app (Twitter: @beachmap) Currently you can only browse beach locations, soon I will add ability to add your own beaches (crowdsourcing).

Enjoy the JQM transitions!

Mobile apps with Oracle Apex

I have been toying with the idea of using both Oracle Apex with JQuery Mobile as an application development platform for a while now. Why not? You get a great database and a Mobile UI design framework to built your mobile apps just using your browser!

I managed to create few apps on this platform. This post is about what tools I used when I was developing the apps.

Overview of using Oracle Apex and JQuery Mobile

This one was easy, just go to http://apex.oracle.com and register for a workspace. The workspace gives you an Oracle Workspace Schema, like a login (schema) to Oracle. Then you start developing Web applications just using a browser. Oracle Apex renders your pages in HTML and you use SQL and PL/SQL for your logic. Most of the pages are decorated with pre-built templates or custom CSS and templates you create.

Your app becomes a web app, read by browsers. To make it look like a mobile app you can use Jquery Mobile . A framework which enables you to further customise your Oracle Apex page regions and make them look like mobile apps.

Oracle Apex 4.2 - the upcoming release - will incorporate the JQuery Framework within itself as standard and building mobile apps will become native to Oracle Apex. If you want an Early Adopter release is available on this link: http://apexea.oracle.com

Below are few mobile apps I have been developing

(best seen from mobile phone or tablets)

The School Proximity app calculates the radius between two circles and draws a circle on Google Maps.

The Greekisland app is a mash up of Google Maps, Yahoo Weather and Google Charts APIs, dynamically querying the weather reading and temperatures on Greek islands. 

The beachmap app is a test I did on Custom Authentication with Oracle Apex. Where you can register with your email and add beach locations around the world.

All you need to do apps like this is two things really

• Oracle Apex
• JQuery Mobile

It was a great learning process. Now, with REST API and JQuery Mobile in the horizon for Oracle Apex 4.2, I see a great potential with Oracle Apex!

Big Data

The other day I watched the Oracle Big Data forum. Now available here. A half-day event with various speakers on the subject of BigData, including Tom Kyte , a mentor who I admire!

In the forum they have gone over Oracle's approach to Big Data and allow me to summarise it below:

1. Acquire - Collect Big Data, identify it, where is it? Then store it in Oracle NoSQL - a value-pair database


2. Organise - Stage Big Data in a transient elastic database. Using Oracle Data Integrator and the Oracle Hadoop connector, reduce and distil it.


3. Analyse - Start Analytics on the now acquired (reduced/distilled) and organised Big Data, using variety of Oracle Tools, like Language R, pattern chasing etc.


4. Decide - Present your data to the decision makers with dashboards, back into a relational database etc...

Looking at the summary above, it really describes Big Data as something as ... a distillation of a massive amount of data ... two questions come to my mind:

• Where is Big Data?


• Why do we want Big Data?

Answering the second question is easy. We want Big Data because is all about having detailed information to allow us to make better decisions. Classical "Your Boss's Decision Making" use of data, if you work for business. If you work in astrology probably the question will be: "Is there life in space?"

The first question, "Where is Big Data?" I think is the one which will probably take more time and effort to answer. Or maybe if we slightly re-write the question to "Where can I find Big Data useful for my Business?" would be even more correct to say, as Big Data must make money, too. In a summary, this was what Oracle said, or at least what I understood from it. Very interesting indeed. Oracle looks at Big Data as another data source which is cool too.

Below is my rumblings on the topic of Big Data

Let me start by saying that, there were many interesting examples used to describe Big Data in the Oracle forum. For some Big Data is things such as the heartbeats of patients collected. For others, as data collected from petroleum pipe sensors on oil rigs. Other descriptions saying, that BigData is sensor data, accumulated during flights (apparently 7TB on a flight between London and New York) and more Big Data is Machine Data or even Big Data is Dark Matter!

Whatever we call it, Big Data is massive! A beast which is usually always truncated, due to lack of space in a relational database - rolling window. A beast which we can not collect in a relational database. A beast, which has to be written quickly as its generation is too fast for a relational database table to catch up, or its schema is too constraining. A thing which cannot be visualised in the I/O of a single system kit, no matter how expensive and grand that system kit is.  Big Data seems like can only live in a cluster of system kits spread out and wide from Australia to the North Pole?

Wait a minute, does this beast fit in a NoSQL value pair database! Bob is your uncle! It fits somewhere, then. But where? In NoSQL. A loose database with no Constraints - where constraints and the whole database logic is in the hands of the web app developer. I am not defending relational databases here, just arguing on the new NoSQL theory. NoSQL, a database which is not based on the sound and proven mathematics of Relational Theory, Calculus and Algebra, a database which is hardly transactional and not consistent (eventual consistency) and integral. Hmmm.... Whatever, as long as Big Data fits in a NoSQL database and we can capture it, is fine. Capture now, analyse later. Just store and spread it out. Then bring in all the metal and CPU and memory in the world (in parallel) to crunch this. What a Great idea!

I am persuaded by Big Data. Not all data needs to be stored relational, and ACID is a theory of transactions and not a must have database property.

What bugs me is, now that we are taking away all constraints and logic from database data and allow it to grow to Big Data, when we look back at it, how quickly will we be able to relate other data to it and how easy will it make sense? I am just looking forward to any sort of easy to use prompt (like Pig and Hive) where I can write something like:

 Select * from BIGDATA;

but in the cloud, as I will never be able to afford the metal to host big data anyway, should be fun.