Hello Kiddies,

We have a very special surprise for you today, a combined blog by me and Jon Conway!

Database design

What is a database and why do we care?

From wiki - " A database is an organized collection of data. The data is typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies).". For us it enables us to collate data in a defined manner.

Two main uses for a database in test and measurement - limits and results.

• We store how we want to run/control the ATE and what limits to apply to the results, then the database where those results will reside.
  
  
• An important issue is ensuring that the databases always show how a unit was tested, even over the evolution of the test specification. This means never deleting any records, but using version numbering to ensure the database is always accurate historically.
  
  
• Over time a well designed database will give a huge insight into production.

What does Database Design actually mean?

Our interpretation of initial database design is looking at the data and abstracting it down into the Third Normal Form.

Third Normal What?

Essentially this is where we abstract the information into their cohesive entities/components. Each table therefore exhibits cohesion with respect to the data it is holding, further compounded by the rule (this is were 3NF is satisfied) that all the fields are closely related to the primary key, or put another way -  " every non-key must provides a fact about the key, the whole key, and nothing but the key" ( taken from quote by Bill Kent).

Putting it all together - ERD

Entity relationship charts enable a design to be displayed diagrammatically.  An ERD enables you to map out the abstracted elements of the database, name them, and define who is linked to what (relationship). It is nothing more than a picture, you can draw it or use whatever tool you like. JC often uses Microsoft Access to prototype my database, define the relationships and draw the ERD. SW uses MySQL Workbench (left).

Design in the vertical, not horizontal plane.

This is a common design issue. Lets say you have a measurement of voltage, and you store this in a field called volts. Then a new requirement is that you store current, so you add a new field called current. By adding a new field you have changed the underlying design of the table, with various potential effects (relational integrity, impact on test software, related databases etc.)

This is what we would deem a horizontal design, where new data is added horizontally across the table. A better approach would be to design a table that describes a type of reading and it's associated result. So an attribute field would hold the reading type e.g. in our example volts and current), then when a new reading type needs to be added, for example impedance, it is simply a new entry and not a design change. If your database had fields named Widget1, Widget2, Widget3 you are on the wrong plane.

Always strive to design in the vertical.

Other helpful design stuff: -

Single valued facts. Making a list of facts about the database can be very helpful, they are akin to requirements, but at a lower database level e.g. The results table will hold all measured values.

Shoot the auto number - how can any table that includes an automatic number as its primary key exhibit relational integrity? Many relational database systems allow an automatic numbered field to be the primary key. If this is the only element of the primary then this is BAD ... duplicates here I come.

Got the database, what next?

Communicating with the database

We use good old fashioned SQL.

Why: -

• It's a standard that every relational database system will understand.
• Does not tie you to a particular product or technology.
• It's relatively easy to understand
• The LabVIEW interface is simple.

It is always a good idea to extract your SQL strings into a separate config file. Generally a relatively small set of SQL statements will serve a vast amount of any application. However, consider your embedded SQL code needs changing, say a field has to be renamed, you will have to hunt down all the occurrences of the relevant SQL strings. Far better to keep the SQL detail in a config file and use an interface component, via an enum, to pick off the relevant statement e.g. We want to select a customer from the customer table: -

SQL config file entry - Select Customer = " SELECT custName, custaddress, custContact FROM tblCustomers WHERE custName = '%s'"

To select the customer we want we simply use the string read form the confug file fed into a 'Format into String' vi to substitute the %s for the relevant field. Alternatively we can use a string pattern instead of %s and use the Match Pattern vi.

Mantra - abstraction in the code, detail outside the code.

DESCRIBE

Some databases (MySQL) have a very useful statement called DESCRIBE this allows a table to describe itself. Similar functionality is available in SQLite by querying the database description tables or SQL Server via a special function call. This allows us to write generic browse interfaces that can edit, insert etc. Maybe I'll talk about this in a later blog.

Lots of Love

Jonny and Stevey

PS If you understood the daft intro you too lived through the 1980s UK as a kid.

Apologies for the lack of LabVIEW but we felt that databases are becoming more important these days, so a little time spend here will hopefully pay dividends later.

Today I thought of an analogy to describe what I was doing to a bit of code. It had been handed around, added to and re-used and had come back from my customer with some additional requirements to do on it.

It very nearly did what was required  but not quite, so I found myself adding, adjusting and messing with the logic. I did all this with an air of frustration because I knew it was getting a bit smelly. I also really hate re-writing code.

It occured to me that this is like re-painting a door by just painting over it, eventually it ends up a lumpy unpleasant mess.

What I did was get the code-stripper out and had a look at the actual requirements, strip out the old code and re-write. It took an hour or so with lots of testing but I ended up with nice, easy to understand block-diagram. This paid dividends on delivery to the customer because they promptly found a load more new requirements for it, all solved nice and easily.

Code Smell#5:Over-painting

Code Deodorant/ Code-Stripper: Stop tweaking and go back to the requirements!

Only a very short blog today, but coming soon is a joint effort by me and Jon Conway, the first thing (apart form Christmas Cards) we've co-written for over 10 years.

Lots of Love

Steve