Last week I found very lame and stupid bug in Groceryx. I've made a huge mistake when I was designing the data model. When designing UX for app's first launch I wanted to give users some set of predefined items to show a sample shopping list and to make suggestions when adding a new item to the list. Here is how my simplified data model looks like:

CREATE TABLE list (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
);

CREATE TABLE item (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    note VARCHAR(250),
    fk_category_id VARCHAR(36)
);

CREATE TABLE item_category (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    index DOUBLE PRECISION NOT NULL
);

CREATE TABLE list_item (
    fk_list_id VARCHAR(36) NOT NULL,
    fk_item_id VARCHAR(36) NOT NULL,
    is_checked BOOLEAN NOT NULL
);

I use the similar structure on both the backend (PostgreSQL) and the app (CoreData).

On first launch item and item_category are filled from the bundled JSON:

{
    "Items": [
        {
            "Index": 100,
            "Items": [
                {
                    "Name": "Baby Lotion",
                    "UID": "5821BD45-E8EE-4AB1-B16F-4D4A6161CC43"
                },

                ...

                {
                    "Name": "Teething ring",
                    "UID": "DB43CC67-B8A4-449D-B897-622D34FD67F0"
                }
            ],
            "Name": "Baby",
            "UID": "7C234477-2A76-4AA2-8DD4-005C3BE08D22"
        },
        {
            "Index": 110,
            "Items": [
                {
                    "Name": "Aloo Pie",
                    "UID": "DA879236-6804-423B-B0DD-61F1EA3282A8"
                },

                ...

                {
                    "Name": "White Bread",
                    "UID": "A24329880-EFF5-4F49-A0F6-D268EC42610"
                }
            ],
            "Name": "Bakery & Bread",
            "UID": "B34342C4-D750-4078-91DC-7E234888F4DA"
        },

As you can see item's and category's UUIDs are pre-generated. It was my choice since I wanted that after a shopping list is shared between users and one of them adds predefined items (e.g. "Apple") this item would then be copied to other users app's databases. If UUIDs are different, this will lead to creating duplicate "Apple" item. I was so scared of this duplication that I've missed a bug: due to item's and category's UUIDs being the same for all the users, any user changing predefined item or a category change it for all the users. Yes, when a user changed item's name from "Apple" to "Green Apple" all other users who have "Apple" in their lists will find that it's changed to "Green Apple". This only happens with predefined items (imported from the bundled JSON on the first launch) and all other new items created by the users were ok (their UUIDs were generated at runtime). Nevertheless, it was a stupid mistake of mine.

A solution most likely will involve changing backend's data model and code along with iOS app's code and storage scheme (CoreData). Which in turn would require migration process on the backend and in the iOS app. And don't forget about supporting requests from the old version of the app on backend since all of the users will not migrate to the new version overnight. You get the picture.

My first solution was to move item properties into list_item, thus effectively switching from "one item in many lists" to "one item in one list" model (added list_item_category mean that set of categories is also on per-list basis now). Here are these changes to the schema for Solution #1:

CREATE TABLE list (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
);

CREATE TABLE item (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    note VARCHAR(250),
    fk_category_id VARCHAR(36)
);

CREATE TABLE item_category (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
-   index DOUBLE PRECISION NOT NULL
);

CREATE TABLE list_item (
    fk_list_id VARCHAR(36) NOT NULL,
    fk_item_id VARCHAR(36) NOT NULL,
    is_checked BOOLEAN NOT NULL
+   item_name VARCHAR(100) NOT NULL,
+   item_note VARCHAR(250),
+   fk_list_item_category_id VARCHAR(36
);

+ CREATE TABLE list_item_category (
+   id VARCHAR(36) PRIMARY KEY,
+   fk_category_id VARCHAR(36)
+   name VARCHAR(100) NOT NULL,
+   index DOUBLE PRECISION NOT NULL
+ );

I ended up with a redesigned data model (more than a half of the entities are changed) and perspective of a serious re-write of both backend's and app's code. And I did not even touch a migration yet. At this point, I stopped. Groceryx is my hobby project, and my mind is occupied with another app ideas since I launched it. I expect to run it in maintenance mode while spending my free time mostly on my next app idea. This solution is too expensive. Let's keep searching.

Solution #2. After discarding a solution for being too expensive, I went to the opposite extreme. What if I just ignore item's and category's UUIDs and generate new ones for every user on the first launch? That'd lead to a duplicated items and categories for the shared shopping lists. When a user adds "Apple" to a shared list, it'll be copied to another user as new item despite that he or she already have "Apple" with another UUID. Not good enough.

Solution #3 was a slightly changed Solution #1 and it did not become any cheaper to implement, so I discarded it as well. I've tried extremes, cheap that does not work and the one that was too expensive I focused on finding a solution which will be in the sweet spot between them.

Let's get back to the root of the problem. Items and categories are defined outside the shopping lists. How can they be bound to a list? Let's go straight and add list id as a foreign key to both item and item_category. Here is Solution #4:


CREATE TABLE list (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
);

CREATE TABLE item (
*   id VARCHAR(36) NOT NULL,
+   fk_list_id VARCHAR(36),
    name VARCHAR(100) NOT NULL,
    note VARCHAR(250),
    fk_category_id VARCHAR(36)
);

CREATE TABLE item_category (
*   id VARCHAR(36) NOT NULL,
+   fk_list_id VARCHAR(36),
    name VARCHAR(100) NOT NULL,
    index DOUBLE PRECISION NOT NULL
);

CREATE TABLE list_item (
    fk_list_id VARCHAR(36) NOT NULL,
    fk_item_id VARCHAR(36) NOT NULL,
    is_checked BOOLEAN NOT NULL
);

I've marked item.id and item_category.id with an asterisk to highlight their demotion from being primary keys. This solution works well for initial import of predefined items with defined UUIDs. Both item.fk_list_id and item_category.fk_list_id will be NULL in this case. When an item is added to a list, a new entity is created with filled fk_list_id. So the items with the same UUIDs would be able to have different properties for every list they been included in. Migration on the backend and in the app seems a lot easier compared with the previous solutions. Looks like we have a winner. I'm going with it and I'll see how it will work out. Thanks for reading this far.