Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

All I learned in school was SQL which saves data to tables. Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG).

The XML itself contains over one thousand coordinate points, plus additional information on the data.

In my opinion it would make no sense to store this information in tables. Besides I couldn't store JPEG-files with SQL either.

What would be appropriate solution, or is there an error in reasoning on my side?

As you can see I am pretty new to databases. So any constructive suggestions, links and advice is welcome.

share|improve this question
    
SQL Server can certainly store JPEG files using IMAGE datatype. I would not recommend that, however. You would be better off using FILE-STREAM. –  datagod Feb 20 '12 at 15:56
    
When it comes to storing jpeg (or any other file) in the database, that's been handled here on some of our most frequently asked questions. As to the manner of storing XML and then finding that data quickly, that's pretty much what document data storage systems are for. I would look at a NoSQL solution over a relational database, it's going to be easier to work with I would imagine. –  jcolebrand Feb 20 '12 at 16:38
    
When it comes to XML, IBM's DB2 allows you to do it. And you can query the data with either SQL or XPath/XQuery. Their free Express-C edition comes with the ability to do this. If you move to Enterprise Edition, though, you have to pay to turn this feature on. –  Chris Aldrich Feb 20 '12 at 18:34
add comment

4 Answers

up vote 11 down vote accepted

All you need is persistence of your XML. Use a NoSQL solution or the file system.

There is no benefit in using an RDBMS, unless you want to use it instead of NoSQL or the file system.

share|improve this answer
    
What would be a appropriate NoSQL solution? I am working with C# in a winform application btw. –  bodycountPP Feb 20 '12 at 9:26
    
@bodycountPP: I have no experience with them... –  gbn Feb 20 '12 at 9:49
7  
I would check out RavenDB (ravendb.net) or CouchBase (couchbase.com/couchbase-server/overview) for use on Windows. Others can be found via the Googles - I've had a good experience with those products. Obviously your mileage may vary :) –  ITHedgeHog Feb 20 '12 at 10:04
add comment

I bookmarked Phil Factor's blog post Normalisation and 'Anima notitia copia' today as it neatly summarises the case for and against normalising certain types of data. Run the following query on a SQL instance and see if you agree.

SELECT * FROM sys.syslanguages

SQL enables you to create relational databases. However, even if it smells bad, it is no crime to do hideously un-relational things with a SQL Database just so long as it’s necessary and you can tell the difference; not only that but also only if you’re aware of the risks and implications.

You mentioned that the XML file contains "additional information on the data". Is there any benefit in modelling that metadata in a relational database, for the purposes of interrogation perhaps? If so, there may be a case for extracting the relevant data and persisting the remaining XML as an XML document type.

...if you are passed a JSON string or XML, and required to store it in a database, then all you need to do is to ask yourself, in your role as Anima notitia copia (Soul of the database) ‘have I any interest in the contents of this item of information?’. If the answer is ‘No!’, or ‘nequequam! Then it is an atomic value, however complex it may be.

Phil Factor's argument is that non-relational fields in a relational database is perfectly acceptable if the field is treated as atomic i.e. it doesn't change, or when it does the whole field changes, not a constituent part of it. The natural extension of this being that if your document contains elements you do have an interest in, there may be value in applying a relational model to those elements.

Relevant to the question but primarily for the phraseology, one last quote from Phil:

Naturally, I’ve never knowingly created a database that Codd would have frowned at, but around the edges are interfaces and data feeds I’ve written that have caused hissy fits amongst the Normalisation fundamentalists.

Haven't we all!

share|improve this answer
1  
ACORD is a bit like that. I've seen people trying to shoehorn it into serving as database model. ACORD subsequently went and licensed a data model from Prima, so even they admit it wasn't much of a data model. The ACORD messaging standard has about 7,000 fields defined of which around 200 are mandatory - the best description of ACORD (from someone heavily involved in messaging standards) is that the "don't know how to manage a standards process." –  ConcernedOfTunbridgeWells Feb 20 '12 at 13:55
add comment

As far as Oracle databases are concerned, the answer is you can't. All data in a database is stored in tables, even meta-data. Data can be stored in queues, but those are just a different way of using tables. XML files can be stored outside of a database, but that wouldn't meet your "in the database" requirement.

Moving beyond your stated question, JPEG files or any files for that matter can be stored in a database. They would require a table and a LOB column (BLOB or CLOB). XML could be also be stored this way, but importing the XML data into the database would allow you to operate on the data in ways that cannot easily be done now. It would also provide other benefits of databases including: Reduced Redundancy, Accessibility, Concurrency, Scalability, Interoperability, Security, Recovery, and Performance.

If the benefits of a database don't further your goals, then don't use one.

share|improve this answer
add comment

It sounds to me like you are trying to implement a spatial database. This is a kind of relational database (or an add-on to existing database products, e.g. Oracle Spatial, postGIS) that supports spatial data types functions and is used to optimise storage and queries for data relating to spatial features such as polygonal boundaries, points and layers. The XML you have sounds like boundary coordinate pairs and the associated imagery sounds like artwork assets to display in that boundary. If the data fits, consider a relational spatial database that offers built-in spatial modelling to provide functionality that you would otherwise find difficult or time-consuming to implement.

share|improve this answer
    
Additionally, if you are implementing a Geographic Information System, consider asking this question over on Geographic Information Systems. –  darvids0n Feb 21 '12 at 3:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.