How to store data in a database without tables?

Question

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.

Answer 1

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.

Answer 2

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!

Answer 3

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.

Answer 4

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.