Hi everyone.

As the title says. I just recently started learning python UI and postgresql. This is kinda my personal learning project I'm trying to figure out and would appreciate any suggestions.

The idea is I'm trying to record an "item"
an item can only be under 1 shot
a shot can only be under 1 episode
an episode can only be under 1 project.

So the structure is similar to windows directory.

Project --> Episode --> Shot --> Item

--> are all one-to-many I guess?

The way I want the UI to work is like this

Show list of projects.
Click a project, show lists of episodes next to it.
Click an episode, show lists of shots next to it.

The user can choose to list "items" after selecting only the project. Selecting episodes and shots are optional which will scope down the list even further.

Now to the database. I think there's 2 main idea I can use. Which would be better for this? What are the advantages/disadvantages of the 2?

yes

the first of the two diagrams is the correct one

Thanks r937

About naming, is it better to match the names I'm creating foreign keys for? Right now I have

project.id = episode.project_id

should it be like this instead? Or doesn't matter?

project.project_id = episode.project_id

that's the way i do it

why? because it's obvious which one is the primary key and which one is the foreign key

if they're both named the same, you can't tell