Here's a little behind-the-scenes peek at how projects are hatched in my company. This is the transcript of an instant messaging session between me and my right-hand man. This is posted for your amusement and/or education, however you'd like to take it.
[Note: Some of the time stamps are out of sequence because I re-ordered some of the comments to make the conversation flow more logically when multiple threads were being discussed simultaneously.]
15:05 Trak3r: got a few minutes?
15:05 Kanook: yes
15:05 Trak3r: -----------------------------------------------------------
15:06 Trak3r: demarcation line for the log when i look this up later
15:05 Kanook: me over there?
15:06 Trak3r: no lets do this in chat
15:06 Kanook: ok
15:06 Trak3r: topic of discussion: images in the database
15:06 Trak3r: (let me dump my thoughts first)
15:06 Kanook: ok
15:06 Trak3r: 1. need servlet to suck them out and serve them -- it would of course cache them, keeping them in memory
15:07 Trak3r: 2. need a servlet for putting them in the database so the [managers] can upload/update them -- also this is a requirement for [client-a] (uploading new widgets/sub-cobrand images)
15:07 Kanook: first q (answer at your convenience): do we have to store them in the db?
15:07 Trak3r: 3. can we hack iis to redirect /images to the servlet so we can move all existing images into the database w/out have to re code templates?
15:08 Kanook: er
15:08 Trak3r: 4. ... shit you broke my train of thought
15:08 Kanook: then don't read my qs :-P
15:08 Trak3r: 5. do we want the database table structure to mimic a directory structure (with nested groupings)?
15:09 Trak3r: ok, discuss
15:09 Kanook: q2: is this for *all* images or something? I thought it was just for those [client-a] images in their tables
15:09 Trak3r: this discussion is about moving *all* images
15:09 Kanook: holy crap why?
15:09 Trak3r: why 1. [client-a]
why 2. so the [managers] can manage the images
15:10 Kanook: Well, 2) is asking for trouble of course
15:10 Kanook: but that's another story
15:10 Trak3r: indeed
15:10 Kanook: Please expand on 1)
15:10 Kanook: Also, please expand on why the [managers] need to manage images
15:11 Trak3r: main 1 or why 1?
15:11 Kanook: Finally, please expand on why the images need to be stored in a DB
15:11 Trak3r: one topic at a time
15:11 Kanook: why 1). Why does [client-a] mean that we have to put all images in a DB
15:11 Kanook: Ok
15:11 * Kanook is longing for good wiki software
15:11 Trak3r: [client-a] does not mean we need to put all images in the database. i'm saying [client-a] requirements are a good reason to do so.
15:12 Kanook: ok
15:12 Kanook: what are the [client-a] requirements?
15:12 Kanook: All I know is that they *currently* have some images in their DB
15:12 Trak3r: [client-a] requirements are to upload widget and cobrand images at their whim and see them live immediately
15:12 Kanook: ok, that's fine
15:12 Kanook: now, next topic
15:13 Kanook: Does it make sense to store these in a database, as opposed to a file system?
15:13 Trak3r: yes (reasons...)
15:13 Kanook: ok
15:14 Trak3r: 1. they have to be centralized
2. the only thing we have centralized is the database
3. if we centralize a web server, it becomes a single point of failure
4 .that would require a servlet that wrote files to the disk, which i consider to be a tad more problematic than writing to the db
15:14 Kanook: Ok, reason discussion begins.... now
15:14 Trak3r: one at a time
15:14 Kanook: yup
15:14 Kanook: r1) What does "centralized" mean?
15:15 Kanook: And why do they "have" to be it?
15:15 Trak3r: centralized means a single source to which to upload -- we can't upload to load balanced web servers if we use the file system since they each have their own disk
15:15 Kanook: right
15:16 Kanook: r2) We also have a centralized file share
15:16 Trak3r: 6. revision control (i sent this for logging, we'll come back to it)
15:16 Kanook: you missed 5
15:16 Trak3r: that centralized share is only for the two db servers -- there's no secure channel from the web heads to the disk array
15:17 Kanook: erm
15:17 Kanook: let me verify that
15:17 Kanook: I know for certain that I have some shared folder between all servers
15:17 Kanook: and IIRC it's on the "disk array"
15:18 Trak3r: yes your and mine accounts are authorized to access it, the iis account is not (and for good reasons)
15:18 Kanook: Ok, that may be
15:18 Kanook: however
15:18 Kanook: is there any reason why we couldn't create a folder on the shared disk array and give each IIS account read access to it?
15:19 Trak3r: which iis would write to it for uploads?
15:19 Kanook: hrm
15:19 Kanook: well, IIS wouldn't... we'd have to build something to accept the uploads & write them to the disk
15:19 Kanook: (ie: a servlet)
15:20 Kanook: (alternately, we might be able to use our [client-n] servlet, but that's a side issue)
15:20 Trak3r: sounds complicated and excessive so far
15:21 Kanook: But more complicated/excessive than writing to the db? :-P Maybe, maybe not.
15:21 Trak3r: what do we need? a centralized storage device accessible from the web servers. do we have one? yes, the database. why try to create another one via the disk?
15:21 Kanook: yes, that is a good point.
15:22 Kanook: One reason: it might take up excessive amounts of disk space for an already-large db
15:22 Trak3r: hmm
15:22 Kanook: OTOH, we could probably put this stuff in its own database
15:22 Trak3r: (thinking and clicking ... please hold)
15:22 Kanook: Ok
15:23 Kanook: I do agree with you on reason 1: centralization. The means are still up for discussion but the ends are clear in my mind
15:23 Trak3r: /images is currently 7mb -- a fraction of a fraction of the db
15:23 Kanook: 7mb? That's it?
15:23 Trak3r: yes, but...
15:23 Kanook: wow
15:23 Trak3r: i want to entertain the notion of putting other non-image binaries in there (like shockwaves and power points)
15:24 Kanook: so entertained
15:24 Kanook: However
15:24 Kanook: This is starting to get into my next point
15:24 Kanook: which is...
15:24 Kanook: Why do we want to have *all* images managed by this system?
15:25 Trak3r: 1. to avoid having to manage two image systems
2. so that the [managers] can control anything they wish
15:25 Kanook: Ok, let's talk reason 2) here...
15:25 Kanook: The [managers] managing stuff, while theoretically desirable, has certainly generated a lot of headaches
15:26 Trak3r: that relates to my point #6 -- revision control
15:26 Kanook: And, even when they have the power, they still often delegate it right back to us... as evidenced by the freaking articles that they sent me just today
15:27 Trak3r: yup, and that's something we're going to beat out of them come hell or high-water (note: i have no idea wtf that cliche means)
15:27 Kanook: re: version control. I'm not just talking about being able to undo their fuckups. I'm talking stuff like "how do I upload a file again?". Also, "do I want to roll this back or not?"
15:27 Kanook: high water is probably a Noah reference
15:27 Trak3r: ah
15:28 Trak3r: yes the admin interface will have to be fairly idiot proof, allowing them to roll forward/backward revisions, but NOT do stupid shit like rename images or move them to different directories, since their references will still be hard coded in the [html] templates
15:28 Trak3r: ...
15:28 Trak3r: ... unless we use some sort of image type class for dynamicicity [yes i just made up a word]
15:28 Kanook: I'm not confident in the [managers] being able to managing anything. I'm also not confident in us being able to teach them anything... after 5 years of [client-n] we've still had limited success.
15:29 Trak3r: ok, duly noted that the [managers] are morons and a liability. i'm not going to change your mind on that. next topic :-)
15:29 Kanook: Ok next topic...
15:30 Kanook: (note: we could not give the [managers] the power to manage images; that doesn't necessarily rule out an image database)
15:30 Trak3r: (i am SO going to blog this conversation (with names changed to protect my ass))
15:30 Kanook: heh
15:30 Kanook: Next reason: > 1. to avoid having to manage two image systems
15:30 Kanook: That's a noble goal on the surface
15:30 Trak3r: i'm superficial :-)
15:31 Kanook: However, it begins to worry me that we'll be 100% dependant on whatever we (have time to) build
15:32 Trak3r: well we obviously wont migrate over until the system is satisfactory
15:32 Kanook: but here's the thing:
1) we'll need some sort of system in place for [client-a], soon
2) We'll need plenty of time to build a system that does *everything* we need it to do, for all clients
15:33 Kanook: [client-g] is at the forefront of my mind in this respect
15:33 Kanook: They ask for some stupid stuff when it comes to images & flash files
15:34 Trak3r: 1. not necessarily. image uploading is not a requirement for this launch, just early next year. it will continue to be done manually for now.
2. i don't think it will take as long to build as it will to design ... which is what we're getting a start on here
15:34 Kanook: 1) good
15:34 Kanook: 2) what I'm worried about is the "oops, we forgot about that"s that crop up
15:35 Trak3r: ha, that's a worry of mine with every patch that hits the production server :-)
15:36 Kanook: I really don't want to be clicking a form a thousand times to upload 90 new shockwave files when [client-g] decides they want to renew their demos
15:36 Kanook: I'd rather just copy the files over
15:36 Trak3r: well, the point is -you- wouldn't, [manager] would
15:37 Kanook: But that's wishful thinking. For starters, *all* the [client-g] content changes get delegated to me
15:37 Trak3r: that's one of the points of this project: to get trivial crap like image management off your plate
15:37 Trak3r: maybe next we'll move templates into the db and the can fuxor them too ;-)
15:37 Kanook: Sometimes it's not a trivial thing though
15:38 Kanook: Here's another point: it's much harder to synchronize database records between servers than it is to fling files around
15:38 Trak3r: with the separation of verbiage and formatting templates, we could [relatively] safely let the [manager]'s munge the verbiage ones :-)
15:38 Kanook: such as Dev->Stg, stg->prod, prod->stg, dev->[client-g]
15:38 Trak3r: hmm
15:38 Trak3r: let me mull that one while i piss
15:38 Kanook: Yes, but that's a pipe dream for some other century
15:39 Kanook: Ok, I'll recap while you do:
15:39 Kanook: We've already established that we need an image management system that's accessible to someone other than you and I
15:39 Kanook: [client-a] will be the #1 user of the system.
15:39 Kanook: The [managers] may or may not be #2
15:40 Kanook: The system needs to be fault tolerant
15:40 Kanook: (which generally means load balanced and failed-over)
15:40 Kanook: I think that's it
15:41 Kanook: my points:
15:41 Kanook: 1) Not *every* image needs to be managed this way
15:41 Kanook: 1a) Most images don't change at all
15:41 Kanook: 1b) Some images change in unexpected ways
15:42 Kanook: (1b sounds goofy; let me know if you want an explanation)
15:42 Trak3r: sure, fire off some examples
15:42 Kanook: Ok, ex 1:
15:42 Kanook: last night, [client-g] sent me a crap load of Shockwave files to add to the site
15:42 Kanook: (29mb zipped; no idea how much they were unzipped. about 90 files total)
15:43 Kanook: So I add them to my server
15:43 Kanook: I change the name of the directory they're sitting in a couple of times (I wanted to pick a meaningful name)
15:43 Kanook: Then I change a JS file to point to the right directory.
15:44 Kanook: Then I check my server to make sure they're working properly
15:44 Kanook: It looks good, so I send it to the [client-g] server
15:44 Kanook: THEN I get word that what the [client-g] guy sent me was incorrect...
15:44 Kanook: some files changed, and some files were to be deleted
15:44 Kanook: so, I wipe the directory clean, and wait for him to upload the new set
15:45 Kanook: I copy those over to my server, test, and then copy them to [client-g]
15:45 Kanook: there they sit
15:45 Kanook: They're not checked in, because they're not approved; they could be changing at any time
15:45 Trak3r: these are quality use cases
15:46 Kanook: once they're approved, I'll add them to VSS and then upload them in a patch. I can do that without asking you to run any SQL on the db
15:46 Kanook: That's ex 1
15:46 Trak3r: so what i'm hearing is we might need...
15:47 Trak3r: - a command line tool for putting a file into the db
- a command line tool for copying a file-record from one db to another
- a way to batch both tools
15:47 Kanook: what we *need* is something for [client-a] to upload their images to. What we *have* already is good enough for everyone else
15:47 Trak3r: i disagree on your second point
15:48 Kanook: Ok, how so?
15:48 Trak3r: 1. image maintenance is a burden to us
2. images bloat the drops
15:48 Kanook: 1) Image management is a burden, yes. However, I think moving them to the database would *increase* the burden.
15:49 Kanook: (that is, moving *all* the images)
15:49 Kanook: obviously, there could be some cases where non-[client-a] images would be best handled by the [managers]; I'm OK with delegating that to this new image database
15:50 Kanook: However, I think it is in our best interests to *not* apply this system to every image
15:50 Trak3r: how about you write a plug-in for windows file explorer that looks and acts like a file system folder so we can drag and drop files into it but it really puts them into the database? ;-)
15:50 Kanook: Ok I'll get right on that :-P
15:50 Trak3r: hmm, ok...
15:51 Trak3r: so perhaps we're considering using [client-a] as a burn-in period for this system at first, leaving all other clients with the file based system
15:51 Kanook: File-based image management is wonderfully KISS for images that don't change often.
15:51 Kanook: Yes
15:51 Trak3r: plausible, but has a few caveats
15:52 Kanook: It does violate the "1 system" rule... but I think we'd be crazy to jump in headfirst
15:52 Trak3r: [client-a] image upload interface is going to be severely limited -- when the upload a widget image, we're already going to know where it goes and to which widget it's mapped, so they wont get the tree-structure image-naming support we'll need for later migration
15:52 Kanook: why not?
15:52 Kanook: Who says we can't build it and not use it right away?
15:52 Kanook: And who says we can't add it on later?
15:53 Trak3r: when they upload an image for widget #13, that image is going to be named 13.jpg and go into /images/widgets -- they have no say in that matter
15:53 Kanook: ok...
15:53 Trak3r: we can add it on later, but [client-a] will not use it and therefore can't be considered a "burn test" for it
15:53 Kanook: that's fine
15:54 Kanook: I'm not worried about the system not working as designed
15:54 Kanook: I'm worried about the system not being designed to do what we need it to do
15:54 Kanook: (or involving much more difficulty to get it to do what we need)
15:55 Trak3r: what [client-a] needs it to do is but a small subset of what we need it to do, so we can't consider them much of a proof of concept
15:56 Kanook: Ok
15:56 Kanook: So where does that leave us?
15:57 Trak3r: it leaves us with a few more questions than answers, but that's a good stopping point for today. i see by the time stamps we've spent exactly one hour on this conversation
15:57 Kanook: hrm
15:56 Kanook: BTW, what do we "need" it to do, beyond what [client-a] needs?
15:57 Trak3r: beyond what [client-a] needs:
- tree structure
- naming of images
- revision control
15:58 Kanook: Note that we have all of those right now :-)
15:58 Trak3r: minus the "centralized" bullet
15:58 Trak3r: ---------------------------------------------------------------------------
15:59 Kanook: yes, but right now "centralized" is not a huge concern... the web servers pull pretty much the same copy of the file from Blue on a re sync.
15:59 Kanook: don't you "------" me when I'm still typing :-P
15:59 Trak3r: LOL
16:00 Trak3r: i hear ya. we'll pick this up again soon.