advanced Category Management

2010.02.08 mrooney: I added the link to the wiki page as the specification URL; please visit it and add any info/discussion! Also there was a similar blueprint recently added which I've marked as superceded by this; this must be a good idea!

2009-05-19: simpsus@kolmis:
1) You are right about disk space, but we didn't think about that... our db is so small, unless we turn into Rockefeller we won't hit space problems
2) You are also right about the complexity in programming. Wolfer and myself had to a few hassles to get to the point that could have been easier.
We still believe it is the right way to do database design. We both remember vaguely from our database class at university. I did a quick google about the issue but I don't want to bore you with the first blogger talking about it. So, as a conclusion: I think it is cleaner to avoid null values as they show weak design, but I could not get a valuable supply from google in 2 minutes time.

So maybe you are right. Maybe I just miss the reason to tell you. I am pretty sure there are some cases where null values can lead to unexpected results (referential integrity etc.), but I am also pretty sure that they don't apply here

2009-05-18 kolmis@wolfer:
and what is the main objection to a table having "too many null values" in a column ? is that a thing considered in db design (being a self taught db guy, i'll be glad to learn the right way). if you're targeting disk space occupied by the db file, i think that sqlite null storing is pretty tight, in fact i believe it costs zero bytes of space under certain circumstances (see http://www.sqlite.org/fileformat.html#record_format ). my point of view is that this optimization is not worth the added code and sql complexity

2009-5-18 wolfer @kolmis:
the approach with additional columns would lead to many fields with value Null in the database. So we created those two tables to avoid these Null-values. the categories are designed to have at most one parent category and each transaction has one category at most.

2009-05-13 kolmis @simpsus, wolfer:
i checked out your branch, i have one question: why did you decide to create the categories_hierarchy and transactions_categories_link tables ? wouldn't adding a categoryId column to the transactions table and parentId column to the categories table be enough (and a simpler solution) ? if you agree the at-most-one-category to a transaction constraint and one parent category to a category, i'd say that's the way to do it.
one more thing, one of you has to change his code editor's setting to not trim the trimmable lines, it makes a seeing what a commit changes a bit harder to read :)

2009-05-13 simpsus:
@kolmis: I fully agree with you. My wording is unlucky here. We want to leave tags they way they are after wolfgangs patch and start on the new category approach

2009-05-13 kolmis: i'll still be advocating the categories & tags approach.
categories:
 - hierarchical
 - exclusive (one transaction - one category only) => suitable for reports, pie charts, etc.
 - simpler to use
 - reasonable to bundle a moderately sized predefined translatable tree of cats with wxBanker.
 - example: Bills->Electric; Bills->Internet; Income->Salary

tags:
 - non exclusive (one transaction - multiple tags)
 - more of a filtering role than transaction categorization
 - of course when one sticks to one transaction - one tag rule, then these are just non-hierarchical categories

2009-05-13 mrooney: This of course depends on the original transaction-tagging spec, so I've added the dependency there. Let's get that spec into a clear and defined state before we worry about advanced tagging :) So, contributions and feedback welcome / desired there.