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

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

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 - Cannot secure connection using TLS -

-
i'm trying establish secure socket connection between java client applet (built jdk 1.7.0_75-b13) , vc++ server application. as test vehicle, used vc++ client/server sample found in msdn forums, modified use schannel , able establish socket using cipher suite tls_rsa_with_aes_128_cbc_sha. works of tls 1.0/1.1/1.2. when try opening socket java applet same server application, connection rejected server reporting following: tls 1.0 acceptsecuritycontext failed: 0x80090327 tls 1.1 acceptsecuritycontext failed: 0x80090331 tls 1.2 acceptsecuritycontext failed: 0x80090331 this java code used create socket: debugprint("setting secure connection"); sslsocketfactory sslsocketfactory = (sslsocketfactory) sslsocketfactory.getdefault(); sslsocket sslsocket = (sslsocket) sslsocketfactory.createsocket("127.0.0.1", socketnumber); debugprint("starting handshake"); sslsocket.settcpnodelay(true); sslsocket.setsolinger(false, 0); sslsocket.s
Read more