CODEWITHSUNDEEP
databasedatabase-designnosqlamazon-dynamodb
Alex_Dms
Alex_Dms

Reputation: 157

Design scenario of a DynamoDB table

I am new to DynamoDB and after reading several docs, there is a scenario in which I am not sure which would be the best approach for designing a table. Consider that we have some JobOffers and we should support the following data access:

Since we need to support sorting, my understanding is that we should use Query instead of Scan. In order to use Query, we need to use a primary key. Because we need to support a search like "get all JobOffers without any filters sorted somehow", which would be a good candidate for partition key?

As a workaround, I was thinking to use a new attribute "country" which can be used as the partition key, but since all JobOffers are specified in one country, all these items fall under the same partition, so it might be a bit redundant until we will have support for JobOffers from different countries.

Any suggestion on how to design JobOffer table (with PK and GSI/LSI) for such a scenario?

Upvotes: 0

Views: 114

Answers (1)

lynkfox
lynkfox

Reputation: 2400

Design of a Dynamodb table is best done with an Access approach - that is - how are you going to be accessing the data in here. You have information X, you need Y.

Also remember that a dynamo is NOT an sql, and it is not restricted that every item has to be the same - consider each entry a document, with its PK/SK as directory/item structure in a file system almost.

So for example:

You have user data. You have things like : Avatar data (image name, image size, image type) Login data (salt/pepper hashes, email address, username), Post history (post title, identifier, content, replies). Each user will only have 1 Avatar item and 1 Login item, but have many Post items

You know that from the user page you are always going to have the user ID. 100% of the time. This should then be your PK - your Hash Key, PartitionKey. Then you have the rest of the things you need inform your sort key/range key.

PK
USER#123456
    SK:
    AVATAR - Attributes: (image name, image size, image type)
PK
USER#123456
    SK:
    LOGIN - Attributes: (salt/pepper hashes, email address, username)
PK
USER#123456
    SK:
    POST#123 - Attributes: (post title, identifier, content, replies)
PK
USER#123456
    SK:
    POST#125 - Attributes: (post title, identifier, content, replies)
PK
USER#123456
    SK:
    POST#193 - Attributes: (post title, identifier, content, replies)

This way you can do a query with the User ID and get ALL the user data back. Or if you are on a page that just displays their post history, you can do a query against User ID # SK Begins With POST and get back all t heir posts.

You can put in an inverted index (SK -> PK and vice versa) and then do a query on POST#193 and get back the user ID. Or if you have other PK types with POST#193 as the SK, you get more information there (like a REPLIES#193 PK or something)

The idea here is that you have X bits of information, and you need to craft your dynamo to be able to retrieve as much as possible with just that information, and using prefix's on your SKs you can then narrow the fields a little.

Note! Sometimes this means a duplication of information! That you may have the same information under two sets of keys. This is ok and kind of expected when you start getting into really complex relationships. You can mitigate it somewhat with index's, but you should aim to avoid them where possible as they do introduce a bit of lag in terms of data propagation (its tiny, but it can add up)

So you have your list of things you want to get for your dynamo. What will you always have to tie them together? What piece of data do you have that will work?

You can do the first 3 with a company PK identifier and a reverse index. That will let you look up and get all a companies jobs, or using the reverse index all a specific job. Or if you can always know the company when looking up a specific job, then it uses the general first index.

Company# - Job# - data data data

You then do the sorting on your own, OR you add some sort of sort valuye to the Job# key - Sort Keys are inherently sorted after all. Company# - Job#1234#UNITED_STATES

of course this will only work for one sort at a time. You can make more than one index, but again - data sync lag is a real possibility.

But how to do this regardless of Company? Well you can have another index with your searchable attribute (Country for example) as the PK then you can query that.

Or do you have another set of data that can tie this all together? Do you have another thing that can reach it all?

If not, you may just have two items in your dynamo:

Company#1234 - Job#321 - details Company#1234 - Country#United_states - job#321, job#456, job#1234 Company#1234 - Country#England - job#992, job#123, job#19231

your reverse index here would apply - you could do a query on PK: Contry#UnitedStates and you'd get back:

Country#United_states - Company#1234 - job#321, job #456, job31234 Country#United_states - Company#4556 Country#United_States - Comapny#8322

this isnt a relational database however! So either you have to do one of two things - use t hose job#s to then query that company and get the filter the jobs by what you want (bad - trying to avoid multiple queries!) or each job# is an attribute on country sk's, and it contains a copy of that relevant data in a map format {job title, job#, country, company, salary}. Then when they click on that job to go to the details, it makes a direct call straight to the job query, gets the details to display,and its good.

Again, it all comes down to access patterns. What do you have, and how can you arrange it in a way that lets you get what you need fast

Upvotes: 0

Related Questions