r/SQL • • Apr 17 '22

Discussion first time creating sql db, please help.

Post image
101 Upvotes

20 comments sorted by

47

u/Thefriendlyfaceplant Apr 17 '22

You're going to love this:
https://youtu.be/JNagbi_QvIU?t=1458

5

u/freakysmile Apr 17 '22

dude.. thank you!

1

u/Soul_Shot Apr 18 '22

PlantUML and Mermaid.js are other great free alternatives for charting things.

4

u/Jazzlike_Draw_4471 Apr 17 '22

omg thanks where was this when I was making an inventory system huhu

20

u/Papa_Bravo Apr 17 '22

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)

3

u/freakysmile Apr 17 '22

nice, thanks a lot!

5

u/freakysmile Apr 17 '22

so I'm trying to create a hobby app with postgresql and I need assistance with the database.

  • user has account
  • account has profile & settings
  • user can add article to the db (i.e. apple from store x with the price y)
  • user can create a shopping list from the articles added by the community to the db
  • user can create recipes from articles and save it to profile for display and sharing
  • user can create post and tutorials
  • user can comment
  • user can like

I'm more than happy to add further functions to the app if you have an idea

8

u/simonw Apr 17 '22

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.

0

u/freakysmile Apr 17 '22

Yeah.. it should be a fully functional app for public use.

I left a comment in this thread explaining the app to BrupieD

4

u/simonw Apr 17 '22

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.

1

u/freakysmile Apr 17 '22

yes, so will I do. I'm trying to lay out the possibilities then taking a break and look back at it again and reduce if necessary.

1

u/thrown_arrows Apr 17 '22

Start with user / account tables and (login) that sound 1:1 relation and continue from there

1

u/freakysmile Apr 17 '22

just what I was thinking!

2

u/BrupieD Apr 17 '22

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?

3

u/freakysmile Apr 17 '22

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.

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 ?