User Name
Pass Word:


Thinking Out Loud
Previous | Next by elfie 16 March, 2009 - 4:01 AM

So I've had this plan for a major overhaul to one of my projects for a while now. But I haven't implemented it because the data structure is kind of wacky. Now of course it doesn't have to be STORED the way its displayed to the user, I know that. But I'm still having trouble wrapping my head around the best way to structure the data in a database.

What I want to present to the user is a chronology of events. Sounds simple, right? Just attach a Date to the Event and ORDER BY? The problem is that events aren't just a single date. They have a start and end which sometimes overlap. Extra fun; with my data there's no actual dates. Just "this happened before that thing, after that other thing, and at the same time as that other other thing." Yay!

The way I've been WANTING to present this on the screen is a down-flowing list where the earliest event is at the top and the most recent event is at the bottom and events that happen at the same time as one-another are BESIDE each other with some sort of vertical alignment so that if C starts in the middle of A and ends in the middle of B you get something like:


Now that's all fine and good but it just seems like a NIGHTMARE to me with regard to coming up with an actual queryable data structure. Each Event could have a many-to-many relationship cross-table. The cross table would have two EventIDs and then a single value indicating "starts before" and "ends during" and stuff like that. Theoretically a really beautiful web of information could be built based on that kind of information, but I can't begin to imagine how I would turn that data into UI. The bits in between seem a little out of reach for my ability. I've seen it done though, so I know is possible. I just don't know if I want to melt my brain trying to figure it out. We'll call that Plan A.

Plan B is that I would just dumb everything down. Force a linear representation of the data and give each Event a SortOrder. If two events happen at the same time, you HAVE to pick one to be listed first. You can use descriptive text to explain that they actually happen simultaneously. This gets a little more complicated to explain in the ABC example above but its still doable.

And to throw an extra bit of excitement into this, I want everything to be user-editable. So with Plan A the ideal would be to let the user actually physically drag around Events on the screen and place them where they belong, drawing lines between related events with the ability to add hover-text to the lines to explain these relationships. That would be a HOT user experience. I would fucking love that. UI for Plan B could also be draggable, but with only vertical placement options, not side-by-side placement, and grouping would be done more by putting a box around related events. Not AS hot as Plan A's UI, but it still gets MOST of the information across visually will still giving the rest of the information across via text.

I dunno. I've been struggling with how to approach this for a while and I haven't made any actual progress on it because I just can't seem to decide how I want to do it. Maybe I need to call a developer conference at DuClaw. We haven't had one of those in a while :p

3/17/2009 >> ben

i would suggest letting your business layer handle this as a type of scheduler class - i mean it sounds like you want a stripped down version of a channel listing, but you only want extra channels for contemporary events... the database could handle it, but i am not a fan of having business data in the database

3/17/2009 >> elfie

But so how would I structure the database then? Each Event having a relationship to its adjacent Events like I described above?

Having the business layer handle it is definitely the way to go, but I think it'd still be crazy complex. It would probably have to be some sort of system where I read the data once, load it to memory, handle all changes in memory and write them to the database, but not re-load FROM the database unless some idle time had passed. Hopefully it won't end up being too much data floating around in memory.

Do you think I should actually build the business layer in a branching linked-list sort of way? Or should the business layer re-format the the objects into columns, like the TV listing idea you were discussing (or is that more of a UI layer issue)?

3/17/2009 >> ben

well, how much data will you have? are we talking dozens, hundreds, thousands or tens of thousands or more rows for events? also, how much would you be pulling back? the whole set at once, or one period of time? you could probably keep SOME of the data in memory, if you've got zillions of rows, but only need a few weeks of data?

and i think the BL should format the data the way you'll view it in the front end, and the DB should have it formatted in the most logical/normalized way that seems fit to you - at least, generally that's how i feel, since i've been in services land for the last 2 years, :)

3/18/2009 >> elfie

Hmm I'm going to say data is going to start in the hundreds range, quickly get into the thousands range if the site takes off, and definitely has the potential to eventually get into the tens of thousands range.

I would definitely NOT be pulling everything back for every request. So it definitely wouldn't make sense to keep EVERYTHING in memory.

Any one screen of data would probably have less than 100 records, but certain sets of data would be more popular than others, getting pulled more often. Perhaps caching decisions could be made based on popularity of the data set?

3/18/2009 >> ben

that would be very cool - but at that point i'd suggest pulling that logic into a separate framework - that's something that you might want to use in a whole lot of places, :)

3/18/2009 >> elfie

Well I already have plans for determining popular data sets and listing them on the home page, so maybe that data would be a good candidate for caching.

Then again, maybe what I should really do is just write them so that they always pull from the DB and I'll worry about caching if there actually end up being any speed concerns. :p


BEFORE I start this I have to finish two other projects though.

1) Rebuild the framework of my facebook apps to use their new API
2) Rebuild using release bits for .NET AJAX (it was originally written using beta bits so when I installed the release bits it stopped functioning)


You must be logged in to comment.


This is a Flickr badge showing public photos from Kheiligh. Make your own badge here.