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.
- 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.
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.
Standard-approach design in the simpler form to design the database to retain point data:
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.
To store as comma separated values:
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.