New to NoSQL/MongoDB and needing advice with schema

Working with Data
,
1

Hi! I’m new to NoSQL Db’s and I’m having a seriously hard time figuring some stuff out. I’ve been watching some of the vidos en the MongoDB youtube channel and now I have a few guideliness to go with, however, I’m having a seriously hard time thinking what is the “correct” or “most efficient” way of building my documents for a specific project.

I have several little “books” (really tiny) with 100 tickets each and the books can have different prices per ticket. They all have a specic series and a correlative. for example, AA 10290 to AA 10390, exactly 100.

I need to prepare the “service” and give away a plastic bag with several of those books for them to go and sell each ticket. It should like something like this:

01 Huge Jackman

Book | N tickets
AA 1000 - 1100 | 100
AA 1200 - 1300 | 100
AA 1400 - 1500 | 100

When they come back, they could have sold the entire first book with 100 tickets. and come back with half of another.

AA 1000 - 1100 | sold out
AA 1200 - 1250 | 50
AA 1400 - 1500 | 100

Next “service” I prepare I will use the half sold book:

AA 1250 - 1300 | 50
AA 1400 - 1500 | 100
AA 1600 - 1700 | 100

And the process repeats.

This is where the problem arrises. How do I keep track of all those tickets? should I create a document with 2 arrays for each book like this:

{
  _id: 10293812
  for_sale: [ 1000, 1001, 1002, ..., 1100 ]
  sold: []
}

assign the object id to the service document (or should i embed the entire book document in the service document?) and when the employee returns update the arrays values from for_sale to sold?

According to some videos i watched, i should embed everything that can be embedded into just one document. So it should look like this:

{
  _id: 120938,
  employee: {
                   _id: 102398734
                   name: Huge Jackman
                   },
  date: 2023/01/01,
  books:{
             book1: {
                         _id: 7987234,
                         serie: "AA",
                         for_sale: [1000, 1001, ..., 1100],
                         sold: []
                         }
             },
            book2: {
                        _id: 76598,
                       seria: "AA",
                       for_sale: [1200, 1201,  ..., 1300],
                       sold: []
                       }
}

Each ticket of each book is unique with serie+correlative, I can’t have a repeated book with serie “AA” and correlative 1000 in any other service.

Thank you so very much for your guidance!

2

Hi @Mauricio_Ramirez

So the schema you need can be determined only by the access pattern of your application, for example do you need to update each sale and track what exact tickets were sold in real time? per employee or per book?

Or on the other hand do you only update the system once the employee comes back from his “shift”/“service”?

In MongoDB the same dataset can look totatly different for each one of the use cases.

For example, if you use the first approach and you need to query specific progress of a specific series as they progress with the sale you can end up with the following schema:

Books

{
  _id: "AA 1000 - 1100",
  book: "wordy",
  for_sale: [ 1000, 1001, 1002, ..., 1100 ],
  sold: []
}

{
  _id: "AA 1200 - 1300",
 book: "wordy",
  for_sale: [ 1200, 1201, 1202, ..., 1300 ],
  sold: []
}

{
  _id: "AA 1400 - 1500",
 book: "wordy",
  for_sale: [ 1400, 1401, 1402, ..., 1500 ],
  sold: [],
pricePerUnit: 24.99
}

Service

{
  _id: "01 Huge Jackman",
  booksToSell: [ "AA 1000 - 1100", "AA 1200 - 1300", "AA 1400 - 1500" ]
}

If you need to have a more traditional schema where you will you only care about the total amounts sold per series and preparing on a start/end service basis. The following schema can be a good start:

Book Collection

{
    "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
    "name": "Wordy",
    "pricePerTicket": 24.99
}

Service Collection

{
    "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
    "seriesStart": 1000,
    "seriesEnd": 1100,
    "availableQuantity": 10,
    "books": {
        "BookId": ObjectId("5f4d0c1234f6b8d7e98a1234")
    },
    "employeeDetails": {
        "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
        "name": "John Doe"
    }
},
{
    "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
    "seriesStart": 1100,
    "seriesEnd": 1200,
    "availableQuantity": 100,
    "books": {
        "BookId": ObjectId("5f4d0c1234f6b8d7e98a1234")
    },
    "employeeDetails": {
        "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
        "name": "John Doe"
    }
}

Let me know if that helps?

Thanls
Pavel

2 Likes
3

Thank you very much for your reply!

Since it is very different depending on each use case, let me elaborate a bit more. (I’m really having a hard time getting outside the mindset of a sql relational db schema).

I think I’m confusing you a bit by using the word “book”, I just really don’t know what they are called lol. It’s like a tiny ticket book used as a, kind of, receipt when someone takes the public transportation in my country. When someone gets on, they pay X depending the route distance’s fee, $0.25, $0.35, $0.45 … $1.50. Depending on that, they hand a single ticket with that cost from the ticketbook for that price/route. The person on shift can have several ticketbooks with the same price per ticket or have a mix of ticketbooks with different prices.

I don’t think they need exact control over what number of ticket has been given (it leads me to believe the design should be like the second example you gave me), they just need the starting correlative and end correlative, so we will know that if the ticketbook starts with 1100 and comes back with 1125, it means the ticketbook has 75 tickets left. Ticket amount in each ticketbook is 100 fixed, never varies.

I would assume that using your second schema example, for the second service I will create, when I enter the seriesStart: 1125 I will have to look for a service collection where 1125 is between (inclusive) seriesStart and seriesEnd, to automatically get the seriesEnd and the available quantity of 75. Would that be the proper approach?

Thank you!!

4

Perhaps you can generate some sample document based on the second approach and show some CRUD that you need so we can verify it fits.

Thanks

5

Alrighty! I will just have to build part of the app that does that and then come back with the data, I was just planning upfront haha but I think it is better to have some data and then make corrections. Thank you!

6

Hi @Mauricio_Ramirez ,

One alteration that you can do to the assignment document:

{
    "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
currentBookTickets: [{
    "seriesStart": 1000,
    "seriesEnd": 1100,
    "availableQuantity": 10,
    "price" : 0.75
    "book": {
        "BookId": ObjectId("5f4d0c1234f6b8d7e98a1234")
   }},
{
    "seriesStart": 1100,
    "seriesEnd": 1200,
    "availableQuantity": 90,
    "price" : 0.25,
    "book": {
        "BookId": ObjectId("5f4d0c1234f6b8d7e98a1235")
   }}],
    "employeeDetails": {
        "_id": ObjectId("5f4d0c1234f6b8d7e98a1234"),
        "name": "John Doe"
    }
}

This way a specific person can have different series in an array of assigned bookings …

I recommend indexing predict like seriesStart and seriesEnd if you query by them as well as the user assigned (maybe even combined)

I recommend the following reading and courses for u

Ty

3 Likes
7

oohh okay! Pavel! I think that is the design I will go with! makes a lot of sense!

And thank you a lot for the linked documentation, I will definitely read it!

8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.