What would you guys do?

[Mark Smit]

Hello again,

Just wondering how you guys would solve this problem...

1. I have a hierarchial structure consisting of Layouts which have several Tracks each of which will have various Layers.
2. I need to store this data into a SQLite database.

So would you...

A. Create a tables for Layers, Tracks and Layers and use the various RowID's to link them together or....

B. Create one table "Layouts" and store the actual structure in some sort of XML-like script in a TEXT column. (RowID, LayoutName, LayoutXML)

Thanks again!

An example script might look like this...

Layout:"StripLog"{
	Depth="0";
	Offset="0";
	Rotate="0";
	Zoom="0";
	Track:"Progress"{
		Width="0";
		Layer:"Background"{
			Type="1";
		}
		Layer:"Date"{
			Type="3";
		}
		Layer:"Border"{
			Type="2";
		}
	}
	Track:"Curves"{
		Width="0";
		Layer:"Background"{
			Type="1";
		}
		Layer:"Grid"{
			Type="4";
		}
		Layer:"ROP"{
			Type="5";
		}
		Layer:"TotalGas"{
			Type="5";
		}
		Layer:"Gamma"{
			Type="5";
		}
		Layer:"Border"{
			Type="2";
		}
	}
}

[Brian Chirgwin]

You state an example script might look like this. If it is possible to change the script format to be more consistent.

If there is no reason to store things as XML, then don't, store as a script. Why add the overhead of the conversion to/from XML. Unless the application uses XML as its script language.

I might (based on what I currently know) do the following:

LayoutTable: Name, Script

Store the entire script as a whole and the name to retrieve it.

If you need to search by track or properties, I would create another table containing the required search info. This is similar to storing a large text field (Memo) and creating a table with each word of the memo field to perform full text search.

SearchCriteria:LayoutName (Or ID pointing to LayoutTable), Propname, Value

SearchCritera table can always be completely recreated based on LayoutTable. Creating LayoutTable based on SearchCritera may not be possible.

Example:
LayoutTable: StripLog, <EntireScript>

Search Criteria:
StripLog, Depth, 0
striplog, offset, 0
striplog, rotate, 0
striplog, zoom, 0
striplog, Track, Progress
striplog, Width, 0 <- if you need to know this is part of progress more data is needed. A parent field might do it.
striplog, layer:, Background
striplog, type, 1;
...

SearchCriteria is just used to find what Layout scripts contain matches. i.e.

Untested: Select Layout.script FROM layout Join SeachCriteria ON layout.name = searchcriteria.name WHERE searchcriteria.propname ='depth' and searchcriteria.value = '0';

Anyway, just a thought.

[Chris Holbrook]

various RowID's to link them together ...

Rowids can change as previously noted. They are good to use for direct access to rows in a table rather than firing off another query, but generally excluded from relational design per se. But they can't touch you for it.

[Michael Mattias]

Since there aren't many hierachical databases out there any more (are there any?) , and since SQLLite is relational... I'd probably just go the simple way you thought of...

A. Create [] tables for Lay[outs?] , Tracks and Layers and use the various RowID's to link them together or....

.. except I'd use my own key column instead of ROWID to link 'em, i.e, assign your OWN "ROWID"

[Mark Smit]

Hey guys,

I really appreciate your help but I'm a little lost as to why the RowID (built in) is such a bad column to use?

[Brian Chirgwin]

Hey guys,

I really appreciate your help but I'm a little lost as to why the RowID (built in) is such a bad column to use?

There are several reasons I haven't used them. It's not that there bad, just never worked for me. Guess I like having control when needed.:

In some database engines RowID will change on you if you compact the database or if the highest rowID is deleted, it can be reused (See SQLite note below).

In some dbs RowID is read only. There is no way to set to a specific value. If you were to export / import or merge two databases you would not be able to use the same RowIDs. Of course, you could export the data and related table data in order without ids, but how would you load the data making sure the new RowID assigned is the assigned Foreign key in other tables.

If you have your own column you can at least set the CustomID to an offset during import/merge and include a unique ID in the exported data.

Not all databases have a RowID.

A custom column used as a primary key, doesn't have to be numeric.

From the SQLite Docs

http://www.sqlite.org/autoinc.html

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

In other words, if you have a database with RowIds 1-10,000 and you delete 5000-10,000. The next record inserted will have a RowID of 5,000.