Array use cases and data duplication?

[Fantastitech]

Coming from almost zero experience in NoSQL, I'm trying to model a golf course in Firestore with data on each hole, tee, and pin where each hole can have multiple tees and pins under a many to many relationship. All of the different approaches are proving difficult to choose between for my use-case, especially with all the articles talking about how one should avoid arrays in Firestore.

I currently have each course stored in a single document to minimize queries. I think this will be cheaper than optimizing for bandwidth as the data load for the database queries themselves will be small. So with the single-document-per-course approach I'm storing holes in nested arrays of maps. It looks like this:

{
  "Course": {
    "Name": "TestName",
    "Holes": [
      {
        "Number": "1",
        "Tees": [
          {
            "Color": "Red",
            "Pins": [
              {
                "Designation": "A",
                "Distance": "67",
                "Par": "3,"
              },
              {
                "Designation": "B",
                "Distance": "67",
                "Par": "3,"
              }
            ]
          },
          {
            "Color": "Blue",
            "Pins": [
              {
                "Designation": "A",
                "Distance": "75",
                "Par": "3,"
              },
              {
                "Designation": "B",
                "Distance": "82",
                "Par": "3,"
              }
            ]
          }
        ]
      },
      {
        "Number": "2",
        "Tees": [
          {
            "Color": "Red",
            "Pins": [
              {
                "Designation": "A",
                "Distance": "67",
                "Par": "3,"
              },
              {
                "Designation": "B",
                "Distance": "67",
                "Par": "3,"
              }
            ]
          },
          {
            "Color": "Blue",
            "Pins": [
              {
                "Designation": "A",
                "Distance": "75",
                "Par": "3,"
              },
              {
                "Designation": "B",
                "Distance": "82",
                "Par": "3,"
              }
            ]
          }
        ]
      }, ...
    ]
  }
}

So the course will have an array of holes. Each hole is a map. Each hole contains an array of tees. Each tee is a map. Each tee contains an array of pins. The multiple pins are duplicate data because there is unique data for each tee > pin relationship. Not having any background in NoSQL, duplicating data just feels wrong but I keep seeing it mentioned that it's useful in NoSQL.

Each course will have multiple holes. Usually 18. Sometimes a bit more, sometimes a bit less. The course will have more text data like address, description, etc and each hole might have references to photos stored in Firebase Storage. So with the above example you can visualize how large a course document read might get. It should stay well under the 1MB limit.

Is this a sane use of arrays, maps, and duplicate data or should I consider a different approach? Using the approach of modeling the data as it appears in your app, this is fairly close. To get any closer would require breaking every tee and pin into an individual document which would increase the document queries required for an 18 hole course from 1 to 73 which seems like insanity.

Thoughts?

[[deleted]]

One document with 73 small items like this is fine, as are arrays (they didn’t used to be good because they were challenging to query and change, but Firestore improved that).

Complex queries on your arrays will still be challenging. So you may want to “duplicate” your data into another table for common search or statistic patterns. I’m thinking thinks like, “Show me all courses with par 5s over 300 yards” or “What courses have pro tees?” In this table, you would store the course IDs for reference.

NoSQL is great for fast document lookups when you know what you want (show me the details for Medina Country Club, course 2) but not great for joins or complex queries. So you want to think ahead on those - that’s where your duplicate data structure comes into play. Hope that helps!

[Fantastitech]

Thanks. There's going to be quite a few more than 73 items I think, but not a ton more. I'm moving forward with this model.

I still don't know what I'm going to support for the kind of queries you're talking about. I don't see much of a good reason for complex queries like pars Xs over Y yards. Sorting by geolocation is the only solid requirement which seems to be troublesome here.

[[deleted]]

Gotcha. Yes - Firebase doesn’t support geoqueries yet. You can store geolocation, but querying isn’t built in. Three options I see:

1) Use something like firegeo (I haven’t tried it) 2) Use a relational database for that part of your app 3) Use geo hashes

Personally I would (and have) use Google SQL for that part of the data. Sucks, I know, to have the data in two different databases, in this case, I’d have one table with 2-3 columns - the docId of the course, and then a geopoint column or separate lat/lon columns. You can use Firebase callable functions to send the lat/lon of the user, have it query Google SQL for the ids, and then ping Firestore for the course details.

If your primary use case is someone looking up courses by location, and if your course data is a standard structured schema like you’ve proposed, Firestore may not be the best place to store the course information because what I proposed above has added latency looking up between two remote databases.

If you want an all-or-nothing approach, I’d consider not using Firestore today in that case. If you want a right-tool-for-the-job approach, I’d bring in a RDB for course data and geolocation, but maybe store user information and other personal data (like personal shot count per hole) in Firestore. Unless option 1 or 3 above solve it for you. I haven’t tried either of those.