I have a (actually quite simple) data structure that has a tree-like adjacency. I am trying to find a good way to represent data for a film-industry based web-app which needs to store data about film projects. The data consists of: project -> scene -> shot -> version - each adjacent to the previous in a "one-to-many" fashion.
Right now I am thinking about a simple adjacency list, but I am having trouble believing that it would be sufficiently efficient to quickly retrieve the name of the project, given just the version, as I'd have to cycle through the other tables to get it. The (simplified) layout would be like this: simple adjacency layout
I was thinking about - instead of referencing only the direct parent - referencing all higher level parents (like this), knowing that the hierarchy has a fixed depth. That way, I could use these shortcuts to get my information with only one query. But is this bad data modeling? Are there any other ways to do it?
It's not good data modelling from the perspective of normalisation. If you realise that you put the wrong scene in for a project, you then have to move it and everything down the hierarchy.
But... does efficiency matter to you? How much data are you talking about? How fast do you need a response? I'd say go with what you've got and if you need it faster, have something that regularly extracts the data to a cache.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments