Ideas for Storing Graph Data in a Relational DB

[bbean]

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

[snooper]

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

[torekp]

[...]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.

[bbean]

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.