SQL: Old Soldiers Never Die

Structured Query Language (SQL) has been a phenomenally useful language for the relational database era. But I see that era coming to a close.

One of the primary flaws is SQL allows for database Alters, Drops, Updates and Deletes. When diskspace was expensive this made perfect sense, but with the unlimited disk resources we have today a greater principle holds true: NO SCHEMA OR DATA SHOULD BE ALTERED, DROPPED, UPDATED OR DELETED.

A second flaw is the lack of interactive modification of the schema in real time. Changes still blow most applications all to hell.

A third flaw is supertype/subtype hierarchies. Such things should not be hard coded into a design.

That being the case SQL has four unnecessary statements just waiting to be abused. We need a better language. In fact, we need a better database architecture.

A new language would provide no means for updates or deletes. I created the first Releases of this language I called “Structured Thinking Language” (STL).

STL has the following commands:

  1. CREATE – affordance concept (creates entities)
  2. DIRECT – affordance context (relates entities)
  3. POSIT – affordance method (entity output)
  4. OBJECT – affordance pragma (entity input)
  5. NEGATE – affordance cosmos (entity security)
  6. INTUIT – affordance chronos (entity manipulation)

As you can see there are no means to delete data.

Each entity (noun) has only one “attribute” in the relational ERD sense and each entity value is unique.

Each relationship between entities is called an direction with a subject, verb and object.

What we are actually dealing with is a database that has data states. Data being no longer affected by Alters and Deletes are instead affected by change of state without physical alteration or deletion.

After looking at STL recently I realized I had created a command language for an existing database architecture: The Associative Model of Data by Simon Williams.

The Book on the Model and a free copy of the Enterprise Edition software is available here.

An old release of STL can be found here.

Advertisements

Structured Thinking System: Methods

Give a man a fish and you feed him for a day.
Teach him how to fish and you feed him for a lifetime.

Lao Tzu

sts-entities.jpg

  1. CREATE refers to the Verity. Make a Moral Law.
  2. RELATE refers to the Unity. Make a favorable Command.
  3. REPORT refers to the Quality. Make a favorable Discipline.
  4. RECORD refers to the Quantity. Make favorable Training.
  5. AFFORD refers to the Safety. Make favorable Terrain.
  6. ENGAGE refers to the Relief. Make a favorable Climate.

Related Links:

STL: Shakedown R0.4

structured-thinking

I believe we are on an irreversible trend toward more freedom and democracy
– but that could change.
Dan Quayle

I’ve been playing with writing STL code for a couple of days now and have been working out some major logical issues. Actually trying to write code instead of syntax that is logical has shaken down the Six Hats, Six Coats Framework considerably. Sort of like dismantling and rebuilding a Chevy and then taking it on its first drive through the neighborhood without a muffler.

One of the things I have discovered is that Structured Thinking Language is best for describing Structured Thinking Systems (The Six Hats, Six Coats Framework). So let’s take a look at what I found.

First, we will go over the revised verbs and nouns. Here are the Structured Thinking Verbs:

stl-verbs.jpg

  1. CREATE refers to the extistential. Capability. Right a wrong.
  2. RELATE refers to the unity. Portability. Have a mantra.
  3. REPORT refers to the benefit. Reliability. Unique and valuable.
  4. RECORD refers to the cost. Profitability. Have a business plan.
  5. AFFORD refers to the usability. Security. Easy to adopt.
  6. ENGAGE refers to the convenience. Availability. Spawn evangelists.

And Here are the Structured Thinking Nouns:

stl-nouns.jpg

  1. MOTIVE refers to the rule hierarchy. Moral Law.
  2. PERSON refers to the people hierarchy. Command.
  3. OBJECT refers to the data hierarchy. Discipline.
  4. METHOD refers to the function hierarchy. Training.
  5. LOCALE refers to to the node hierarchy. Terrain.
  6. MOMENT refers to the event hierarchy. Climate.

This gives us our Structured Thinking Framework:

structuredthinking02.jpg

What we have as a result is the meshing of six horizontal hierarchies and six vertical hierarchies.

Next, we create all of the entities. There are six entities per noun.

CREATE	CreateName
	MOTIVE	(	Virtue,
			Unity,
 			Esteem,
 			Accord,
 			Safety,
 			Entity
 		) 

 	PERSON	(	Creator,
			Leader,
			Patron,
			Member,
			Friend,
			Teller
 		) 

 	OBJECT	(	Motive,
 			Person,
 			Object,
 			Method,
 			Locale,
 			Moment
 		) 

 	METHOD	(	Create,
 			Relate,
 			Report,
 			Record,
 			Afford,
 			Engage
 		) 

 	LOCALE	(	ExtraNet,
 			InterNet,
 			IntraNet,
 			ExtraNode,
 			InterNode,
 			IntraNode
 		)
 	MOMENT	(	Year,
 			Month,
 			Day,
 			Hour,
 			Minute,
 			Second
 		);

Next we relate the entities to one another. The keys are surrogates, so they are not visible. I am building a set of relationships from left to right on each row and a set of relationships top to bottom on each column:

RELATE 	RelationshipName
 	(	MOTIVE.Virtue 	TO MOTIVE.Unity,
		MOTIVE.Unity	TO MOTIVE.Esteem,
 		MOTIVE.Esteem 	TO MOTIVE.Accord,
 		MOTIVE.Accord 	TO MOTIVE.Safety,
 		MOTIVE.Safety 	TO MOTIVE.Entity
		MOTIVE.Mantra 	TO PERSON.Creator,
 		PERSON.Creator  TO OBJECT.Motive,
 		OBJECT.Motive 	TO METHOD.Create,
 		METHOD.Create 	TO LOCALE.ExtraNet,
 		LOCALE.ExtraNet	TO MOMENT.Year
 		PERSON.Creator 	TO PERSON.Leader,
 		PERSON.Leader 	TO PERSON.Patron,
 		PERSON.Patron 	TO PERSON.Member,
 		PERSON.Member 	TO PERSON.Friend,
 		PERSON.Friend 	TO PERSON.Teller,
		MOTIVE.Unity 	TO PERSON.Leader,
 		PERSON.Leader	TO OBJECT.Person,
 		OBJECT.Person	TO METHOD.Relate,
 		METHOD.Relate	TO LOCALE.InterNet,
		...
 	);

This gives us the following entities composing our Structured Thinking System (STS):

stl-entities-03.jpg

As you can see, the order of the columns have been changed. You can also see that I have changed the color coding of the hats and coats to better reflect common usage in the industry (ie. Black Hat = Secure). I also think I am coming more into line with de Bono, but the jury is still out on that one.

Another issue raised in making the relationships is they are one to many as they proceed left to right across the rows and one to many as they proceed down the columns. There is no compromise to this if the system is to work at peak effectiveness.

There is no need for normalization or denormalization as the structure is fully normalized. There is also no need for attributes because they are identical for every entity:

  • Motive
  • Person
  • Object
  • Method
  • Locale
  • Moment

I am at a turning point here. I have to go deeper into the model to determine how to create attributes. Which I have not yet attempted. I have to save it for later posts.

Now we can create our reports. This is an alternate function of the six verbs that occurred to me. Note that the selected cells are all adjacent to one another either horizontally or vertically and flow from left to right; top to bottom:

REPORT	ReportName
 	(	MOTIVE.Esteem,
 		MOTIVE.Accord,
 		PERSON.Member,
 		OBJECT.Method,
 		METHOD.Record,
 		METHOD.Afford,
 		LOCALE.IntraNode,
 		MOMENT.Minute
 	);

Giving us the following Report:

If you want to throw in some filters it is easy:

REPORT	ReportName 

 	(	MOTIVE.Esteem,
 		MOTIVE.Accord,
 		PERSON.Member = John Doe,
 		OBJECT.Method,
 		METHOD.Record,
 		METHOD.Afford,
 		LOCALE.IntraNode,
 		MOMENT.Minute = 30 	);

The “30” aggregates to every 30 minutes.

Now we can plan our data capture. Again an alternate use for the RECORD verb. Again the cells for capture are all adjacent to the left or down:

RECORD	RecordName
 	(	MOTIVE.Esteem,
 		PERSON.Patron,
 		PERSON.Member,
 		OBJECT.Method
 );

This would create the following form:

Here we set up the affordances for the entities:

AFFORD	AffordName
 		RECORD.RecordName
	TO 	PERSON.Member;

Finally, we execute the RECORD Script and as the Member isn’t given the Member must log in:

ENGAGE	EngageName
 	(	RECORD.RecordName
	AND	PERSON.Member
	);

The code I have created here is a radical departure from the syntax releases I have come out with so far as I realized what the design was leading me to create. And that is the clincher. The design brought itself out. I have just been trying to follow it along.

What I am finding is there are not four verbs–Select, Insert, Update, Delete–but six–create, relate, report, record, afford and engage!

Related Posts:

Systema: Seven Hats, Seven Links

STL: Structured Thinking Language R0.3

I had a bit of an epiphany today. What I realized is that by structuring Structured Thinking Language as I have, everything can evolve as lists. Each VERB is simply the addition of another list to the NOUN you are working with.

Six Verbs: CREATE, RELATE, REPORT, RECORD, AFFORD, ENGAGE

Six Nouns: MOTIVE, LOCALE, OBJECT, METHOD, PERSON, MOMENT

Four Adjectives: INDUCED, DEDUCED and IMPLICIT, EXPLICIT

CREATE INDUCED|DEDUCED IMPLICIT|EXPLICIT
     NOUN
        (   nounname_1,
            ...,
            nounname_n
        );       

RELATE INDUCED|DEDUCED IMPLICIT|EXPLICIT
     NOUN.nounname TO
                (    NOUN_1.nounname_1,
                     ...,
                     NOUN_n.nounname_n
                );         

REPORT INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname
                (    attributename_1,
                     ...,
                     attributename_n
                );       

RECORD INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname.attributename
                (    constraintname_1,
                     ...,
                     constraintname_n
                );         

AFFORD INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname
                (    SELECT
                     INSERT,
                     UPDATE,
                     DELETE
                )
                ON
                (   NOUN_1.nounname_1,
                    ...,
                    NOUN_n.nounname_n
                );         

ENGAGE INDUCED|DEDUCED IMPLICIT|EXPLICIT
SELECT|INSERT|UPDATE|DELETE

Obviously, it still needs work, but we can see where the Structured Thinking Language adds value to the design process. SQL does have it’s place in data manipulation. However, STL has a place in data definition. See the related posts for background information on this syntax.

Related Posts:

Structured Thinking Language R0.3

STL: Structured Thinking Language R0.2

The more original a discovery, the more obvious it seems afterwards.
Arthur Koestler

I find I hated the Six Hats being six adjectives in STL R0.1 so I am changing them back to verbs:

  1. CREATE refers to the creation of entities. Meaning. Capability.
  2. RELATE refers to the creation of relationships. Uniqueness. Portability.
  3. REPORT refers to the creation of attributes. Value. Reliability.
  4. RECORD refers to the creation of constraints. Business Plan. Profitability.
  5. AFFORD refers to the creation of affordances. Adoption. Usability.
  6. ENGAGE refers to the creation of manipulations. Evangelism. Availability.

The Six Coats remain the unchanged nouns:

  1. MOTIVE refers to the rules of the system.
  2. LOCALE refers to to the nodes of the system.
  3. OBJECT refers to the data of the system.
  4. METHOD refers to the functions of the system.
  5. PERSON refers to the people of the system.
  6. MOMENT refers to the events of the system.

Giving us the following:

stl_r02_1.jpg

INDUCE and DEDUCE will be changed to the adjectives INDUCED and DEDUCED.

Now that we have the verbs, adjectives and nouns of STL we can work on Release 0.2 of the syntax:

CREATE INDUCED|DEDUCED IMPLICIT|EXPLICIT
     NOUN.nounname;

RELATE INDUCED|DEDUCED IMPLICIT|EXPLICIT
     NOUN.nounname
                (   MOTIVE.motivename,
                    LOCALE.localename,
                    OBJECT.objectname,
                    METHOD.methodname,
                    PERSON.personname,
                    MOMENT.momentname
                ); 

REPORT INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname.attributename; 

RECORD INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname.attributename.constraintname; 

AFFORD INDUCED|DEDUCED IMPLICIT|EXPLICIT
    NOUN.nounname
 	(	SELECT,
 		INSERT,
 		UPDATE,
 		DELETE
 	); 

ENGAGE INDUCED|DEDUCED IMPLICIT|EXPLICIT
SELECT|INSERT|UPDATE|DELETE
    NOUN.nounname.attributename.value;

That’s it for now. Time to get some sleep.

Related Posts:

Structured Thinking Language R0.2