0

Say I have a database structure like this:

CREATE table Customer (id identity, name varchar(30), dateofbirth datetime, Photo varbinary, primary key (id))

CREATE table Order (id int, Date datetime, Primary Key (id))

create table CustomerOrder (CustomerID, OrderID)

Say I wanted to copy the database into DynamoDB for CQRS. The dynamodb table would have the following columns:

Name
DateOfBirth
Photo
List of Orders

However, if the customer places 100 orders then the same photo is stored 100 times. Is it normal to do this or should the photo be stored in a different table?

  • Why would it have 100 photos? From your description you'd still only have one row for the customer, with one photo, and with multiple orders in that row. – immibis Nov 20 '17 at 22:34
  • What do you mean by "copy"? – Constantin Galbenu Nov 21 '17 at 4:06
3

In the NoSQL world you do lose some relational behavior. However, you don't need to have complete redundancy of data.

In my case I created a pair of objects: SomethingRef which specified a lookup id and enough data for display purposes, and Something which extends SomethingRef for the full record. (Ref is short for reference)

Applying that to your case, an Order would have a CustomerRef object rather than copying the whole customer record. That gave me enough freedom to only duplicate the minimum relevant information, but provide more detail in the actual record.

That said, do not store binaries in a NoSQL document database. Most of them use JSON as your transport mechanism, which will de-serialize to a byte array, which in turn takes up run-time memory you can't afford to waste if you are trying to keep hosting costs down. Use blob storage like S3 or some equivalent and store the URL to the data in your Customer object. That way you can have the UI pull the image from blob storage directly, or give you the flexibility to stream through your app.

  • Thanks. Can you post any links showing how to do this? I am relatively new to AWS and DynamoDB. – w0051977 Nov 20 '17 at 18:20
  • I don't have a neat article that ties it all together. You can start with the API: aws.amazon.com/documentation/s3 and use the C# wrapper library to work with it. The rest is just discipline about how you organize your objects. Most NoSQL databases have a C# class to serialize objects to JSON documents and interact with the database. My tech stack was a little different than yours. – Berin Loritsch Nov 20 '17 at 18:29
  • Overall I think there is some good advice but NoSQL is not a description of a single technology. It encompasses a lot of different things and I think we are at a point where we need to qualify the advice around these databases based on what they are instead of by what they are not. – JimmyJames Nov 20 '17 at 19:30
  • @JimmyJames, true enough. While most people I run across are referring to document databases, which is what I've used, there's also key-value and graph databases to work with. DynamoDB, mentioned in the OP, is why I focused on the document NoSQL database. – Berin Loritsch Nov 20 '17 at 21:35
  • Added some specificity, it's a subtle update. – Berin Loritsch Nov 20 '17 at 21:37
-2

Yes, this is normal in the NoSQL world.

The idea is that you use NoSQL when the available storage is large in comparison to the volumes you'll actually be writing, so that you can simply shrug off the non-optimal storage requirements. Obviously, duplicated image data is a particularly gross waste of space because images are so large compared to bits of text, but the NoSQL programmers I know would indeed shrug this off as well.

  • can you confirm that the NoSQL programmers you know would not be concerned about redundant image data? I just want to be clear. I will then accept. Thanks. – w0051977 Nov 20 '17 at 17:36
  • 2
    Don't store images in a NoSQL database. Store it in blob storage like S3 and reference it in your record. Having 100 copies of a URL is better than having 100 binary images. – Berin Loritsch Nov 20 '17 at 17:49

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.