data-design question

[Marielle Lange]

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