Search code examples
sqldatabasestore

Storing DATA FROM ACTUAL GRAPHS in a database (sql?)

I'd like to store data from actual graphs. in other words we might the following for example:

  • paper: smith

  • finance type: outgoings

  • time | 0 10 20 30 ... etc

    amount | 10 22 31 44 ... etc

I would like to store the variables paper, finance type and for each the graph data given by time-amount. there will be other variables also (note the above example is fictional)

I'm not here to get solutions although I hardly know anything about databases. Would like to get started. When I type in Google 'store data from graph in database' all I get is information about sql graph types, node etc. I need just some direction for the actual tools to use (MySql or another database type? XML?). I will eventually want to extract the graph data of person and use that information. Google is not being my friend at the moment and I don't know who to ask personally

The database wouldn't be that big but will eventually run into 1000s of entries.

Solution

  • It is possible to model this in a database, but if you hardly know anything about them, you should start learning a bit about ER schema's, normalization (just up to third normal form) and the basic DDL and DML queries.

    Anyway, possible model with two tables:

    TABLE 'graphs'
 - ID
 - paper
 - finance type

TABLE 'graphdata'
 - ID
 - GRAPH_REF
 - TIME
 - AMOUNT

    In your table graphs, you put 1 line for each graph you have. You might have a graph for 'smith, outgoings', one for 'smith, incomings', one for "deloitte, reports"... that would be three lines. The ID is just a counter.

    In the table 'graphdata', you put 1 line for each data point. Again, the ID is just a counter. The GRAPH_REF is the ID of the graph in the 'graphs' table where this data-point belongs to.

    So for your example, you'd have the following graphdata rows:

    1 - 1 - 0 - 10
2 - 1 - 10 - 22
3 - 1 - 20 - 31
4 - 1 - 30 - 44

    Are you following so far? Now you can make a webpage (or an application, anything you can program that can work with SQL - even Excel or Access will work) that gives a user the choice to create a new graph, or select an existing graph.

    Creating a new graph would insert a new row in the 'graphs' table. Then, for each data point, you put a new row in the 'graphdata' table.

    When they select an existing graph, you fetch the data points from the graph, and display to them. Maybe they can add/delete points?