Jump to content

Ideas for Storing Graph Data in a Relational DB


Recommended Posts

I have a project down the road where I need to store Graph data in a relational database. I wanted to see what other people are doing in terms of table structure in a database to store graph data from various tests. The graphs could have a variable number of plots and also a variable number of points (say up to 1k pts) in each plot. Based on my limited experience with database design I was going to make 3 tables in the dB:

Table 1 (PlotData)

col 1 = PlotDataID, autonumber (primary key) integer

col 2 = PlotID_fk, (foreign key) integer

col 3 = PlotDataX, real

col 4 = PlotDataY, real

Table 2 (Plots)

col 1 = PlotID_pk, autonumber (primary key)

col 2 = GraphID_fk, (foreign key) integer

col (n) = (plot info ....)

col (n+1) = (other plot info)

Table 3 (Graphs)

col1 = GraphID_pk

col(n) = ....(graph info)

col(n+1) =...(some other graph info)

Then JOIN them altogether on the primary keys to retrieve the data. My worry is PlotData table will get huge and take a long time to query. Has anyone gone down this road before? Is there a better way?

Thanks in advance

Link to comment
  • 3 weeks later...

In my opinion the following design is more flexible and faster

[TableData]

UniqueID Primary Key+Identity

PlotNumber U32 (bigint)

TimeIndex U32 or DBL

Value DBL

now you can create an indexing on plotnumber and timeindex. This should make your query fast and simple.

SELECT Value FROM TableData WHERE PlotNumber=0 and Timeindex>10 and Timeondex<1000

Greetz

Arnoud de Kuijper

T&M solutions

I have a project down the road where I need to store Graph data in a relational database. I wanted to see what other people are doing in terms of table structure in a database to store graph data from various tests. The graphs could have a variable number of plots and also a variable number of points (say up to 1k pts) in each plot. Based on my limited experience with database design I was going to make 3 tables in the dB:

Table 1 (PlotData)

col 1 = PlotDataID, autonumber (primary key) integer

col 2 = PlotID_fk, (foreign key) integer

col 3 = PlotDataX, real

col 4 = PlotDataY, real

Table 2 (Plots)

col 1 = PlotID_pk, autonumber (primary key)

col 2 = GraphID_fk, (foreign key) integer

col (n) = (plot info ....)

col (n+1) = (other plot info)

Table 3 (Graphs)

col1 = GraphID_pk

col(n) = ....(graph info)

col(n+1) =...(some other graph info)

Then JOIN them altogether on the primary keys to retrieve the data. My worry is PlotData table will get huge and take a long time to query. Has anyone gone down this road before? Is there a better way?

Thanks in advance

Link to comment
  • 3 weeks later...
[...]Table 2 (Plots)

col 1 = PlotID_pk, autonumber (primary key)

col 2 = GraphID_fk, (foreign key) integer

col (n) = (plot info ....)

col (n+1) = (other plot info)

I don't understand what is going into Table 2 - plot colors and point styles, stuff like that?

I like snooper's idea, but if you don't need to query limited ranges of data, maybe the best thing would be to store the X values and Y values as "blobs". Then you could put multiple plots' and graphs' worth of data into one table, as seems to be your intention, and only have a few rows. I'm pretty much a DB ignoramus so take this suggestion with a grain of salt.

In my limited experience of transferring data to/from LabView and DB, there is no problem dealing with a few thousands of rows in a table.

Link to comment
I don't understand what is going into Table 2 - plot colors and point styles, stuff like that?

I like snooper's idea, but if you don't need to query limited ranges of data, maybe the best thing would be to store the X values and Y values as "blobs". Then you could put multiple plots' and graphs' worth of data into one table, as seems to be your intention, and only have a few rows. I'm pretty much a DB ignoramus so take this suggestion with a grain of salt.

In my limited experience of transferring data to/from LabView and DB, there is no problem dealing with a few thousands of rows in a table.

Table 2 is just a link between the Graphs Table and the actual Plot Data in the Plot Data Table. Really you only need two columns for Plot ID and GraphID, the extra stuff is if you want to store information specific to the plot..like a color so that you could load the configuration into your graph property node when you query the db for a Graph.

I have been using a similar technique on an application recently and it hasn't had any problems yet, but the Plot Data table is still relatively small ...about a million points. I can query and return a 150000 pt plot in about 5 secs across the LAN. Not too bad. We'll see how it does as the DB grows. I'll let everybody know if there's an issue.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.