Is it ok to use JSON strings for relations in Oracle database -

-

some time ago joined project oracle (11.2) database. json strings heavily used in relational data:

  1. instead of using separate tables many many relationships, json string used. example: let's suppose have student , lecture tables. in our architecture student table have "lectures" column. contain json array lecture names (not lecture ids even, names). 1 many relations though, other's table id used instead of json.
  2. often whole objects stored inside json columns, instead of separate tables. student can have i.e. grades column have jsons { grade: 'a', date: '2015-01-01', lecture: 'mathematical analysis'}
  3. objects retrieved microorm , jsons parsed in c# code json strings become lists of simple objects. each json column have code this:

private string _lecturesjson; public string lecturesjson { { return lectures.tojson(); } set { _lecturesjson = value; lectures = value.fromjson<list<string>>(); } } public list<string> lectures { get; set; }

the whole architecture somehow mixing relational , document database paradigms. tried convince architect such solution bad, insists such architecture nowadays. agree there lot of going on in document databases world. different, isn't it? in opinion there @ least several reasons why bad:

  1. searching performance disastrous. e.i. calculating average grade mathematical analysis students. have read/pase json values.
  2. you cannot set indexes on json keys
  3. you cannot set constraints primary keys or foreign keys on json keys - data consistency threatened. of tables there no primary keys or foreign keys set @ (i mean 1 many tables data linked plain id),
  4. database size bigger needs - storing names of other objects, redundant - if referenced object name modified? seems have go through json strings , update names (as wrote use names instead of ids link data).

the application should optimised data querying, since of time searching , data exporting used.

am right? totally wrong or maybe there trend not aware of? there other reasons why architecture wrong?

you correct.

you can store clobs/blobs in relational db. not mean using relational db doc store trend, more of anti-pattern

if need doc store, try mongo or orientdb.

if need join data across multiple jsons horribly inefficient, having data in fields means can join on column. means need understands data modeling.

if need pull data in json, better make stored proc, or better yet rest api provides json tables, have flexibility provide other representation without affecting model.... description sounds don't need json.


Comments

Post a Comment

Popular posts from this blog

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall...
Read more

java - UML - How would you draw a try catch in a sequence diagram? -

-
Image
is there standard way of drawing try catch sequence diagrams? i've made attempt based on this feel end result doesn't feel right. code diagram based around: public static void save () { try { filehandle filehandle = gdx.files.external(file); filehandle.writestring(boolean.tostring(constantshandler.soundenabled)+"\n", false); (int = 0; < 5; i++) { filehandle.writestring(integer.tostring(constantshandler.highscores[i])+"\n", true); } } catch (throwable e) { } } my attempt note: know still need add loop. that's ok. fragments meant show conditional control flow in sequence diagram. superstructures puts limitations on use of fragments (see pp. 467 of ss2.1.1). should use critical region try part above. , option catch . however, keep telling can take quite freedom in using uml. it's language , such changes in life time. long reader ge...
Read more

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co...
Read more