The Art of SQL

too long for a comment, and purely some thoughts, not a full answer, but having dealt with many date effective records in financial systems (not in Django) some things come to mind:

My gut would be to start by putting it on the save method of the resource model. You are probably right in needing a custom manager as well.

I'd probably also flirt with the idea of a is_current boolean field in the state model but certain care would need to be considered with future date effective state records. If there is only one active state at a time, I'd also examine the need for an enddate. Having both start and end definitely makes the raw sql queries (if ever needed) easier: date() between state.start and state.end <- this would give current record, sub in any date to get that date's effective record. Also, give some consideration to the open ended end date where you don't know the end date date. Your queries will have to handle the nulls properly. YOu probably also may need to consider the open ended start date (say for a load of historical data where the original start date is unknown). I'd suggest staying away from using some super early date as a fill in (same for date far in the future for unknown end dates) - If you end up with lots of transactions, your query optimizer may thank you, however, I may be old and this doesn't matter anymore.

If you like to read about this stuff, I'd recommend a look at 1.8 in https://www.amazon.ca/Art-SQL-Stephane-Faroult/dp/0596008945/ and chapter 6:

"But before settling for one solution, we must acknowledge that valuation tables come in all shapes and sizes. For instance, those of telecom companies, which handle tremendous amounts of data, have a relatively short price list that doesn't change very often. By contrast, an investment bank stores new prices for all the securities, derivatives, and any type of financial product it may be dealing with almost continuously. A good solution in one case will not necessarily be a good solution in another.

Handling data that both accumulates and changes requires very careful design and tactics that vary according to the rate of change."

Just based on the diagram, I would suggest that an RDBMS solution would indeed work. It has been years since I was a developer on an RDMS (called RDM, of course!), but I was able to renew my knowledge and gain very many valuable insights into data structure and layout very similar to what you describe by reading the fabulous book "The Art of SQL" by Stephane Faroult. His book will go a long way to answer your questions.

I've included a link to it on Amazon, to ensure accuracy: http://www.amazon.com/The-Art-SQL-Stephane-Faroult/dp/0596008945

You will not go wrong by reading it, even if in the end it does not solve your problem fully, because the author does such a great job of breaking down a relation in clear terms and presenting elegant solutions. The book is not a manual for SQL, but an in-depth analysis of how to think about data and how it interrelates. Check it out!

Using an RDBMS to track the links between data can be an efficient way to store and think about the analysis you are seeking, and the links are "soft" -- that is, they go away when the hard objects they link are deleted. This ensures data integrity; and Mssr Fauroult can answer what to do to ensure that remains true.

I skimmed the answers and apparently nobody has mentioned Stephane Faroult's work.

I strongly suggest you should consider "The Art of SQL" (http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945), I found it really interesting and - amazingly enough, even fun to read.

I would reccomend this book:

https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...

I am not the OP but I would suggest anyone who wants to get better at SQL to read this book:

https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...

I just finished an app about a month ago that's uses what I think would be the most "Flex"ible solution. (hehehehe)

(First Answer:) If you're into/familiar with (good) database design, you could design a SQLite db that allows you to add to & modify all the data you're working with.

(If not, I'd recommend either: http://www.amazon.com/The-Art-SQL-Stephane-Faroult/dp/0596008945/ref=sr_1_14?s=books&ie=UTF8&qid=1336262973&sr=1-14

or

http://www.amazon.com/SQL-Demystified-Andrew-Oppel/dp/0072262249/ref=sr_1_1?s=books&ie=UTF8&qid=1336263052&sr=1-1

...this post is going to take longer than I anticipated! hehehhehee ;P =D )

Basically what it'd be is: Tables for categories (e.g. Volume, Length, etc.) and another for specific name/value pairs ("km to mi" = 0.62137119224 [each in separate columns]) with a category id column to.

Then on your home page you have your init() create an DAO (Data Access Object [research if you don't know already]) for the categories table, then fetch the categories into an ArrayCollection and set it as the dataProvider on your category list (on the home view -- or wherever).

(Second Answer:) Have your change handler for the category list grab the selectedItem and pass it as the second param in navigator.pushView(). That will send the VO (Value Object -- another to research if you don't know it) to the new View as the "data" property.

In the "pushed view," use your creationComplete handler to "catch" (use) the data variable, which will contain the category's name and id. Create a new DAO for the values table and then use the data.id value to load all your values with that category id. Then set your new ArrayCollection as the dataProvider of your value list.

Then create another View for choosing values to edit in the same way. Except the final view in that "flow" would be a form with inputs for category, name, & value (with save & cancel buttons) that would get populated with the appropriate data too. (Note: use a category DAO to get the names of the categories so that the category names & ids are available if you change a category.

...Then it's just a matter of implementing insert & update methods on that View and the SQL & methods needed in each DAO.

You can use Lita (http://www.dehats.com/drupal/?q=node/58) build, design, pre-populate your database.

...I may come back with some nice example code/files (if I remember)

I made some examples for those who were reading and hoped I would...

//////////////////////////////////////  
//VO (Value Object)  
//Category.as  
//////////////////////////////////////  


package dao  
{  

[Bindable]//Makes all public properties bindable
public class Category
{   

    import mx.collections.ArrayCollection;

    public var id:int = -1;
    public var categoryName:String;
    private var categoryDao:CategoryDAO;

    public function Category() {}



    public function get exists():Boolean {
        return this.id > -1;
    }


    //"Super" convenient methods
    //Not really part of Value Objects / Value Object Pattern
    //May actually be a bad practice if you have many VO instances,
    //you have the potential for a DAO instance in each
    //when only one instance could be used.

    public function insert():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.insert( this );
    }

    public function update():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.update( this );
    }

    public function deleteRow():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.deleteRow( this );
    }
}
}


//////////////////////////////////////  
//DAO (Data Access Object)  
//CatagoryDAO.as    
//////////////////////////////////////  


package dao
{
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.filesystem.File;

import mx.collections.ArrayCollection;

public class CategoryDAO
{
    public static var _sqlConnection:SQLConnection;
    public var failed:Boolean;
    public var errorMessage:*;

    public function CategoryDAO() {
    }

    public function getAll():ArrayCollection
    {
        var sql:String = "SELECT * FROM categories"                     
        + " ORDER BY categoryName ASC";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.execute();
        var result:Array = stmt.getResult().data;
        if( result ){
            var list:ArrayCollection = new ArrayCollection();
            for (var i:int=0; i < result.length; i++){
                list.addItem( buildVO( result[i] ) );   
            }
            return list;
        } else {
            return null;
        }
    }

    public function getByCategoryId(id:int):Category
    {
        var sql:String = "SELECT * FROM categories WHERE id=?";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[0] = id;
        stmt.execute();
        var result:Array = stmt.getResult().data;
        if( result && result.length == 1 ){
            return buildVO( result[0] );
        } else {
            return null;
        }
    }

    public function insert(category:Category):void
    {
        var sql:String = 
            "INSERT INTO categories ( categoryName )" + 
            " VALUES ( :name )";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":name"] = category.categoryName;
        this.execute( stmt );
    }

    public function update(category:Category):void
    {
        var sql:String = 
            "UPDATE categories" +
            " SET categoryName = :name" +
            " WHERE id = :id";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":name"] = category.categoryName;
        stmt.parameters[":id"] = category.id;
        this.execute( stmt );
    }

    public function deleteRow(category:Category):void {
        var sql:String = 
            "DELETE FROM categories" +
            " WHERE id = :id";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":id"] = category.id;
        this.execute( stmt );
    }

    protected function execute(stmt:SQLStatement):void {
        try {
            stmt.execute();
        } catch(error:Error) {
            this.failed = true;
            this.errorMessage = error.message;
        } 
    }

    protected function buildVO(o:Object):Category
    {
        var category:Category = new Category();
        category.id = o.id;
        category.categoryName = o.categoryName;
        return category;
    }


    public function get sqlConnection():SQLConnection
    {
        if (_sqlConnection) return _sqlConnection;
        var file:File = 
               File.documentsDirectory.resolvePath(DbUtility.DB_FILE_NAME);
        var fileExists:Boolean = file.exists;
        _sqlConnection = new SQLConnection();
        _sqlConnection.open(file);
        return _sqlConnection;
    }
}
}



//////////////////////////////////////  
//CategoryView.mxml  
//////////////////////////////////////  

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark"
    xmlns:dao="dao.*"
    opaqueBackground="#111111"
    title="All Categorys"
    creationComplete="init()">
<fx:Script>
    <![CDATA[
        import dao.DbUtility;
        import dao.DropPoint;
        import dao.Category;
        import dao.CategoryDAO;

        protected var dbVerifyUtil:DbUtility

        protected function init():void
        {
            dbVerifyUtil = new DbUtility;
            dbVerifyUtil.confirmDb();

            if( dbVerifyUtil.dbExists() ){
                var categorysDAO:CategoryDAO = new CategoryDAO;
                categoryList.dataProvider = categorysDAO.getAll();
            }

        }

        protected function categorySelected():void
        {
            navigator.pushView( CategoryListView, 
                    categoryList.selectedItem );
        }

        protected function newCategory():void
        {
            navigator.pushView( EditCategoryView );
        }

        protected function viewCategory():void
        {
            navigator.pushView( CategoryListView, 
                    categoryList.selectedItem );
        }

    ]]>
</fx:Script>


<s:List id="categoryList"
        left="10" right="10" top="10" bottom="85"
        change="viewCategory()"
        dataProvider="{data}"
        itemRenderer="irs.CategoryIR">
</s:List>


<s:Button label="Add Category"
          left="104" bottom="10" height="43"
          click="newCategory()"/>
<s:Label text="Touch a category to view or edit it."
         y="326" horizontalCenter="0"/>
</s:View>


//////////////////////////////////////  
//CategoryListView.mxml  
//////////////////////////////////////  

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark"
    xmlns:dao="dao.*"
    creationComplete="init()">

<fx:Script>
    <![CDATA[
        import dao.Value;//Value VO
        import dao.Category;//Category VO

        import mx.collections.ArrayCollection;

        import spark.events.IndexChangeEvent;

        private var category:Category;

        protected function init():void
        {
            var category:Category = data as Category;
            listValues.dataProvider =
                    valueDAO.getByCategoryId(
                            category.id );
        }

        protected function editValue():void
        {
            navigator.pushView( EditValueView,
                    listValues.selectedItem );
        }

        protected function editCategory():void
        {
            navigator.pushView( EditCategoryView, category );
        }

    ]]>
</fx:Script>

<s:viewMenuItems>
    <s:ViewMenuItem label="Edit Category"
                    click="editCategory()"
                    icon="@Embed('assets/edit.png')"/>
    <s:ViewMenuItem label="Add Location"
                    click="addLocation()"
                    icon="@Embed('assets/plus.png')"/>
</s:viewMenuItems>

<s:List id="listValues"
        left="10" right="10" top="10" bottom="60"
        labelFunction="labelValue"
        change="editValue()"
        itemRenderer="irs.ValueIR">
</s:List>
</s:View>

I got this out of the book The Art of SQL, pages 284-286:

Let's say your table name is foo.

First, create a table called pivot:

CREATE Table pivot (
  count int
);

Insert into that tables as many rows as there are columns that you want to pivot in foo. Since you have three columns in foo that you want to pivot, create three rows in the pivot table:

insert into pivot values (1);
insert into pivot values (2);
insert into pivot values (3);

Now do a Cartesian join between foo and pivot, using a CASE to select the correct column based on the count:

SELECT foo.id, Case pivot.count
  When 1 Then cat
  When 2 Then one_above
  When 3 Then top_level
End Case
FROM foo JOIN pivot;

This should give you what you want.

Joe Celko's SQL Puzzles and Answers

The Art of SQL (slight Oracle bias)