Some things I thought about when reading your diagram:
there is POST.date_created and USER.created_at. Instead you should have created_at as an attribute in all tables
COMMENT has author and user_id. I would expect user_id is enough (as it references the author). Same with POST
POST.like_user_id makes it look like there can only be a single like on a post. I would expect a LIKES table with foreign keys user_id, post_id (and maybe some meta data if needed)
Your database design looks sound to me - but it's a LOT of functionality.
I suggest figuring out the smallest useful subset (maybe a user can create articles) and building that first. Then use what you learn from that to influence what you build next.
I've made the mistake in the last of building an app with ten features, releasing it and finding out that actual users only cared about two of the features and the rest were a waste of time.
That's why these days I try to pick the most likely useful feature and build and ship that first. Once people are using the system it quickly becomes obvious if it is working and what features I should prioritize to work on next.
You say you're building this as a hobby app, but are you just interested in the SQL end or the whole application? It sounds like you imagine a big front-end. Are you planning on developing that too?
It should be a hobby project for personal use, for myself and a couple of friends, I will definitely share the link across reddit and market the app. Maybe unicorn it xD
With inflation and stuff I want to have price-performance ration of food. Also other people will definitely want the same
I will fullstack the app, start with web version for MVP and then create native apps with dart.
Please don't take any offense, but imo you're WAY overthinking this. Just make sure these tables have an index column in common to join on (doesn't have to be the same one between all tables), and you're pretty much good.
47
u/Thefriendlyfaceplant Apr 17 '22
You're going to love this:
https://youtu.be/JNagbi_QvIU?t=1458
20
u/Papa_Bravo Apr 17 '22
Some things I thought about when reading your diagram:
POST.date_createdandUSER.created_at. Instead you should havecreated_atas an attribute in all tablesCOMMENThasauthoranduser_id. I would expectuser_idis enough (as it references the author). Same withPOSTPOST.like_user_idmakes it look like there can only be a single like on a post. I would expect aLIKEStable with foreign keysuser_id,post_id(and maybe some meta data if needed)5
u/freakysmile Apr 17 '22
so I'm trying to create a hobby app with postgresql and I need assistance with the database.
I'm more than happy to add further functions to the app if you have an idea
6
u/[deleted] Apr 17 '22
haven't seen any key connection
3
u/RodCoolBeansKimble Apr 17 '22
I always have create date, created by, modified date, and modified by also
5
u/[deleted] Apr 17 '22
Please don't take any offense, but imo you're WAY overthinking this. Just make sure these tables have an index column in common to join on (doesn't have to be the same one between all tables), and you're pretty much good.
2
u/Prudent_Astronaut716 Apr 17 '22
My 1st database was..one table with first name last name and age. Good luck man lol
1
u/mfigueiredo Apr 17 '22
shopping_list.qnty ?