0

I was having some problem when trying to design for my firebase database structure. Basically, 1 account can have many receipts, 1 receipts can have many items. Here is the JSON:

receipts {
    accountID1 : {
        receiptID1 : {
            date : "07/07/2017"
            store : {
                storeName : "store1"
                storeAddr : "addr1"
            }
            currency : {
                currencyName : "currency1"
                currentcySymbol : "$"
            }
            totalAmount : "50.00"
            items : {
                itemID1 : true,
                itemID2 : true,
            }
        }
        receiptID2 : {
            date : "08/07/2017"
                store : {
                    storeName : "store1"
                    storeAddr : "addr1"
                }
                currency : {
                    currencyName : "currency1"
                    currentcySymbol : "$"
                }
                totalAmount : "20.00"
                items : {
                    itemID3 : true,
                    itemID4 : true,
                }
        }
    }
},
items {
        itemID1 : {
            type : "food"
            name : "snack"
            unitprice : "10.00"
            quantity : "2"
        }
        itemID2 : { 
            type : "entertainment"
            name : "gaming equipment"
            unitprice : "150.00"
            quantity : "1"
        }
        itemID3 : { 
            type : "food"
            name : "fruit juice"
            unitprice : "4.00"
            quantity : "1"
        } 
        itemID4 : {
            type : "entertainment"
            name : "gaming equipment"
            unitprice : "150.00"
            quantity : "1"
        }
},
itemIDsByType {
    food : {
        itemID1 : true,
        itemID3 : true,
    }
    entertainment: {
        itemID2 : true,
        itemID4 : true,
    }
}

I realized there is a duplication problem under the items child. For instance, account A purchase item A in receipt 1. Then, account A purchase the same item again in receipt 2. Under the receipts child, yes that will not cause any interference.

However, by looking at items child, specifically for itemID2 and itemID4, they are same item but belonged to different receipt. These two records are duplicated, and let's say for large set of data, I think this design might cause a problem.

Any ideas on how to restructure the database design in order to remove the duplication problem mentioned above?

I have actually come out with another design but it is less-flatten:

receipts {
    accountID1 : {
        receiptID1 : {
            date : "07/07/2017"
            merchantName : "NTUC"
            branch : {
                branchName : "Marsiling"
                branchAddress : "Blk 167, Marsiling"
            }
            currency : {
                currencyName : "currency1"
                currencySymbol : "$"
            }
            totalAmount : "50.00"
        }

        receiptID2 : {
            date : "08/07/2017"
            merchantName : "NTUC"
            branch : {
                branchName : "Marsiling"
                branchAddress : "Blk 167, Marsiling"
            }
            currency : {
                currencyName : "currency1"
                currencySymbol : "$"
            }
            totalAmount : "20.00"
        }
    }
},

itemLists {
    receiptID1 : {
        items : {
            itemID1 : {
                type : "food"
                name : "snack"
                unitprice : "10.00"
                quantity : "2"
            }

            itemID2 : { 
                type : "entertainment"
                name : "gaming equipment"
                unitprice : "150.00"
                quantity : "1"
            }

            itemID3 : { 
                type : "food"
                name : "fruit juice"
                unitprice : "4.00"
                quantity : "1"
            } 
        }
    }

    receiptID2 : { 
        items : {
            itemID4 : {
                type : "entertainment"
                name : "gaming equipment"
                unitprice : "150.00"
                quantity : "1"
            }
        }
    }
},
itemIDsByType {
        food : {
            itemID1 : true,
            itemID3 : true,
        }
        entertainment: {
            itemID2 : true,
            itemID4 : true,
        }
},
merchants {
    merchantID1 : {
        merchantName : "NTUC"
        branches : {
            branchID1 : {
                branchName : "Marsiling"
                branchAddress : "Blk 167, Marsiling"
            }
            branchID2 : {
                branchName : "Woodlands"
                branchAddress : "Blk 161, Woodlands"
            }
        }
    }
}

As for this design, each of the items are grouped under each receiptID so that could eliminate the duplication problem mentioned above. But I find it less-flatten, I am trying to juggle between flatten design with duplicate data or less-flatten design with no duplicated data. Which one would be better for a large set of data?

Community
  • 1
  • 1
QWERTY
  • 2,303
  • 9
  • 44
  • 85
  • Without seeing the Firebase structures it's really hard to understand the question. Along those lines, what is the function of the items node? Is that a master list of all items or node that stores the items that go with each receipt? – Jay Jul 16 '17 at 12:28
  • @Jay Yeah the function for the items is the node that stores the items that go with each receipt! – QWERTY Jul 16 '17 at 13:15

1 Answers1

1

Let's start with a master items list. This list is ALL of the items available for sale.

item_0
  name: "burger"
item_1
  name: "taco"
item_2
  name: "hot dog"
item_3
  name: "fries"
item_4
  name: "refried beans"

Then the receipts node which stores info about the receipt, date, time, customer name etc. Note there are no references to the items as they are not directly needed, but could be added for convenience.

receipt_0
   customer: "Frank"
   timestamp: 170716093623
receipt_1
   customer: "Bill"
   timestamp: 170716094515

and finally the details of the items on each receipt.

receipt_items:
   -Y89jasjdiasd:
      item_id: item_0
      price: 5.00
      qty: 1
      receipt: receipt_0
   -YHJis9asdasd:
      item_id: item_3
      price: 1.50
      qty: 1
      receipt: receipt_0
   -Yn9kasdpaosd:
      item_id: item_1
      price: 2.00
      qty: 3
      receipt: receipt_1
   -Yllois9040ka:
      item_id: item_4
      price: 1.50
      qty: 1
      receipt: receipt_1

As you can see, Frank got a burger and fries on receipt_0 and Bill got 3 tacos (!) and a side of refried beans on receipt_1

With this structure, you can get the details of each receipt, customer, date etc. Or query the receipt_items node for a receipt_id and get the details of the items on it - item, price, qty etc.

You can also query the receipt_items node for a specific item; then sum up the quantities for say.. the most popular, or the average selling price.

This eliminates duplicate items AND data and provides a queryable, denormalized structure.

As mentioned above, you could add a child node to each receipt to store the receipt_items but since the receipt_items is queryable it may not be needed. It could be used to order the items on the receipt..

Note: the child node keys in receipt_items are created with childByAutoId.

Jay
  • 34,438
  • 18
  • 52
  • 81
  • Thanks so much! But let's say I wanted to group by type also as you can see from the design above, I actually have another lookup table itemIDsByType, so the 'type' attribute should be under receipt_items, am I right? Basically the master items list just storing the name. So when a new receipt comes in, I create a new receipt, get its ID, loop thru the item names check if exist, if not then create new item, get its ID, then insert both ID into receipt_items, am I right? – QWERTY Jul 16 '17 at 14:00
  • Hey sorry but also, let's say 1 merchant has multiple branches, 1 receipt belong to 1 branch. Do you think the merchant part in my second design is good? – QWERTY Jul 16 '17 at 14:26
  • I would think the *type* would be stored with the item, so in my answer, burger, hot dog and taco would be type *entree* and the fries and refried beans would be a type *side*. If you want to query the receipt_items for all sides, you would also want to store the type in that node as well. I would probably store it in both places for consistency - again the master items list is the reference for the info for each item. As far as the receipts go, I don't know why you would loop through the item names. In general, you would already have the items in your master items list. – Jay Jul 16 '17 at 14:43
  • If a merchant is going to have multiple branches.. you may want to consider a compound value. So in the receipt_items node, instead of a child node *receipt* you may want to consider using *branch_receipt* which concatenates the branch and receipt number together; branch_0_receipt_1, branch0_receipt_2 etc. That will enable you to user startingAt and endingAt to query for branch_0 receipts from x to y. – Jay Jul 16 '17 at 14:47
  • But I will be using the unique push ID for both the receiptID and branchID. Is it still viable for the compound value in this case? Also, are there mistakes for the merchants and receipts part in the second design? – QWERTY Jul 16 '17 at 14:53
  • The branch design looks ok to me. However, I don't know how you are implementing the branchId's - are they pre-defined or are they actually created via a push at some point? I don't know your specific use case. The bottom line is that Firebase structures are models according to what data you want to get; what kind of queries do you need? What data do you need regarding the branch? This may be a separate question because that will take some explanation. – Jay Jul 16 '17 at 15:01
  • those IDs will be generated by the unique push ID. I changed them for explaining purposes. I see I see. Thanks so much Jay, you are my savior! – QWERTY Jul 16 '17 at 15:08
  • Hey Jay, I have came across another design problem for firebase structure. Do you mind to take a look at this: https://stackoverflow.com/questions/47879031/firebase-database-structure-for-chat-application? – QWERTY Dec 19 '17 at 02:11