Pseudo-FAT in a database

I’m creating a pseudo-file system for my game, using a MySQL database as the FAT. I thought it would be fairly simple at first, but as I was about to program it, I realised that my method I planned on using is hugely expensive. Since it’s a call that’ll be used hundreds of times each time the player plays for a short period I really can’t do with it being a resource intensive feature.

ER model of directory table (I'll change this to a prettier one when I have the energy and time to play around in GIMP)

Basically, it’s just navigating directories. Here’s how they’re stored in the database, according to the ER model to the right:

harddrive_directories (directoryID, directoryName, parent)

The foreign key inside the same table with the primary key through me for a second, but I understand that now.

My problem is when a user gives me a string like “/foo/bar/alien/”, and I then have to find the ID for the directory labeled “alien”. I obviously can’t just search for directories called “alien” because there could be a “/foo/alien/” or any other combination. (Though to cut out any needless directory traversing, it would be faster to do that; see if there are any directories called “alien”, if there’s only one work backwards, which is much easier to check if it’s what I’m looking for, if there’s more than one, we don’t have much choice.)

So, I look for a root directory called “foo”. There can only be one, so that’s fairly simple. Now I have foo’s ID, I can look up all of the directories called “bar” whose parent is fooID. And so on, untill I get to a directory called “alien”.

Now I’ve said it outloud, there’s really only n lookups (where n is the number of directories in the path), which isn’t so bad. Is there a better way of doing this though?

Work hard, play hard.

Lets make a productivity list for tomorrow, because the last ones all worked so well…

• Go take photos for my website assignment! I’m gonna take my laptop too, due to the small card capacity. I decided this should be a group activity.
• Write a tutorial on how to add a button to TinyMCE in WordPress.
• Read another chapter of Twilight. Must read this, and the next book before the next film comes out. Granted, I probably have a year, but I’d rather get the entire saga out the way.
• Look through Monday’s class notes, and maybe write a few help sheets. I’ll be working as a classroom assistant, so I should probably be more prepared.
• Wash up. Really, Shane, you have no plates left.
• Get the systems analysis work from Blackboard. I really want to ace this presentation. This is looking to be the only module I might actually fail because of stupid things, like lack of motivation for it.

Busy day tomorrow. Carnage bar crawl is in the night too.

It's not redundant, just plain stupid.

I just decided to pop in here to have a tiny rant about something that’s bugging me about my Database Design class.

I just got my assignment. We’re basically to design and set up a database, create forms, use VBA, general piss easy stuff. I’m a little annoyed to read the specification for this work to find that we’ve actually been told most of the table names, and attributes that need to be there. Thanks for taking away my creativity.

What I’m most annoyed though is this:

Finally ‘status’ is set to ‘current’ and ‘end date’ is left null. When a customer wishes to end a contract the end date is set for the last day of the current month and the status is changed to ‘ended’.

It’s not just me that finds this stupid, right? I can’t even really explain why this annoys me.

Company’s don’t do this. They have minimum term contracts. They’d just have a StartDate (when the customer wants the contract to start) and and EndDate (when it’s to end). In fact, a better design would be ContractLength.

Leaving the empty EndDate seems dumb to me. And having to have some sort of batch process which checks for an EndDate and then updates Status seems like a lot of extra work. Why does Status even sodding exist? In that stupid system, if there’s no EndDate then it’s obviously “current”, else “ended”.

Stupid course is stupid.

I’m actually feeling productive today, and I’m fairly certain I have a multitude of work to do.

I woke up with the intent of creating this database we need for Database Design Concepts. Well, I say need, but it turns out we might not actually have to hand it in. Because I thought it was integral to my learning, I sat through it and slowly knocked up a few tables following the instructions letter by letter.

When it started explaining, in great detail, how to change the freaking font of a label I gave in. Not only was it offending me, but flicking through the book I can’t see anywhere where it’s telling me to hand the finished database in. Even if it did, what would be the point?  You can’t vary from the obnoxious instructions; even if one person created the database and then gave a copy to everyone in the class the person marking it would never know. They’re all identical.

So, I gave in with that. I’m pretty sure there’s more work to be done, so I asked Laura. Apparently we have a Systems Analysis assignment that we’ve not been told about yet.

The final assignment we have is from Computer Tech, to create a website. I’m not sure why I can’t just give him the URL to one of the many websites I’ve designed and coded to prove my work, but meh. I have to create a guide about Leicester for freshers of DMU, which I’m okay with actually. I might even be able to make some money from it. Even if I do feel slightly underestimated.

Why can’t university be as hard as I actually expected? Why do people walk into a degree with zero knowledge of the course? There are people that have blatantly never even used a computer before on my course; wtf? Just make A Level Computing a requirement of the course entry, and teach with that as a foundation. Don’t freaking teach it me again! Grr.

I’d switch to Computer Science if I wasn’t so worried my math would let me down.

Class summary for today

In visual web dev. we just learnt about parameters for functions, and making subroutines and functions. Yeah, it was thrilling. I finished up the set work in the first hour (class is two hours along) and just spent the rest playing with Flash.

Database design concepts was okay. “Okay” is measured by “did I learn something?” and I sort of did. It was about how DBMSs should handle their integrity, using rollbacks and transactions. Also on user privileges.

When I can be more motivated (or if someone asks), I’ll come back and actually explain what they mean, because a few people missed that lecture. For now though, I’m going to work on my Leicester Guide Website for my computer technology assignment.

Weeks one and two summary: Database Design Concepts

We started with a lecture in this class, which was good since most people probably had no idea why a database was interesting, and why they shouldn’t be using a spreadsheet. To be perfectly honest though, I’m sure a lot of people still don’t know. Though, thinking about it, most people seemed to understand some of the SQL so maybe I’m just under estimating them.

Reasons why you shouldn’t use a spreadsheet to store data:

• It’s too easy to repeat data. For instance in a database you could just say “this row belongs to user 85″, and then have one row describing user 85, like their name and such. However, in a spreadsheet, unless you wanted to look up user 85 every time they were mentioned, you’d have to write out their name potentially hundreds of times. And then what if they change their name? You’ll have to go through and at least write  a macro to rewrite all the names, where in a table you’d just need to change the user table once.
• Spreadsheets take up a lot of space, due to the repeat data, and also due to most the cells being in the wrong, or bad, format so compression is harder. For instance, in a database we could just say that a telephone number is capped at eleven characters. The file system now only needs to give that field that many bytes of data. Whereas in a spreadsheet it’d have to be variable length, a much greater resource intensive method.
• They’re not as accessible. Over a network the user would need to download the spreadsheet then make any amendments they want, and then uploading it. They’d also need to lock the file so no one else could even read the data whilst it was in use. With a database, it’d stay on a central server where anyone can access the data at the same time. The database server could better manage user collisions.
• Sticking with that example, a spreadsheet would require tenfold more bandwidth.

That’s enough examples.

After that we just jumped into learning terminology. That’ll probably the hardest thing for me to learn. Since I’m mostly self-taught I’ve rarely had to refer to much to anyone other than myself. For instance, my tutor refers to fields as “attributes”, which is what I could have called it’s type (she calls that the “attribute type”).

As she explained the term “key field” I got a little annoyed. I think she made it sound as though primary keys and unique keys were interchangeable words for the same thing, which they’re just not. Primary keys tell the database that it’s a unique field which is safe to refer to the row as. Whilst that’s the same for a unique field, unique keys aren’t usually used as foreign keys.

During the tutorial we had an example database, which a few tables. As a class (which is good, since most people were new at it, they might be unsure about what exactly the should be doing) we answered a few questions where we had to decide which tables we needed to select to get the information we wanted. Like, “list the students first names”. That was simple enough, so the WHERE clause was introduced. “List the first names of the students who were studying computing”.

As we were working through the lab workbook, I was pretty happy that people sitting close to me felt comfortable enough to ask me if they were stuck. The main problem I think for most people was that they didn’t understand that you need to tell the computer exactly what you want. When the problem asked to return all the students’ addresses, two people that asked me for help got it wrong because they had tried to called the table name `address` when they needed `street` and `town`. I’m pretty sure that’s just a thing they’ll get used to though.

In this class we’re going to be assessed on our ability to write SQL statements, I think. At least at the moment. I’m not sure how advanced we’re going to go into it though. So far it’s just SELECT we’re using, with WHERE, OR/AND, and comparison statements on single tables. Look through the lab work book, I saw that later we’ll be joining tables too. I think I saw a COUNT in there too, so I hope we’ll be using more complex SQL functions. I’ve never had much need to use them before.