2

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.

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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.

  • Thanks for the suggestion, isn't going to be more work when we retrieve the data as we need to give the end user with two rows one for X values and another row for y values corresponding to the x values – Dinesh Gaddam Sep 19 '18 at 2:35
  • As an end user, I'd rather get a list of tuples; if they'd import it into Excel, they need two columns (not two rows) anyway. But the performance difference is probably negligible. – Glorfindel Sep 19 '18 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.