I've got a lot of choreo that I'd like to put into a database and make searchable. Some of it, like singing calls, I plan on putting on your web site and sharing with the world. Other sequences I want to keep for myself.
Anyway, what I'd like to do is make it searchable, but my knowledge of databases is limited. (I am working on it little by little.) What I am wondering is how you have your tables set up. I want to get started in the right direction so that I don't have to redo much (if any) work. Do you have a fixed size for each sequence, or do you relate each entry back to a sequence key?
Minimally, for a sequence database, you'll need the following:
CREATE TABLE SequenceDb (SequenceText blob, SequenceType int, RecordId int AUTO_INCREMENT PRIMARY KEY)
SequenceText
is the sequence itself. Blob means that the field contains as many characters as you'd like.
SequenceType
can be used to hold some information about the type of the sequence.
RecordId
is a number that uniquely identifies the
sequences.
SELECT * FROM SequenceDb WHERE SequenceText LIKE '%Ping Pong%'
My sequence database is currently defined as:
CREATE TABLE ChoreoDb (CheckedByVdc int, LevelValidated int, // 0 = false, 1 = true SeqTypeInd int, Text blob, SubmittedBy int, // Index into 'callerdb' where 0 = anonymous LevelInd int, Comments blob, HandleList varchar(255), // stored with a comma on both ends (e.g., ",9,122,2124,") DifficultyInd int, ModDateTime datetime, CreateDateTime datetime, RecordId int AUTO_INCREMENT PRIMARY KEY)
CheckedByVdc
indicates that the record has been proofread.
(i.e., I have verified that the sequence works).
LevelValidated
indicates that I have verified the level of the sequence.
SeqTypeInd
indicates the type of the sequence. (e.g., Zero-Line to Resolve, etc.)
Text
is the sequence text.
SubmittedBy
indicates who submitted the sequence to the database.
LevelInd
is the level of the sequence.
Comments
is additional text associated with the sequence.
HandleList
is a list of parse handles used by CSDS, so I can do database searches for calls and concepts without using text.
DifficultyInd
indicates the difficulty of the sequence.
ModDateTime
indicates the date and time that the sequence was last modified.