It was some time ago, and repeatedly, that I’ve mentioned my desire to rework some parts of the site. In particular, the “Downloads” page currently goes to a “custom” CMS of sorts which uses a Database that I have to manipulate manually to add items, as well as using my older site design. (I still like it, but apparently everybody calls it “retro 1996” so I don’t think it really works for what I was going for). I’ve considered a redesign of that main site, since I really don’t want to scrap it, but for some time now I’ve merely redirected the main website straight to this wordpress blog.

As I was writing this, I intended to and had even started to design a new database table to attempt “another go” at a download CMS. My entire plan was to effectively “blog out” each step along the way- since such a task would involve not only writing PHP, but would also involve reworking my existing, older “Update” Library that provides update functionality (or, it is supposed to, at any rate) to some of my existing programs, so it would be quite code intensive. It suddenly dawned on me that there may very well be a WordPress plugin for that, so I’ll be taking a look into that shortly (It looks like some of those available may meet my needs). ‘Thankfully’, even if I take that route I will need to make modifications to my client-side library to appropriately interface with my wordpress site to retrieve downloads, so it won’t be a completely solved case. For now, I’ll share my initial design phase.

The first step to a new design is to evaluate the existing design and determine where I went wrong. My existing database suffers from several problems. First, there is no consistent casing in the field names; second, the field values are sometimes repurposed- some of them are supposed to be URLs, but if the URL isn’t valid, they are supposed to be treated as something else. There are magic number fields (What is a download type? I don’t even know and I wrote the bloody thing, apparently). So with all these issues in mind, I decided to redo the design work.

Do one thing

The existing design does too much. I was going for some weird over-arching CMS, with Links, Articles, Downloads, and flash movies and the like all being available in one database and from one section of my site. This made sense before I had a wordpress blog here but now some of that is irrelevant; rather than store links there, I can put them in a standard blogroll. Furthermore, I would ideally be able to have some sort of integration with wordpress (this why I’m leaning towards plugins as I mentioned) rather than have the downloads seem to be hosted “somewhere else”. of course, with that original design, it was simply trying to do too much, and since I didn’t design it for that originally, it started adding magic “dltype” fields to indicate what kind of an item it was, and repurposing fields. “If it is this type, these fields mean this, otherwise, they mean this other thing” which was a complete mess. The solution, of course, is to simply do one thing. In this case, I only want it for downloads, and that is it. So we can pare off a lot of excess fat from the rump of the problem just with that.

One annoyance in the original table was that I named fields stupidly, and they use inconsistent casing. When you have to repeatedly look up the schema to figure out if you put a field name in properly, that is a problem. the solution I devised for this was simple- make them all lowercase field names. No more question of “Was that uppercase? was it Pascal Case? etc. since that becomes a non-starter question since it is all lowercase.

I redesigned the schema, and came up with the following table layout:

This is much simpler than the existing schema, which should, in turn, simplify implementations dealing with it. The next step, then, would be coming up with a design for the PHP pages for displaying and managing the database table information.

However, for simplicity’s sake I rather hope WordPress plugins will do the job- then it becomes a question of determining how to retrieve that information client side- hopefully, a SOAP or other remoting API will allow pulling down information about downloads, but that remains to be seen.