Opinion on Database Schema
Can anyone give me his opinion on my schema?
I am trying to have for each user a food log everyday, in the food log user should be able to add multiple products into it
But i feel the schema is not correct, can anyone give me advice?
60 Replies
⌛ This post has been reserved for your question.
Hey @Ed! Please useTIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here./closeor theClose Postbutton above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.
The serving size seems to be duplicated between product and food log
Yeah, i feel i am missing something, i am trying to basically have a list of products for every user in each day (food_log), and each product should have a portion that the user ate
also for the password, make sure it's hashed
product surveying size is general info that isbased on it i will calculate his total calories that he ate
but why have it twice?
yeah for now i am not using it at all, i use oauth
1 time its tied to the product itself, second time will be how much the user ate
but actually i can remove it
but for preview i might keep it when he search for product it can be in details
but my actual question is that is the schema correct like this or should i seperate and add 1 more table, basically combining food_log id and product(s) ids?
why?
like food_log_products, so that each log can have multiple products
is it an n:m relation?
its 1:m
1 food_log per day
so actually maybe n:m if overall
Is it one product per food log?
no many
then it's probably n:m
and for n:m, you need an additional table for the relation
each user has list of foodlogs each foodlog is for a day, each day in the food log user can have 0 or many products in it
in spring entity what is the relationship @ManyToMany?
that requires having an additional table for the relation
okay, so i am going to create a new entity, that holds FoodLog and List<Product>
you don't need a new entity though..?
foodLog is oneToOne and product Onetomany
hmmm
i am really bad at entity in spring boot
just name a
ManyToMany between the food log and product?i am sure it can be done by mapping but i have no clue how
but then the surveyingSize will be an issue i think
I wrote https://gist.github.com/danthe1st/a7cab8cad5a3044b3562e4f83530c12c quite a while ago - it's supposed to be a simplification of what annotation to use for which relation
Gist
JPA data class checklist and example persistence.xml for EclipseLink
JPA data class checklist and example persistence.xml for EclipseLink - Data_class_checklist.md
yes but i am thinking i might actually need an extra entity
To hold the productId and FoodLogId and SurvyingSize and other details about time of meal
or am i brain dead
yes if you need extra attributes, create an additional entity
For that, create a 1:m between product and the entity
just for clarification, this entity will be OneToMany with foodLog and ManyToMany with product
and 1:m between food log and the entity
you know what let me check the documentation about entity mapping i need it i think
two 1:m relation
or hold on
inside the new class yes you are right
no actually
1 foodLog cna have multiple entries
Do you really need extra information in the relation between food log and product?
yeah time of product and product size
information that's neither part of the food log nor the product
product database is basically a UPC database of qr codes of millions of products
ah then yeah it's necessary
i cannot update or migrate it it must be seperate
yeah
so inside this new class it will be i think many to many for both or not?
or many to many for food log and many to one product
one to many for product
FUCK
essentially you create a new entity that has a
Product and a ProductLog field, both @ManyToOnei might just create the database and generate the entities based on it its easier
if it's easier for you, yes
i will try this if it works fully i will continue
I will remove product from foodLog class and just do that
i think its finished:
so if you have one food log with two products, these can have two different meal types?
yes
in frontend i have 3 types i extract and based on type i fill the views
and the same also holds for serving size and measurement type?
yes they all change based on user input and how much he eats
user can add 1 tbsp of oil or 10g of oil thats for measure type based on him
and serving is different each time he adds an item
If that's the case, is there much of a point in the food log table? I mean
food_log_products could just contain a reference to user and the date (yes that's the situation from before)
essentially you could just do that but don't group it by date
if you say one food log is just one product entryno food log for full day
based on date
then i map the data on a calender
each day shows what user ate on it
which is a list of products
User has a calender, each day on calender has a log, each log has a list of meals, each meal shows how much he ate
ok if you want it like that
if you have better idea i can change it
Note that you can still group by date in the query with that
but i felt like the object i am extracting is confusing little bit
honestly idk i think there are so many ways to do this but i dont know which one is optimal
though
GROUP BY can maybe be a bit annoying with JPAyeah +
if you want to avoid it, you can use the extra table
is there any downsides from my schema
or you just request days individually if this is doable for you (beware of N+1 SELECTs though)
having an extra table means you need to go through that table every time you access it
i have to think this through, but i have a feeling if i dont seperate it will be heavy call on database when user has a heavy log
and requesting daily it will be so many api calls, i want 1 call to retrieve full dataset and save it in async memory for usre
possibly
maybe an index could help, maybe not
i am not sure, i will have to do integration testing for this schema if its good then i will keep it if perf is bad i will change something
Anyway i gotta go now, its 1 am here. Take care and thanks for the help!
If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.
Post Closed
This post has been closed by <@351059014341099521>.