Storing a drawn graph information in database for web application

Question

Well while developing a web application that allow users to draw graphs (flow charts, ER diagram , UML, .... etc) the information of drawn items and their relation to each other and position on canvas is expressed as JSON object

The question is:

1- is saving such graph in database as raw (json object text) is enough (in one field of a table) 2- or is it always required to break down the json object into logical pieces and store them into a well built database structure!

option 1- other than making devilopers life easier it will eleminate the need of recreating json object from small bits and pieces of information when the user open a graph he have built and saved long time ago

option 2- will have fine grain details in proper structure but will cost kind of heavy processing while opening a saved graph from Database

which way is correct way to go ?

Answer 1

Some considerations against one giant object are:

1-Time to do specific queries (for example list properties of Customer object should not require loading a model with 200 objects)

2-Time to save changes in small number of items (for example when you change the Customer object's name, you only need to save that and not the entire model)

3-If more than 1 user will be interacting with your system, you can't use 1 huge object without the appropriate concurrency checking otherwise some users work may be lost due to overwrites.

Now if you want save the user's model into separate objects, you need to have good database design for your meta model. Such a meta model is not trivial to design.

Answer 2

is it always required to break down the json object into logical pieces and store them into a well built database structure

Well, I don't know who tells you what is required and what not, but in most typical use cases I know of it won't make sense. A flow chart, an ER diagram etc. is some kind of graphical document where it seldom makes sense to

• edit the document concurrently with more than one user on it (I have seen some trials in the past, but never one that worked well)

• query for things like "get me all graphs with a line starting at point x1,x2".

However, what makes sense is to store some meta-data in standard table fields associated with your JSON string. For example, the diagram title, the author, time of creation, type of diagram, category tags, a short description, geographic information, a version number and so on. Think about what kind of search queries you really expect, those kind of data you should model explicitly.

Answer 3

Splitting a user's drawing up into nodes and edges (and all their associated attributes) and storing them in tables would give you the advantage of being able to query a drawing.

The question is: do you really need that functionality? Or could you get away with just storing some metadata with the text representation of the graph?

Consider that there will be a lot more work involved in getting a drawing out of the database if it has many nodes, compared to extracting one large text field.