FAQs
 
FAQs
 

Choreography Database ==> All
My own sequence database
I am a square dance caller looking to reinvent the wheel. Sorta.

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?

Our entire web-database is defined at http://www.ceder.net/admin2/webdb.txt

Minimally, for a sequence database, you'll need the following:

CREATE TABLE SequenceDb
  (SequenceText   blob, 
   SequenceType   int,
   RecordId       int AUTO_INCREMENT PRIMARY KEY)

Where
  • 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.
You can then do searches such as

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)

Where
  • 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.
  • CreateDateTime indicates the date and time that the sequence was added to the database.
  • RecordId is a number that uniquely identifes the sequence within the database.
Our database runs under MySQL, a freeware database product. The SQL statements given above should work (perhaps with minor tweaking) in almost any database.

https://www.ceder.net/faqdb/viewsingle.php -- Copyright © 2024 Vic Ceder.  All Rights Reserved.
full URL