Print Story Data. Fucking. Base. Bitches.
By coryking (Tue May 02, 2006 at 09:18:24 PM EST) Postgres, life, lack of life, business, your mom (all tags)
It's all about the database.  Literally.  I'll tell you why.

So yeah.  What did I tell you.  Boring?  It is, at least to the outside observer.  This has been my life for the last two weeks.  I've been on the computer working literally 12, 13, or maybe even 15 hour days.  I go on walks and all I can think about is the next thing I need to finish.  I go to the store and my background process is thinking about the next thing to do.  I even say things like "background process" in conversational speech.

Why?  Cause I fucking have to get something out the door.  Something a customer can enter a credit card number into and pay me.  Until that point, I ain't got nothin' for nobody.  I can't get investors, partners, customrs, or anybody to take anything serious until 1.0.  So. Yeah.  1.0.  Comming soon to a theatre near you.

I'm aiming for the end of this month as a ship date.  Thankfully I'm pretty certian I can make it.  There ain't much until artzaar 1.0, but let me tell you, it has meen my every waking moment.  Very literally.  Wake up.  Eat + Coffee.  Emails.  Code.  Code.  Emails.  Quickbooks.  Bank.  Post Office Code. Code. Code. Code.  Grocery Store.  Cook.  Eat.  Code. Code.  Read a book about qw( business marketing programming ).  Kiss ladyfriend goodnight.  Lie awake thinking about what next.  Sleep.  If lucky, dont wake up at 3am and think about what next.  Sleep. Repeat for 30+ days.

Before I can do anything, phase 1, I've got to lay down the law.  The schema that is.  Well, not just the schema, but the database program as well.  MySQL sucks.  Big time.  There are too many problems to even name but I'll list some anyway: its inability  to enforce referental integrity through foreign keys, its lack of a boolean datatype, the fact it various tables crash all. the. god. damn. time. (I was using 5.0).  Thats done.  Migrated a few weeks ago and got rid of most of the MySQL crap. Moved several mysql "boolean" columns into the real deal.  Layered on a bunch of foreign keys.  Tweaked the shit out of all the table indexes.  No crashes.  Only a few mysqlisms that made their way onto production.  Joins are awesome.  Transactional support is great. So far, life is good!  Nothing is better then using a real database.

I've still got to pick an ORM to sit on top of the database and I really dont feel like writing a bagillion CRUD classes mysql.  The top contender seems to be Rose::DB::Object.    I've spent the day looking over the docs drooling - it's exactly what I want.  I tried fooling with DBIx::Class but something about it just didn't resonate with me.

What Database Schema?  Well, for starters the image system is due for an overhaul.  It's moving from a single monolithic table with about 20 fields into about 10 different tables that describe an image's info (EXIF data, keywords, etc), variants (a thumbnail, a random image /w title, a raw copy, etc), and... the system will store all image data in a table.

After giving it quite a lot of thought, I came to the conclusion that putting all image data into the database really is the way to go.  For starters, I'd get all the great features a database can give me such as referential integrity and transactions.  I'm pretty sure any performance issues can be mitigated somehow.  Worst case is I'd have to move them back into the file system, but I truely expect the system to perform much faster when they are in they are all kept safe and sound nestled in the database.

Once I'm done with images, I've got to get albums set up.  Photographica never really supported any way to catalog a colletion of images, which has become a huge issue because many long time users have several hundred photos.  With albums, this will change (well, it will for paying subscribers that is... cough).

Once those two core compentents work, I have to layer on the whole "sell your photography" business.  This secret sauce will do the magic of letting a customer sell their art at the price they name.  Doing this right is critical; it's the most important part of the whole operation.  The system need to be designed in a way to be easy to extend down the line to support inventories, framing, matting, shipping, PO's, vendors, people shipping their own stuff, and pretty much anything you can dream of.  Big stuff here.  Lots of time.  But can't do until Images & Albums.

Those are the "big coding things" that need to get done before 1.0.  It's all downhill after that, codewise anyway.

I've got to make a simple way for people to change the color & logo of their website without knowing any HTML.  This shouldn't be to hard.

I've got to make a way for a customer to access their account data.  They need to be given highly addictive sales stats that keep them hitting F5 all day.

Oh yeah, the nightmare that is billing.  Need to write the management code to bill thousands of customers and bug them when they dont pay (expired card, bad card, lost card, stolen card, fake card, your moms card).  Course, that is all business policy first and foremost; stuff that will probably get created as things evolve.

Oh yeah, I need to get a professional webdesign including a logo.  Business cards.  T-shirts.  Fun stuff.

Oh yeah.  Phones.  800 number.  Landline? VoIP?  Cell only (no!)?

Fuck.  Tech Support.  Somehow have to handle that.

Oh yeah.  A law guy should probably look over the TOS.

Oh yeah.  Fucking accountant.  Probably could use one of those this year.

Oh yeah.  Marketing.  Biz Dev.  Much more imporant & fun than programming; you gotta pimp your stuff, right?  Talk to people, right?  Gotta get that process moving, but can't do much though until I have a product to sell.

Oh yeah.  Networking.  Gotta get out there and chumb it up with the other boys in town.  There are dozens of other cool outfits around town started by zany nerds like me.  I'd love nothing more then to have beer and pick their minds.  Fun stuff, but can't do it until after 1.0.

Pet the cat?  Vacation?  Life?  Friends?  Family?  Sorry.  I have to ship first.

So yeah.  That is my life.  A never ending series of dependancies that all hinge on a fucking database schema.

How is your day?

PS:  This never got spellchecked or really even proof read.  The cat is meowing and needs some kind of attention even though he refuses to play with his toys.  Since scoop doesn't let me save drafts and I'm too lazy to save this to a file you just have to deal with them all.  Sorry.

Badly needed functionality that will get added when I gut out the "legacy story system".

< There's trouble on the home front | BBC White season: 'Rivers of Blood' >
Data. Fucking. Base. Bitches. | 10 comments (10 topical, 0 hidden)
Nice essay. by ambrosen (2.00 / 0) #1 Wed May 03, 2006 at 02:25:35 AM EST
I'd say that as long as you don't use the table with the images in for much else, then you're right.

Enjoy, anyway.

I actually abstracted it by coryking (2.00 / 0) #5 Wed May 03, 2006 at 06:18:43 AM EST
To just be a "binary" table.  All it's got the data, the size, an MD5 hash and a reference to a "file type" table so I know what the hell is in there.  All the image stuff just keeps a pointer to the binary table.

Worst case is we get some programmer who likes to JOIN every god damn table in the database and then use SELECT *...

Dog food. Snack for some. Feast for others.

[ Parent ]
I'd use a different table per type of binary. by ambrosen (2.00 / 0) #6 Wed May 03, 2006 at 06:36:23 AM EST
Well, that's my instinct. I'm sure your design makes sense, though.

Sadly, I'm getting a headache thinking of the toy database that is used where I work, which it is not in my remit to change, despite its hideous inefficiency and painful data design. You missed FileMaker out of your poll, by the way.

[ Parent ]
binaries by coryking (2.00 / 0) #7 Wed May 03, 2006 at 08:07:06 AM EST
Just out of curiosity, why would you go with a table per binary type?

I did miss out on filemaker.  Never got the "pleasure" of working on it.

Dog food. Snack for some. Feast for others.

[ Parent ]
Assuming they're holding different things, by ambrosen (2.00 / 0) #9 Wed May 03, 2006 at 12:29:57 PM EST
I'd view them as helper tables for the table containing their metadata. If by type you're talking about file formats, then definitely in one table.

But I wouldn't take my advice, anyway.

Filemaker may well be better than Access, I don't know. But it's being used well into "You want a proper DB for that" territory here, and I have neither the credibility (because the original DB work I did was optimistically based around being able to do basic joins, programatically vary search parameters, programatically vary export files, etc.) nor the desire to migrate, nor to refactor. Not at minimum wage, anyway. I'll stick to packing books. I get less ire from the original dyslexic designer of the database that way, too.

[ Parent ]
maybe this is naive, by rmg (2.00 / 0) #2 Wed May 03, 2006 at 04:01:12 AM EST
but it sounds like it's basically a middle of the road content management system/store. perhaps a full-fledged database is overkill. for example, i run my blog on bloxsom, which is basically just a perl script and some flat text files for data storage. it might sound primitive, but it works well, it's fast, and it's easy to hack. it even stayed up when i got slashdotted a couple months ago.

obviously, if you're writing banking software or some kind of genome sequencer, mysql or some similar database is the way to go, but for content management, i think databases are more trouble than they're worth. it's just endless little things and poor full text search performance.

or maybe you could compromise. i hear good things about berkeleyDB...

[t]rolling retards conversation, period.

Good Suggestion, by coryking (4.00 / 1) #3 Wed May 03, 2006 at 06:11:43 AM EST
We looked at BerkeleyDB in planning stage of our project.  Unfortunately our managers are requiring that we run on Linux and not Microsoft Windows NT, the native platform of BerkelyDB.  In addition BerkeleyDB has an incompatible "license" with Linux's "GPL license" that would prevent us from making a port.  Worse, since Linux does not support threading or OLOG(N) transactional safety, our only choice was Postgres.

But thanks for the suggestion anyway.

Dog food. Snack for some. Feast for others.

[ Parent ]
This isn't K5, woman. by MohammedNiyalSayeed (4.00 / 1) #4 Wed May 03, 2006 at 06:12:42 AM EST

Try harder.

You can build the most elegant fountain in the world, but eventually a winged rat will be using it as a drinking bowl.
[ Parent ]
Heheh by kwsNI (2.00 / 0) #8 Wed May 03, 2006 at 08:39:32 AM EST
Pet the cat?  Install Gallery2? Vacation?  Life?  Friends?  Family?  Sorry.  I have to ship first.

I thought you'd like my find of the day. by ambrosen (2.00 / 0) #10 Thu May 04, 2006 at 05:08:02 AM EST
From some site telling you what to do if your SQL's bollixed:
MS Access is characterized by strong data typing;
So near, yet so far.

Data. Fucking. Base. Bitches. | 10 comments (10 topical, 0 hidden)