Problem finding the right Database structure-CodePudding

I'm building a reservation app for a restaurant (in Unity). The app should be able to add a reservation and save various data (name, phone, number of guests/tables, timeSlot...) on a certain day. It will also need to keep track of daily, monthly and yearly statistics. The database must also be structured in such a way that it is possible to retrieve these statistics (efficiently) with range of DateTime or just the one chosen. The database I'm using is Firebase Firestore, a NoSQL cloud database.

I've tried to think about how to structure it but I haven't found anything that is efficient. I also tried to use subcollections of documents and/or subdocuments but it becomes too complicated to manage. Since Firestore doesn't accept a subcollection of a collection, if I had to reference something like this:

daily_guest_totals (Collection)
    YYY (Document)
        MM (Collection)
            DD (Document)

The script should look like the following. And so far that would be fine.

databaseReference.Collection("daily_guest_totals").Document("YYY").Collection("MM").Document("DD");

But if you were to reference something like this:

daily_guest_totals (Collection)
    YYY (Document)
        MM (Collection)

The script should look like the following. But what should be a document (MM) is actually a collection, so to add values I'd be forced to create a document with some kind of name (DucumentReference doesn't accept a type of CollectionReference).

databaseReference.Collection("daily_guest_totals").Document("YYY").Collection("MM").Document();

Below you will find the first structure I thought of.

Structure for the reservations:

reservations (Collection)
    reservation_id_1 (Document)
        reservation_date: 2020-01-01 (Field)
        time_slot: lunch
        number_of_guests: 4
        number_of_tables: 2
    reservation_id_2
        reservation_date: 2020-01-01
        time_slot: dinner
        number_of_guests: 8
        number_of_tables: 4
    reservation_id_3
        reservation_date: 2020-01-01
        time_slot: lunch
        number_of_guests: 2
        number_of_tables: 1

Structure to keep track of the statistics:

daily_guest_totals (Collection)
    YYYY.MM.DD (Document)
        total_guests (Field)
            lunch: 10 (Subfield using FieldPaths)
            dinner: 20
        total_tables
            lunch: 2
            dinner: 4
monthly_guest_totals
    YYYY.MM
        total_guests
            lunch: 300 
            dinner: 600
        total_tables
            lunch: 60
            dinner: 120
yearly_guest_totals
    YYYY
        total_guests
            lunch: 3600
            dinner: 7200
        total_tables
            lunch: 720
            dinner: 1440

CodePudding user response：

You need to know that there is no "right" or "the perfect" solution for structuring a Cloud Firestore database. The best solution is one that fits your needs and makes your job easier. Bear also in mind that there is also no single "correct data structure" in the world of NoSQL databases. All data is modeled to allow the use-cases that your app requires. This means that what works for one app, may be insufficient for another app. An effective structure for a NoSQL type database is entirely dependent on how you intend to query it.

The way you are structuring your reservations collection seems reasonable. You can use the hierarchical structure in your Firestore database to efficiently track daily, monthly, and yearly statistics.

Please also take a look at this Stackoverflow Link which may help you.