Hi Charles,
Thanks for the SQLite pointer, in any case. But $149 for the plugin
is not what I would call "inexpensive."
You can use the concepts of relational databases without using mySQL.
That is you use some textfields to be the equivalent of a mySQL
database.
Assume that the basic record (in the database sense) is Album -- so
the basic design is one card per Album. Each Album includes a list
of Tunes (besides Artist/Group and Label/Date/EtcData). Each Tune
is associated with one or more Writers, and also with a list of
Players, each of whom is associated with an Instrument. So we've
got at least four fundamental types of data lists -- player,
instrument, tune-title, writer -- and some items that combine
fundamental items in many-to-one relation.
The rule for relational db is that any information that can occur
more than once within a column should be encoded by an ID, with a
second database being created that holds information about (space
consuming) details for this ID. At least one column should have a
unique ID (usually the first column in the db)
This should give something like this (draft, not guaranteed to be
exact).
DB 1: Album composition
TuneID AlbumID TuneDetails
(as Album ID is used, it is ok if a same tune can occur in two
different albums)
----------------------
Tune1 Album1 DetailsT1 (over multiple columns)
Tune2 Album1 DetailsT2
Tune3 Album2 DetailsT3
DB 2: Album Information
AlbumID GroupID Label Date
----------------------
Album1 Group1 Label Date Etc
DB 3: Groups ID
GroupID MembersID website
----------------------
Group1 Person1 URL1
DB4: Persons details
(this is assuming a same person cannot be player and writer...
otherwise Role needs to be replaced with RoleID and a new db created
to hold details about roles)
PersonID Role FirstName LastName Details (dob, url, etc.)
----------------------
Person1 Player John Doe xxx
DB5 - Tune's writer
TuneID WriterID
----------------------
Tune1 Person2
Tune2 Person3
Tune2 Person4
DB6 - Tune's player
TuneID PlayerID
----------------------
Tune1 Person1
Tune2 Person14
Tune2 Person36
Any suggestions about the best approach to the internals of this?
I'm not clear whether, for example, custom properties are up to the
demands of what's essentially a relational database . . .
Text fields are probably easier to manipulate than custom properties
(at least during the design stage, when you want to debug your data
handling routines). You can very rapidly sort a text field on a given
column, which means you have easily at hand the list of all writers
of a given tune, ore all the tunes written by a given writer.
You can have a look at tree_view (http://revolution.lexicall.org/
stacks_education.php) for a (simplified) example of handling a
relational database structure with textfields. I updated it today to
fix some minor bugs. You have there sorting functions and functions
to find the corresponding entries in the related databases.
People here have convinced me not to write a whole relational
database manager from scratch (that didn't take much).
If you don't have that many records (less than 1000 tunes) and you
don't attempt to create a completely generic solution (i.e. an
application that can manage any set of interelated databases), you
should be able to handle this with textfields and this shouldn't take
more than a week to program (I mean for a version that works for you;
not one of professional standards).
Marielle
_______________________________________________
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
