Marielle,
That's very helpful -- thanks. This thread has gotten me looking at
MySQL, and I think I'll keep pursuing that, though I believe you that
I don't absolutely need it for this project. (And who knows, I may
manage to populate the thing with more than 1000 tunes . . . in
several years.)
But in any case, the conceptual overview you give is helpful, even as
a way to think about how MySQL would do it, and how tables in a MySQL
database should be structured.
Charles
On Sep 27, 2005, at 6:33 PM, Marielle Lange wrote:
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
_______________________________________________
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
