CommunityNews

CommunityNews

Hierarchical Structures in PostgreSQL (2020)

It’s a common pattern: a database developer at a startup is probably on the Product subteam of the Engineering team at their company. In a department store, shoes are a subcategory of clothing, while your favorite thermos is probably in the travel department.

In any Github organization, there are teams within teams within teams. In any large department store there are categories deeply nested. In any recipe book, there are many ways to classify food.

So how can we model them?

Jake (my boyfriend) and I have been exploring relational database concepts out of interest and pure geekery. This was a fun problem that I gave him and we got to work it out together. It was so fun we wanted to share! We won’t beat the bush around with PostgreSQL installation, security, setup, blah blah at this time, let’s just have some pure database fun for a few minutes!

Read in full here:

This thread was posted by one of our members via one of our news source trackers.

Most Liked

OvermindDL1

OvermindDL1

Is it just me or is their example output of SELECT * FROM teams WHERE path @ 'Product'; very wrong?

AstonJ

AstonJ

What’s the @ for? Don’t think I’ve ever used that in a query :confused:

OvermindDL1

OvermindDL1

It’s part of the ltree type in postgres, it allows you to make paths without needing recursive queries and other such annoyances. The path @ element matches when the element exists as any element anywhere within the path. :slight_smile:

Where Next?

Popular Backend topics Top

First poster: AstonJ
Is Rust Used Safely by Software Developers?. Rust, an emerging programming language with explosive growth, provides a robust type syste...
New
First poster: bot
Kawa is a general-purpose programming language that runs on the Java platform. It aims to combine: the benefits of dynamic scripting la...
New
First poster: AstonJ
Ruby vs Python comes down to the for loop. Contrasting how each language handles iteration helps understand how to work effectively in e...
New
First poster: dimitarvp
Rails is not written in Ruby. I’m born and raised in Kraków, a beautiful city in Poland, maybe you’ve heard about it, maybe you’ve even ...
New
First poster: bot
v4 Announcement · actix/actix-web Wiki. Actix Web is a powerful, pragmatic, and extremely fast web framework for Rust. - v4 Announcement...
New
First poster: bot
I am often fascinated by old tech. While I do not have the experience nor the expertise on the subject, in the last months, some very sp...
New
CommunityNews
C++ Cheat Sheets & Infographics. Graphics and cheat sheets, each capturing one aspect of C++: algorithms/containers/STL, language ba...
New
CommunityNews
Python 3.11 in the Web Browser - A Journey Christian Heimes PyConDE & PyDataBerlin 2022 conference . Compile CPython to Web Assembly...
New
First poster: bot
Introducing Trilogy: a new database adapter for Ruby on Rails | The GitHub Blog. We’ve open sourced Trilogy, the database adapter we use...
New
First poster: bot
crubit/design.md at main · google/crubit. Contribute to google/crubit development by creating an account on GitHub.
New

Other popular topics Top

AstonJ
A thread that every forum needs! Simply post a link to a track on YouTube (or SoundCloud or Vimeo amongst others!) on a separate line an...
New
AstonJ
SpaceVim seems to be gaining in features and popularity and I just wondered how it compares with SpaceMacs in 2020 - anyone have any thou...
New
AstonJ
You might be thinking we should just ask who’s not using VSCode :joy: however there are some new additions in the space that might give V...
New
AstonJ
There’s a whole world of custom keycaps out there that I didn’t know existed! Check out all of our Keycaps threads here: https://forum....
New
AstonJ
Do the test and post your score :nerd_face: :keyboard: If possible, please add info such as the keyboard you’re using, the layout (Qw...
New
AstonJ
In case anyone else is wondering why Ruby 3 doesn’t show when you do asdf list-all ruby :man_facepalming: do this first: asdf plugin-upd...
New
Margaret
Hello everyone! This thread is to tell you about what authors from The Pragmatic Bookshelf are writing on Medium.
1147 28379 760
New
AstonJ
We’ve talked about his book briefly here but it is quickly becoming obsolete - so he’s decided to create a series of 7 podcasts, the firs...
New
New
PragmaticBookshelf
A concise guide to MySQL 9 database administration, covering fundamental concepts, techniques, and best practices. Neil Smyth MySQL...
New