data-design question

[Charles Hartman]

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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>