NoSQL RowKey Design for Ordered Data in Azure Table Storage

azuredatabase-designdesignnosql

I store large quantities of GPS data in storage tables. Each partition can reach up to 1 million entities. Each GPS entity that is inserted into the table is actually sequential to the previous data inserted, so order matters.

Sometimes I need to perform the following query:

Get the previous/next 3 GPS entities from the current entity (within the same partition).

Options for RowKey design:

Create an incrementing integer. But how do I track what the current size of the table is? There is no way to get table row count, or to get the last inserted row.
Use DateTime Ticks. But how do get the previous/next entity using ticks?

I'm using the SDK version 2.0 in C#.

Best Answer

I think you will need to have a RowKey such Ticks + Guid - and I think you will have to specify a time-measurement precision that is meaningful for your GPS application. You want three data that are sequential in real time not just the recorded time. If you just use Ticks, then you'll end up with duplicates as described in this post: Why am I getting duplicate RowKey with DateTime.UtcNow.Ticks? Your time measurement system has a finite precision.

You will still need to query the partition for all entities in the partition. But you should then be able to identify data that is truly in time sequence to the practical precision of your system.

Related Solutions

An efficient design to store variables for different product lines in ER database

So I understand you don't want to have fields from data_* in item because they're not really the same thing. So how about something like this schema below? It's similar to your original design, but it adds a new Common_data table between the item table and the data_* tables.

Item
----
 - item_id
 - (other item-focused fields)

Common_Data
-----------
 - common_data_id
 - item_id - FK to item.item_id
 - shared_field_1
 - shared_field_2
 - (many fields that are already shared in data_a, data_b, and data_c)
 - data_type (can be "data a", "data b", "data c")

data_a
------
 - data_a_id
 - common_data_id  - FK to common_data.common_data_id
 - different_a

data_b
------
 - data_b_id
 - common_data_id  - FK to common_data.common_data_id
 - different_b

data_c
------
 - data_c_id
 - common_data_id  - FK to common_data.common_data_id
 - different_c

Pros:

simplifies your shared data, moving it all up to a common data table.
similar to existing design - maybe some of your existing code can be salvaged
new shared data only needs to be added to one place.
simpler to implement.

Cons:

might not be flexible enough if you think you will soon have data_d, data_e, etc... and then remove older ones.
still requires schema (and possibly code) changes when new data_*-specific fields are added.

I'd avoid going the EAV route unless you really need the flexibility of it.

Java – Class Design for JPA Entities with Multiple Tables Referring to Same Business Object

I think your confusion stems from the Product table's general lack of utility, since it contains an ID and nothing else.

However it's not a big deal. When defining JPA entities, I would start with the Products table and define each of the child objects as separate classes. Then include those in the Product class:

@Entity
@Table(name = "PRODUCT")
public class Product {

   @Id
   @Column(name = "id")
   private long id;

   @OneToMany(mappedBy="product")
   List<DataSourceProduct> dataSourceProducts;

   @OneToMany(mappedBy="product")
   List<MasterProduct> masterProducts;
}

Each of these child products would be define with the reverse @ManyToOne relationship with the Product table.

As you mention in the comments, you might rename these and maybe shift around some of the properties, but I think this structure should be a good starting point.

Best Answer

Related Solutions

An efficient design to store variables for different product lines in ER database

Java – Class Design for JPA Entities with Multiple Tables Referring to Same Business Object

Related Topic