snippetModerate
How to store data in a database without tables?
Viewed 0 times
withouttablesstoredatabasehowdata
Problem
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.
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.
Solution
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.
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!
SELECT * FROM sys.syslanguagesSQL 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!
Code Snippets
SELECT * FROM sys.syslanguagesContext
StackExchange Database Administrators Q#13455, answer score: 10
Revisions (0)
No revisions yet.