Planning a database schema for a media library of items.

I have a degree in this respect, what did you want to know like what information did you want to include in your tables?

Sometimes I find putting them all in one table, then trying to group them together for their own attributes works best for the plan of a schema, if that's any help?

Like say you had I don't know for arguments sake, every media entry would have a size of the media file wouldn't it? (if you wanted to display in your site their respective sizes) so I'd put that in the first table perhaps?

I mean it's a huge area databases really but to be honest I find trying to forget about the normal forms as an actual process and then seeing what would work, what you want to avoid in any case is NULL/empty values in your tables (where there's any empty rows in any of the columns you want to try and figure out a way of making those values in their own tables if they even exist.

I really hope this makes some sense?

Apologies for that incredibly obfuscated response/answer, basically try and think of all the information you want to use in a database.

I will take you through the steps to properly design one for that usage, it's honestly not that hard I promise you, been told in the past (though it may take a few days of work) I can set aside a few hours per day to help you with this.

I don't bite at all lol, seriously speaking though, I love teaching people databases

That'd be great, yes it does look very complex but complexity is fun.

I would really love to see it, I can have a look then and suggest any improvements. I generally do think really forgetting the (I'm going to abbreviate the Normal Forms as NF's) like just forgetting about their levels like NF0-NF3. They all depend upon various levels of normalization.

Like NF0 is all the data for that table (what would be outputted I suppose or used in that query), then splitting that up and getting rid of any duplication or empty sets (from what I can remember that's the role of NF1), NF3 (from what I can actually remember) is all about other forms of data like say you had (an actual one I developed for someone/designed for someone in my last employment).

Was they wanted a list of post/zip codes like for example where I am HG1 (didn't matter about the last remaining characters for the UK's country post/area codes) like HG1, HG2, HG3, HG4 etc.

Taking the values from the customers billing address (post codes here I will call them) and putting a count and value next to them, that's actually a NF3 example where the (I forgot the technical name for it) but the first part of the post code in that example serves as the kind of primary key for that table if that makes sense?

Also don't be afraid to experiment with when it comes to querying.

I mean for something I was shown in my previous employment (before I became and am starting up as a freelancer) to put conditionals in your joins.

They really are pretty simple to understand in all fairness and they speed up JOIN (I always put them in capitals lol) something like this (if you were going for a set of users with posts for example in another table, and of course off the top of my head):

SELECT * username FROM users JOIN posts ON users.userid = posts.userid AND users.active = TRUE

That would only join users that are active, otherwise you'd get say if you had 10,000's of thousands of users, for every use in that users table, it'd multiply the querying and be extremely slow!

I speeded up a similar query by say 300 products of something in my last actually working example of a similar query (people don't realize how much you can actually develop your queries like that).

I took a course (free one on the web) from stanford in this, it's really a great one to go through (I wouldn't use my previous join condition since it's just a random one and it's not been tested but illustrates my point I think).