Semi-structured and unstructured data

Storing photographs, pdfs, videos, and other useful files

Databases make the business world go round—we just can’t live without them. Before we had Microsoft SQL Server or Microsoft Access, we had smart plastic boxes with index cards to hold contact data or sales preferences. Now the information is held in CRM and many other database systems.

Yet as useful as databases are, until recently they were limited in the type of information they could hold. Previously, relational database systems were very good at storing numeric or short textual data, but poor at dealing with the type of data we typically use on a day to day basis such as reports and photographs. Semi-structured data such as multi-page documents, photographs or other large files had to be stored separately.

With Microsoft SQL Server 2005 and more notably SQL Server 2008 and 2008 R2, all that has changed. Happily, Microsoft SQL Server now enables this semi-structured data to be handled within the database. This gives a number of valuable benefits:

• Semi-structured data can be securely backed up and restored as part of the database
• Large text documents can be searched using powerful FreeText searches that return exact matches, fuzzy matches and inflections of verbs
• Large files such as photographs or videos can be stored on the file system, but searched and queried from the database
• Data security can be administered from the database, ensuring only authorized users have access to relevant information
• Geographic data such as latitude and longitude can be stored in the database

Storing unstructured and semi-structured data within the bounds of a database is ideal where valuable information is held within large text or other documents, eg sales proposals or site photographs.

In addition, XML data which is often used for web technologies can be both stored in the database, and relational output as XML.

We have extensive experience of Microsoft SQL Server, and in integrating unstructured data within applications and database systems. We are experienced in using spatial data types to create mapping applications, and we have extensive experience of XML.

Research indicates that 70–80% of data is currently not stored within a database. If integrating some of your unstructured or semi-structured data within a database might give business benefits, get in touch to see if we can help. Initial advice is provided free of charge→