GS current Release schema

[khyati0212]

From @atherdon on March 10, 2018 0:47

Main repository: https://github.com/GroceriStar/groceristar I move models here: https://github.com/ChickenKyiv/database-visuals/tree/master/groceristar/models

Review this files, dig into that JSON structure and create a visual for the current release.

Then I want to hear your ideas about how this schema can be improved.

7

Copied from original issue: ChickenKyiv/database-visuals#2

[khyati0212]

Hi @atherdon,

I have created visual for this schema : https://sqldbm.com/Project/MySQL/Share/v1Tcp4mXo3Gy1tmiICeBJg

Can you check it? If it is correct I will update all other by tomorrow morning. And if not please give feedback to improve.

[khyati0212]

From @atherdon on March 12, 2018 20:27

It's amazing. i have no words. really cool. and very informative.

[khyati0212]

From @atherdon on March 12, 2018 20:30

i know that current system is not ideal. Do you have an ideas, how we can make it better(example - I think favorite part is not). let's discuss this here

[khyati0212]

From @atherdon on March 14, 2018 2:27

@khyati0212 we still need to discuss what should be improved at my current DB architecture by your opinion? let's discuss it here, and i'll be able to close this issue.

[khyati0212]

I am working on that. I will message you my finding in 3 hours. Its dinner time for me so.

[khyati0212]

From @atherdon on March 14, 2018 2:50

Good! don't want to push - just have your time. just want to discuss future improvements and move to a next tasks. have a nice meal!

[khyati0212]

My question is how item and ingredient are different?

[khyati0212]

@atherdon I am trying to understand the schema more.

[khyati0212]

@atherdon my suggestion to improve the schema is if "ITEM" and "INGREDIENT" are kind of similar than we can normalize them. I am confused about "FAVOURITE".

[khyati0212]

From @atherdon on March 14, 2018 9:58

Ingredient - table that used at online GS version. Each time when user add ingredient to the list - we have a new row.

Item - a table that I plan to use in the next releases. Main goal: add measurements and flags/attributes to an ingredient. So each item can be milk (Ingredient) + 1 L (Qty and measurement) + 1 (ingredient stored at favorite list) + 1 (item is purchased at current shopping list) So item have more advanced functionality

Favorite and purchased is a quite similar thing. But first is related to user(you can list all favorite ingredients or maybe items at user profile), purchased always related to grocery list - and you can list all purchased items by grocery list ID

[khyati0212]

Still I think ITEM and INGREDIENT has kind of similar functionality. So you can replace ITEM with advance version INGREDIENT.

[khyati0212]

Purchase can have favorite item coloumn.

[khyati0212]

These are 2 improvement came to my mind. What are your thoughts.?

[khyati0212]

From @atherdon on March 14, 2018 16:14

give me more info about replacing ITEM with advanced ingredient. maybe sample. this is an interesting idea - i don't think about it. let's think about it. but if we replace ITEM - how you'll handle this case: I add "1 L milk" - purchased - price $3 i also planed to have price tracker

On 14-Mar-2018 at 04:11 PM, Khyati wrote:

Purchase can have favorite item coloumn.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChickenKyiv/database-visuals/issues/2#issuecomment-373079580, or mute the thread https://github.com/notifications/unsubscribe-auth/ABZrDtQXmJYWjjYfQPVxLSq_5bQdDYUCks5teUEtgaJpZM4SlD0o .

[khyati0212]

From @atherdon on March 14, 2018 16:17

not sure about mixing favorite with purchased... i mean if i buy "1 L milk" and "300 ml of milk" - both will be purchased. but do we need to have both this records in favorite or maybe save just milk?

On 14-Mar-2018 at 04:11 PM, Khyati wrote:

Purchase can have favorite item coloumn.

[khyati0212]

Yeah that is true. Than we cannot mix purchase with favorite. I didn't considered quantity previously.

[khyati0212]

From @atherdon on March 14, 2018 16:24

it was my fault - i don't share with you plans for later releases. not a problem

On 14-Mar-2018 at 04:23 PM, Khyati wrote:

Yeah that is true. Than we cannot mix purchase with favorite. I didn't considered quantity previously.

[khyati0212]

Yeah regarding price tracker I could not see price field anywhere.

Also when user buy same thing more than once. How it will be handled in Purchased?

[khyati0212]

From @atherdon on March 14, 2018 16:40

if this will be stored at different grocery lists - there not problem. if we add one ingredient(i.e. milk) twice - we'll calculate them automatically. so 2 rows became one 1.3L of milk(if milk is similar)

[khyati0212]

Sorry to ask too many questions. But my question was purchased is the list of items stored somewhere. So every grocery list might be stored differently. Right? So what if user buy same item with same quantity on different dates. So there will be same row stored twice. just with different date. Rest of columns will have same values.

Example: "1 L mik" on "03/12/2018" by user " Jane Doe" "1 L milk" on "03/13/2018" by user "Jane Doe"

[khyati0212]

• Grocery table can eliminate Description field as it is already there in ingredient table.

• Userfav table can have just GroceryId column and no ingredientId column. As Grocery table is already having ingredientId as foreign key.

• User table can eliminate ingredientId field as it has groceryId field as foreign key. And it can have itemId field added as foreign key.

Please let me know your thoughts regarding these suggestions @atherdon .

[khyati0212]

From @atherdon on March 14, 2018 23:26

questions are good - and i like that we're setup a working flow here. just awake and will answer to your messages soon

[khyati0212]

Good Morning and no rush.

[khyati0212]

From @atherdon on March 15, 2018 1:56

working on reply

[khyati0212]

From @atherdon on March 15, 2018 14:24

let's pause this topic and switch to some other task. I see that I need more time to reply and put more details in one place

[khyati0212]

Yeah I also thought more details are needed.

[khyati0212]

From @atherdon on March 15, 2018 15:47

i think in one-two days i'll complete my document.

[khyati0212]

Great

[khyati0212]

Let me know if I can be of any help.

[khyati0212]

From @atherdon on March 15, 2018 17:3

i think you should jump into search api task - right now it's about "Arthur must find all information at different old repositories and move it on this clean repo".

[khyati0212]

From @atherdon on March 15, 2018 17:4

we did more complex research with you - more than i expect on this task :)

[khyati0212]

Sure

[atherdon]

did you move all our messages, related to GS database schema, right? i'm starting an article, where i'll address first major step in db updates...

[khyati0212]

Which messages?

[atherdon]

i think i just get confused by amount of data that i have in different places... I think this task contain all our discussion related to GS schema

[atherdon]

https://medium.com/groceristar/database-schema-of-groceristar-current-release-20dc1b4be7b9