[PHP Image Gallery] Database Design

06 Feb 2011 10 Comments

by Nikkiin programming Tags: , , ,

I really need to come up with a name for this project.

Anyway, if you follow me on twitter (and, to the best of my knowledge, everyone who reads this does), you probably already know that I spent the day working on the database design for this project (and watching V).  Yeah, I know, I spent way too long on it, but I had issues trying to wrap my brain around many-to-many relationships.  It’s a simple concept, I know, but for some reason it wouldn’t click in my brain until tonight.  But I’m quite excited that I finally got past that mental roadblock, largely thanks to @IJustRealized, who told me exactly what I had to do and then I over thought it.

So, in the interests of records keeping (and soliciting advice), here’s what I’ve got.  Why, yes, I did have fun playing around in MS Visio.  Gotta use that MSDNAA license for something.

So, what did I do terribly wrong?

Previous Next

  • Anonymous

    God, I hate thinking of names. It’s probably the most painful part of software design for me, and it usually ends in things with names like “PyTide” and “Otro” (both of which are real-life examples of how badly I suck).

    With that caveat in mind, I’m going to list all the names that pop into my head.
    - PhotoShelf
    - PicFairy
    - ImageWarrior
    - Sukiri

    Also, I have a feature request that takes the whole easy-in, easy-out, easy-metadata philosophy a step further: some sort of statistical analysis with tags that gives you “tag suggestions” based on the current tags – for example, it notices that stuff tagged “Sam Carter” often also has the tag “SG1″, so images that only have one of those tags show up with a question-mark-icon-ed tag of the other when you load the page, and clicking on that tag will add it to the pictures tags (confirm it). This makes metadata easier and more useful, especially over time, which encourages people to take the time to use it.

  • http://anovelconcept.net Nikki

    Haha Sukiri kinda came out of left field, XD Niiice.

    My system for naming software is to make it as obnoxious as possible – recursive acronyms, acronyms within acronyms, acronyms that can be easily backronymed to something offensive, etc, etc. It’s pretty terrible, I know.

    The tag suggestion thing is brilliant – definitely going on the list!

  • Anonymous

    Thank you! I was originally going to go with Katana, but it was taken (binary hotpatching something-or-other software). Also glad you like my feature request!

    How I would do privacy (not necessarily the correct way) is to have a table of privacy levels, with columns for every action that might need to be permissions-regulated, with 0 being a special override for “inherit” (same permissions as containing album, or if a top-level album, same permissions as user default). Then when you’re checking permissions, you check if it’s 0, and if it’s not, use the foreign key to dig up the permissions object. If you want to do joins, you can make a blank permission level object for id=0 and then just ignore the properties when it shows up in your results.

  • http://anovelconcept.net Nikki

    Hmm, interesting permissions idea. I think I’m going to go with it, actually – I think it’ll scale nicely with multiple users.

    Whiiich, oh crap, I forgot a user table. Whoops.

  • http://www.monoclelad.com monoclelad

    I’m not super experienced with mysql databases, but I am confused as to what you are using the slug for. Most of what I see that is ‘wrong’ would be things that are missing that seem like you’ll need, like users and permissions so that things like ‘private’ have meaning.

    Although I suggest, changing private to Privacy_Level and making a separate table that has all the permission levels that photos and albums can have. (everyone, logged in, friends only, etc.)

    I assume 10 is used as a placeholder for a decent length number? Even still shouldn’t the description type be a text field so it can be an arbitrary size?

  • http://anovelconcept.net Nikki

    The slug is used exactly the same way as WordPress’ slugs – by default, it’s just the name with all non a-z, 0-9 chars replaced with dashes, but it can be user set to something else.

    Yeah, the permissions level table is going in – Philip mentioned that above, XD And I noticed the missing user table too (also above XP) so… yeah. This is why I post things online before doing anything, XD

    Oh, sorry, the char(10) is the default setting in Visio – I couldn’t make it a string, which is what it’s supposed to be, and even when I tried installing the MySQL options, it wouldn’t fucking load so I gave up as I’m only using it to make pictures anyway.

  • http://www.monoclelad.com monoclelad

    I didn’t read the other comment thread, Disqus recently changed something on their end to make it ‘ipad friendly’ which is apparently when you take a perfectly functioning system and make it dumb. so all the comments are collapsed by default and have to manually open every reply, add that to the list of reasons I am wondering if I backed the wrong horse picking disqus over intense debate.

  • http://anovelconcept.net Nikki

    Y’know, I still can’t figure out how to change the textarea size in CSS – so annoying.

    The Disqus twitter account told me that ‘Chrome was fucking it up’. Riiiight.

  • http://www.monoclelad.com monoclelad

    Well thats clearly a lie, it doesn’t work on ANY browser I tried on my mac.

  • http://anovelconcept.net Nikki

    Yeah, pretty much. I was not impressed with that response.