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)

[Zarf]

@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.

I thought this was what you were asking for though?

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.

The tables design I did should allow you to have boxsets with multiple DVD's, and each DVD can have any mix of episodes and movie content linked to it (accounting for your Buffy and Angel scenario).

Tables wise it's not that complicated, your data entry form should be taking care of any 'heavy lifting' automatically.
It's a personal database with a single user and a few thousand rows - performance (even in access) should pretty much be a non-issue.
The flexibility, however infrequently required, is worth the performance trade-off. With Blu-Ray I expect you are going to find yourself with more and more duplicates.

[Nineblade]

I just have loads of boxes full of dvds and every now and then I rummage through them to see what's there. If I want to normalize a relationship, I'll go and be nice to my wife for a change

[Zarf]

Maybe then she'll compound your primary key :p

[Teatime]

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?

Of course I want to save every possible information. And with a well-constructed db this is fairly easy. This db I have is actually not a very complex one, and it already accomplishes a lot.
My problem is that I'm trying to collect data of objects which are structurally not clearly defined (since companies have the tendency to come up with with new ways of presenting products I might not have anticipated).

@Claws
I don't know your background, but so far it's my experience that a good relational design makes things easier in so many ways. It's sometimes hard to come up with the right design (like the one I'm struggling with here), but once you've got it it's implemented easily enough and helps you in a big way in presentation, analysis and maintenance.

With Blu-Ray I expect you are going to find yourself with more and more duplicates.

Ah, duplicates of the same movie, yes. But rarely of the same medium.
A DVD and a Blu-Ray of a movie would still be two different media, although linking to the same entry in the movie table.  Always remember that a medium will come with technical properties (release year, audio, special features etc) which in 99.9% will make it unique. So at this point the added flexibility is indeed wasted.
I have other DBs which did require that approach (Books and Music).

And yes, since it's just for myself and it's not that big a database performance is not a priority.
But this has become kind of a hobby and I'm trying to learn more and get better at. So naturally when I approach a problem like that my attitude is more general. I'm trying to design it in a way that it could indeed handle millions of entries (not that I'll ever have that many DVDs, but some of the support tables - in the case of this DB the Cast&Crew table for example - can grow huge).

In other words: This is 50% solving a problem and 50% trying to advance myself in a field of interest.

[Teatime]

I just have loads of boxes full of dvds and every now and then I rummage through them to see what's there. If I want to normalize a relationship, I'll go and be nice to my wife for a change

You just want to engage in an innerjoin query ...
Unfortunately she responds with error code 13.

[Nineblade]

I wanted to do a union  on the table but my  query returned no rows

[r007]

Heh. What you want is really a graph in the database. A table with something along the lines of

(medium_id, medium_parent_id)

that would -- in table-space -- solve the problem of having arbitrary collections of media, like the Buffy&Angel box, or any mor convoluted example you can think of.

BUT:

As you may or may not know, SQL cannot by itself express a transitive closure on a graph. This means, in order to find out "everything that is in the Buffy&Angel box", you will have to write code (VBA I assume in your case) that walks along the graph. God help you if you have a circular dependency (the dvd box that magically contains itself...).

€dit: geeky correction: SQL92 (which is what I learned at the time and think of as being supported everywhere) cannot. However, oracle seems to have extensions in place that can.

[Junta]

R007, later versions of SQL Server allow recursive queries - see "common table expressions". Sadly, I don't believe these are available in MS Access!

As for the comments from Cruelcow and Claws - I agree to a point. Databases should hold the information you need without duplication. I strongly agree with Claws' comment about over-engineering relations in linked tables. Having too few tables (and dumping everything in one table) is a sin, and having too many extraneous relationships is also a sin.

@Tea: If in doubt, go back to designing your model BEFORE you throw up tables in a database - it will help

From a brief look at your relationship diagram, it does look to me like you've over engineered some of the relationships, but that is easy for me to say because I don't fully understad the model yet

[claws]

I tend to agree with Cruel re the amount of information, but if that's what you want to do (and hey, it's a learning thing).

The problem is not everything maps cleanly into a 'good relational design'. Sometimes the 'cleanest' design ends up being slow and unwieldy in practice. For example, the media it actually comes on is almost an accident : there's dvds and bluray and maybe hddvd and boxsets of dvd and online rips and god knows what else. You could have a table for each of those, or you could go for a simpler structure which is possibly less 'elegant', but in practice less hassle and makes expansion easier.

As John says, info dumps in one table is a bad thing. However, I do urge keeping it as simple as possible. Think of a design that would /let/ you expand it if you had to, and then /don't implement it/. Sometimes people get the urge to allow for every possible case and you end up with a hulking monstrosity in order to cope with 1 or 2 fringe events.

@r007:That's abuse of relational databases though ... there aren't many cases where you have infinitely deep graphs, and 'in theory' the database is in the best position to optimise complex queries, rather than having k queries for a k-depth tree.

[Teatime]

Heh. What you want is really a graph in the database. A table with something along the lines of
(medium_id, medium_parent_id)

Yes, as mentioned before I am considering something like that. First I'll have to examine the ramification for presentation and analysis first. It might be a good compromise to retain the two-table structure, with one holding the "bottom level" content (which connects to the movies/episodes) and one "top level" which allows for stacking containers. Validation to avoid circular relation could be done on data entry level (that should be fairly easy).

If in doubt, go back to designing your model BEFORE you throw up tables in a database - it will help

Believe me, by now I know that!
Building a database for my movies was the first project of that kind I ever attempted, many many years ago, when I knew nothing of databases and coding. As I learned and gained experience I revised my database (but also new versions of Access, which I'm using, offered new possibilities). The last major revision of this database (now v.5) I made back in 2006/2007.
So you can see that there's a certain element of growing involved. Completely restructuring a "living" db is a pain in the ass. Last time I did that I needed something like 4 weeks only to write the proper import procedures to move the data from the old structure to the new one.
But the current one is quite solid. It this point it is more a minor redesigning, at least if I take the route mentioned above.

From a brief look at your relationship diagram, it does look to me like you've over engineered some of the relationships, but that is easy for me to say because I don't fully understand the model yet

Actually most parts are fine (IMHO). There is a lot of stuff cramped in here, which makes it look worse than it is. I've also learned the hard way to rely on lookup tables. Besides the media section there is only one area which I suspect could be designed more elegantly and at this point there is only one other feature I'm missing (properly maintaining different versions of a movie), and maybe the solution of the second might help me with the first.

As John says, info dumps in one table is a bad thing.

(You posted while I was writing my answer)

It's safe to say that I will hardly be accused of this

[r007]

@claws: Totally agree that it's abuse. Still, for small k (which seems to be the case here, as these trees seem to me to be rather broad than deep) a trade-off that I might be willing to make given the application. And if that's what the customer wants, agreeing to understand the impact of the approach. Sometimes you have to compromise

In my thoughts about a media database (yes, I made some beforehand) a "Medium" had several "Sections" (chapters, tracks, you name it), but as I clearly see from Tea's example, these might again be "Media". I come to agree that Boxes really require a bit of out-of-the-Box thinking here...

Validation to avoid circular relation could be done on data entry level (that should be fairly easy).

If you need to write code (I haven't used access in aeons, so god knows what Access SQL allows as of today) it should also be fairly easy to figure out if you've visited a node before.

[Teatime]

If you need to write code (I haven't used access in aeons, so god knows what Access SQL allows as of today) it should also be fairly easy to figure out if you've visited a node before.

VBA for Access sadly is basically in a pure maintenance state. MS only adds new commands/objects to control new elements, but AFAIK no further development of the language takes place. To my knowledge it's pretty much the same with the SQL-Engine.
Heck, even the IDE has remained unchanged for many years now. Step-by-step debugging is quite tiresome.

But I guess I can handle the validation

[r007]

What I learned:

• Write down all the nouns you can think of that concern your data
• Aggregate nouns that signify properties to nouns that signify things
• Think of verbs or adjectives that connect these things
• Draw an ER-Diagram
• Think of corner cases (we are here right now)
• (have extremely clever ideas here)
• Formalize your ER into tables, observing constraints
• DATABASE! = PROFIT!

Also I has an Idea: How about bottom up? Every Movie can be part of several (1:n) collections, be they DVDs, Tapes, Harddrives, Boxes or Meta-Boxes. Sure, this has a good deal of duplicate information, but avoids the whole graph mess... (edit:) Unfortunately you lose the intermediate layer of which DVD is in which Box.

[claws]

@r007:It's a tough one, I did consider it too...but I guess I'd be thinking of the poor junior programmer coming along a few years later who'd have to try and maintain that, although I guess that's not a problem here! Just my 0.02 euros

@r007 part 2 : I'd think I'd choose the graph rather than the duplication, although I still think we don't need much past k=4 here

Shame about VBA (in some ways). I suppose the .NET family are closest to that functionality in terms of mainstream stuffs

[Finaldeath]

Looks like you're attempting a solution for a problem you created. Yes, I'd agree with the others; if this is for personal use, it seems like much too much information. Medium and media might well be the same; how often do you ever own both the DVD and BluRay version of something? If you have a rip or additional version of something just have an additional related table saying what types of media it is available in...

But if it is all theoretical, knock yourself out, hahahaha