next »

[Please Support Us!]

Please Support Us!

Please help keep (=WDG=) alive

• Server rules
• Forum rules
• Join us
  
• Clan members
• About the clan
• Server stats
• WDG YouTube Channel
• WDG Downloads
• IRC Webchat

Steam groups:

• WDG community
• WDG clan group
• WDG Battlelog Platoon

Our allies:

• [NuB] TF2 clan
• Velocity Gamers Hub
• Destination Gamer

GoG Links:

• Wix Discord
• Wix IRC channel

TF2 Links:

• Official TF2 Blog
• TF2 Steam Forum
• The Fort 2

October 2025

Mo	Tu	We	Th	Fr	Sa	Su
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31
	Birthdays
Chaos (40)
	Upcoming Events
Upcoming Birthdays: Chaos (40)

[Teatime]

Disclaimer: The following is technically not "technical" , actually not even "applicational", since this concerns the pre-technical phase of db-design, namely to project reality to the structures offered by a relational database. But I guess this is close enough.

Okay, one of my hobbies is creating databases for my various fields of interests (yeah, nerdish).
Currently I'm quite dissatisfied with the way I've designed the db which manages my movie collection, and I'm struggling to find a useful approach to solve the problem. So I thought I might spell my problem out here, mostly to simply "say" it out loud, but also on the off-chance that somebody might have some useful ideas.

My Movie DB collects all movies (Features, Series, Documentaries, etc) I have in any kind of physical form, be it bought media or content recorded and stored on tape or DVD-R. So some bulk of the DB consists of the movies and all the related data (cast/crew, technical data, etc). That part could probably need some revising either, but for the time being it works alright. The part I'm struggling with is the other huge bulk which represents the media management.

Here I'm trying to store each piece of media which holds a movie, series, documentary, etc. And sadly this is quite a complex matter. Commercial applications evade the inherent problems since they a) simplify and b) only deal with the media side.

So, what exactly are the problems?
A specific piece of media might come in many different kinds, challenging a projection to db-structures.
There is your standard DVD holding one movie (ignoring special features at this point). But I also have DVDs with two versions of the same movie. I have DVDs with two different movies. I have DVD-Boxes with a number of different movies. I might have a single episode of a series, or a box with a whole season, or a box holding several seasons of a series. Right now I have a box of "Battlestar Galactica" sitting on my shelf which contains the mini-series, all 4 seasons of the series and a TV movie. I have recordings where I might have episodes of different series on the same medium.

In the current version of my Movie DB I've tried to accomplish that with two tables, one representing the "outer" media (the DVD or Box), the other representing the inner segments (e.g. the episodes on a box of a series, or one movie in a DVD featuring two movies). There was some minor cheating involved to make this work.
But I'm increasingly dissatisfied with that approach, for one because I feel it's a sound and proper design (which bugs the perfectionist in me), but also because I continue to stumble into examples this design can not properly reproduce (like aforementioned "Galactica"-Box).

And all this with keeping track of technical data for the different media, like Codec, Format, Audio, Resolution etc ...

(Now here comes the point where it gets really nerdish )

Right now I feel the proper approach to break the two tables and distribute the data further.

One way to deal with the complex structure of media would be to insert an additional layer. Bottom level would be the part of a medium representing a movie or episode. Middle level would combine several bottom level elements (1:n) (like all episodes of one season) and the top level would (potentially) combine several middle level elements (1:n, thought mostly 1:1) (like a box set with several seasons of a series).

The problem with that is that this does not solve the problem, it simply adds a layer which evades the problem for the time being. This structure is just as inflexible as the current one. One day in the future I might encounter some kind of boxset which might no longer be covered by this structure (right of the top of my head: If FOX would offer a box containing all seasons of Buffy and Angel would already break this design).

On top of that I considering to pull out the technical details from these tables and store them in a different place (since some of it is repetitive), connecting those to the respective medium. That again would be something which would require some awkward design.

So here I'm wondering if this might be something which simply can't be projected satisfyingly onto a database or if there might be a completely different approach I just haven't thought of yet.

I know that this is quite specific in the required mindset needed to approach such a problem, and I have no idea if anybody here is working (or has worked) with relational databases and their development. Of course I will welcome any kind of input/suggestion/clarifying question. Just one note for those with experience in Excel (and the like): Don't draw from these experiences. A datasheet and a db-table are very different beasts and a database follows very different rules in structural design.

[Lister]

What is this, I don't even...

Hmm I'm not the right person to ask but good luck none-the-less!

My dad happens to be a database wizard so if I feel the need to introduce him to my "WDG side" I'll point him to this thread ^^

[Nineblade]

You nearly had me there T....I almost switched to database design mode then thought fuck it and went back to mixing concrete for my fence posts 

[Junta]

You need to give an indication of your existing tables, columns and relations.

If your db doesn't support the data then a new structure is needed. Avoid the desire to model every relationship in a separate table. If titles are linked, you have the possibility of storing them in the same table and linking back to another record in the same table (presumes a decent primary key). This can make querying a pain though, but does support relationships for records of the same type (i.e. films).

Stick to the rules of normalisation but don't be a slave to 3rd normal form.

If in doubt, use an "entity relationship" approach, where you document how things link together BEFORE creating database tables.

For example,

A publisher has one or more authors,
An author has one or more books
A book can have one or more edition
A book has one or more chapters

Where you have a "many to many" relation ship, you need an intermediate entity between the two. For example,
An albumn can have many tracks and a track can be on many albumns - you'd need an intermediate entity of "album track" that documents the relationship between albumns and tracks (assuming a multiplicity of tracks and albumns).

[scroggage]

Stick to the rules of normalisation but don't be a slave to 3rd normal form.

Second normal form FTW.

I vaguely remember learning about de-normalising a couple of years back, now I have it all to learn again this year

[Junta]

You tend to de-normalise for performance. Some of the databases I work on have over 11 million rows

[Teatime]

You need to give an indication of your existing tables, columns and relations.

What was the resize-command again?

The tables in question are tblMedia and tblMedium.
The section covering the actual movies will probably need an overhaul as well, but that's more a tweaking than a redesign.

If your db doesn't support the data then a new structure is needed. Avoid the desire to model every relationship in a separate table. If titles are linked, you have the possibility of storing them in the same table and linking back to another record in the same table (presumes a decent primary key). This can make querying a pain though, but does support relationships for records of the same type (i.e. films).

Yes, I am toying with such an idea (actually to some degree that is what I am doing so far). But this would require thorough examination to see what implications it might have on further analytical stages. (Yes, queries would quickly turn into a pain-in-the-ass since basically all details would be derived from queries-within-queries)

But it's nice to see I have an expert here

[DeadlyAvenger]

Yeah - John is the best person that I can think of to ask about this sort of stuff.

I only read through it briefly - but can you not have some kind of parent/child set-up going with a database table referencing itself?

I'm not quite sure the best way to explain it - but have it so that you have a tree of data items that have sub-items stored in the same table. The only example (and unrelated at that) that I can think of is how you might have an online shop with categories of different types of products. You would have a categories table in the database detailing all of the categories with 2 fields that have a 'parent of' and 'child of'.

So let's say your top categories were 'Music', 'Video', 'Games' - each of those would have a row in the categories table. Let's then say you had 'Action Games' which would have a parent id of the games row.

You would then use some kind of (recursive) algorithm that would read through all of the categories using the child / parent fields until it reached the bottom of the tree.

I'm not sure if that makes sense, or even if it could be applied to your situation - but it's fairly flexible.

[Zarf]

rusty on database design and probably missing something but doesn't look too difficult.

I'm not using your entity names here, just working from a clean perspective.




Personally, downloading all my stuff has worked wonders here, XBMC (xbox media center) has scraped all thousand or so of my movies on my NAS with their IMDB info and poster artwork, and I use a nice program called "TV Rename" to keep all my TV episodes organised, all I need to do is to scan my download folder every so often, it pattern matches the names and episode numbers and renames/moves the file into the correct folder along with cleaning up the file name and adding the episode title. It also downloads fanart and updates an xml file in the season folder that my XBMC reads containing all the info pulled down from theTVDB.org.

[Nineblade]

You tend to de-normalise for performance. Some of the databases I work on have over 11 million rows

LOL I always thought normalisation was for exams only and could be forgotten about once said exam had been passed.  

[Teatime]

@Deadly
Yes, you can do that. That's pretty much was John mentioned as possible approach.
Problem is that for queries (filtering data by criteria) you have to connect two queries (one to select the parents and then - based on that - the children) which is possible, but probably not too good for performance (but tbh I haven't worked much which such nested queries before, so I might be wrong).

@Zarf
Nah, this is just a more elaborate version of what I have discussed initially, and the "n:m" relationship doesn't really apply to my structure.
It's far too rare to have the exact same media or content as part of different containers (e.g exactly the same DVD as part of two box-sets) to justify the additional linking tables.

[DeadlyAvenger]

@Deadly
Yes, you can do that. That's pretty much was John mentioned as possible approach.
Problem is that for queries (filtering data by criteria) you have to connect two queries (one to select the parents and then - based on that - the children) which is possible, but probably not too good for performance (but tbh I haven't worked much which such nested queries before, so I might be wrong).

@Zarf
Nah, this is just a more elaborate version of what I have discussed initially, and the "n:m" relationship doesn't really apply to my structure.
It's far too rare to have the exact same media or content as part of different containers (e.g exactly the same DVD as part of two box-sets) to justify the additional linking tables.

Is performance a big issue? Surely a 1 user database, with fairly limited numbers of records performance isn't a big issue.

Also...sometimes it can be easier and better performance to do less in queries and more programmatically (in whatever programming language you're doing this in).

[Teatime]

Is performance a big issue? Surely a 1 user database, with fairly limited numbers of records performance isn't a big issue.

No, it is not.
But before I resort to some half-arsed compromise I'm trying to see if there's a more structural sound way to do it.

I'm brooding about something like that not only to make things work, but also to learn more about the technique and elements behind it, and taking the easy way out is really just a last resort.

[CruelCow]

IMHO you're approaching this from the wrong side. Don't try to save every single possible information (are you saving which actor appears in which episode scene?), ask yourself what you want to query for. Do you really query for all boxes that contain movies and seasons? Or do you rather query for all-galactica-stuff-I-have?

[claws]

It's a good question, because you can very easily go into relational table hell here. /personally/ I wouldn't overdesign it and leave yourself a lot of flexibility in having a (say) 'features' table with all the actual playing thing info, and then tag that in one or two more tables. So one dvd with two movies on (reg and directors cut), i'd have a table with metainformation and tie that feature with 'directors cut'. The virtue of this kind of thing is that it makes your hypothetical boxset quite easy: you have a bunch of features on several dvds, and those dvds are tied together with 'boxset' info.

(Sorry, this post a bit rambly, I made a better one and my browser crashed)