Sql-server – How to store x,y graphs: (a) Using x,y columns for each point, or (b) to store the set of points as comma separated strings

database-designsql serversql-server-2016

We are designing a database to store the X,Y graph data of electronic devices apart from other entities.

Business domain description

  • We have a collection of objects A, each object A has a collection of objects B.

  • Each object B has a collection of X,Y points forming a graph.

For example:

  • Object A has a collection of ~30,000 to 50,000 object B instances.
  • Each object B instance has X,Y graph data of about 200 to 250 points of double values.

Expected data size:

  • ~ 100,000 A instances
  • ~ 50,000 B Instances in each A
  • ~ 200 graph points

Number of rows:

  • 100,000 * 50,000 * 200 = 1,000,000,000,000 = 1 trillion.

Requirements

  • The users are interested in fetching the entire data set of an object B instance, not of a single point.

  • The users are interested in getting the X,Y points of all the B object instances for a given object instance A.

  • Users rarely fetch, probably like one or two queries in a day for a given object instance of A.

Considerations

Design 1

Standard-approach design in the simpler form to design the database to retain point data:

enter image description here

  • Pros: The data is stored "directly" so retrieval doesn't need any conversion.

  • Cons: The table ends up with trillion of rows, we need to do table partition to get quicker access of data.

Design 2

To store as comma separated values:

enter image description here

  • Pros: Data table size is confined to the instance counts of Object B.

  • Cons: Involves using string split to convert the string to rows or to give the data in the comma separated format to the user.

Best Answer

There's a third option: store column 2 and 3 together, so like

X1,Y1,X2,Y2,X3,Y3

If you're absolutely sure you never need to sort or filter on these coordinates, and rely on application logic to create, update and process them, it's fine to store them as a 'blob' in a single column.

Related Question