Urban Whispers

At work today I came across an interesting email titled: "Message to Employees - a tale that is a bit worrying if it's true.... Stay Safe"

The text read as follows:

Warning - We have received a warning from the London Ambulance Service of activities in their area. Whilst the below behaviour is not common place in our area I have spoken with Greater Manchester Police and their risk assessment of the action is to circulate it as a potential'

The London Ambulance service have units closely associated with the Police based in South London who are basically Fighting Gang Crimes. The 'street gangs' in London (particularly South London at present, but it is sure to spread) have initiation tasks which new gang members have to carry out to be admitted to the 'gang'. The latest craze is to drive around, deliberately with no lights on their cars. The first person who 'flashes' them, points at them or sounds their horn at them, has to be followed by that new gang member in their car, who then has to fire a shot into that vehicle with no regard as to who is inside.

Our official instruction is that if we see a vehicle with no lights on, we are NOT to 'flash' it etc. and the advice to friends and family is that you should ignore any vehicles you see without lights. I would ask that you pass this info on to all your family, friends and colleagues and who knows, it may save a life.

A quick Google search of a few of the terms in this email revealed that it is an Urban Legend. Notice also that the News Shopper story was published in May 2004 and that the legend first came to light in 1993.

The first question to come to mind is why make up such stories? A story which causes surplus email traffic, incites fear in the reader, wastes the time and resources of law enforcement authorities and in this case could be the in-direct cause of a motor vehicle accident.

Urban legends however are far from modern as their name may suggest. These stories can be compared to modern day examples of Chinese whispers, which quite literally in the majority of examples have no more than a whisper of truth to them. Since the Internet revolutionised the world these Urban Legends have been able to spread like virus' around the world. Their spread being facilitated by email, word of mouth, SMS and in some cases even news publications and school syllabus', examples:

• Sneeze with your eyes open and they'll pop out.
• The Coriolis effect in bath tubs.
• Microwaves cooking food from the inside out.

One of the recent myths that made its way into the press was the Premium Rate Phone scam. It was reported in the news by several organisations including Saga that pressing 9 upon receiving a phone call saying "You've won a holiday." will put you through to a £20 per minute premium rate line. ICSTIS in fact confirmed that this was not the case and in order to be charged a premium rate one would need to call a premium rate number. Even then the maximum charge is only £1.50 per minute.

HowStuffWorks, states that Urban Legends are often cautionary tales; some morbid; some with morals and others that are just humorous. The origin of Urban Legends is often un-known and going back to the Chinese Whispers analogy they are open wholly to interpretation and therefore exaggeration. While their source is often a true story the end result is likely to bare little or no similarity to the original.

In conclusion Urban Legends such as the "Car Lights" have that cautionary element; but they also include a scare tactic. It could be argued that distribution of such stories is both unethical and potentially dangerous. Any such story should be backed up by a link to a recognised and respected publication and verifiable to from other sources. But sometimes; as demonstrated by the Premium Rate scam example, even this can sometimes be misleading.

My personal recommendation is if such an email arrives in your Inbox - treat it like spam and delete it. A Google search of a few of the phrases will often tell you whether of not it is an Urban Legend.

Sources: HowStuffWorks, News Shopper, New Scientist, Hoax Busters

Composite Primary Keys - Friend or Foe?

Effective data modelling can be a daunting task. Ensuring robustness, efficiency and normalisation are crucial and may determine the success or failure of the system(s) it relies upon. Fortunately the relational DBMS makes this task a lot easier by providing the infrastructure with which to model the data and the tools with which to do it.

One of the key practices when modelling data within the a RDBMS is the use of a unique identifier for each relation. A primary key. The primary key not only serves as an identifier unique identifier, it is also an index (speeding up searches using the key exponentially) and cna be used in relationships with other relations.

As such a primary key is often a single column of an integer data type with many database management systems providing a facility to have its value generated automatically upon insertion of a record.

It is also possible however to create a composite primary key containing two or more columns. Its their use that resulted in a heated discussion between myself and a fellow programmer recently. The arguments against their use are quite logical:

• Uniqueness is spread across multiple columns. This means that any queries require at least as many columns included as the composite key to guarantee a unique result.
  
  
• The primary key index is slower as it contains more than one column, this introduces a slight delay when inserting new records as the index may need to be rebuilt.
  
  

Suffice to say, I think this fear of composite keys stems back to their often inappropriate use in Access, where the inexperienced database designer is tempted to use several (maybe more) fields in multiple relations to ensure a user does not by accident, duplicate data:


Such use of a composite key is definitely not a good move, while it ensures in some part that the same customer is not added twice - it is grossly inefficient and adds unnecessary complexity. Many however hold the opinion that a composite primary key should never be used. This is where I disagree. Consider the following, often common scenario that models an order processing system.

Here, the order item relation uses a primary key of its own and the two foreign keys which relate to the order and product relations.

Each order may have one or more products but the same product cannot be added to the order more than once (the purpose of the quantity column is to specify the number of products). Using the above model, you must check using either a trigger or in the application using the database that a duplicate is not going to occur before adding a new product to the order.

Using a trigger, you could silently update the quantity should a duplicate be found. But without the use of a trigger or stored procedure there is no other way of ensuring a duplicate item is not added to an order. Enter, the revised model:

Here, a composite key in the order_item relation is used containing the order and product Id's. The uniqueness is now spread across two columns and their is no ID column.

In my opinion the use of a composite key provides several advantages:

• The uniqueness of each order item is ensured at the data model level without the requirement for a trigger. A side effect of this is two more advantages:
  
  
• Where a database is accessed by multiple applications, relying on these applications to check uniqueness for you is dangerous and cannot be relied upon, especially if you are not the one developing the applications.
  
  
• Using a trigger or stored procedure will solve the problem of uniqueness, but there is more overhead in processing a trigger than simply checking a composite key.
  
  
• The order_id and product_id fields will benefit from indexes regardless of whether a composite primary key is used. Using the composite primary key reduces the number of indexes on the relation.
  
  
• Although queries will require that you search using both parts of the key, it is rare that you would need to run a query which returns just a single line within an order.

It is my opinion that in some situations the use of a composite key is preferred and can be advantageous. Casting aside those diabolical access databases that use composite "mash up" keys, they can help achieve a level of security and maybe even efficiency within your database system.

Yes I did use Access for the diagrams - one thing it Excels at (pun intended) is rapid creation of ERD's.