What about defining a table called 'properties'. It would have a key
to link to the object and 'name' and 'value' column for each object
property. You could have as many properties as desired for each object
and they need not be the same for each object.
That could be a solution, but at the upper bounds I'm expecting around
30,000 to 60,000 objects which each may have between 50 and a 100
properties.
At the worst that would be around 6 million columns on a property table.
I've not used SQLite with tables that large before
so I'm not sure if searching with 2 index values (name, and object its on)
is going to be slow.
I do wonder the same thing as another poster. Is a database really the
tool you want
to be using for this? I can't imagine what you really need with a
database for that
application.
Well, the main reasons I want to use a database as opposed to some other
type of arbitrary formatted file is real time access.
In the sense that I won't have to read the entire file into memory and can
just request objects as I need them and keep them in memory,
until a certain amount of time passes without them being used in which I can
free their memory, and if someone wants that object again it will
just be pulled out of the database. Commit/rollback functionality is also a
plus.
--michael
