9 {{ upvoteCount | shortNum }}
9 {{ upvoteCount | shortNum }}

Best Way To Store This Data in Database

by cmaclean
7 replies
Hi guys,

I do a weekly call and I have a collection of all my recordings I post to an archive page. Right now this content is not stored in a database but I would like to transition it to one. Here is what the data and formatting appear like for each recording:


2015-04-15 (recording date)

Play-By-Play:
00:00 - - Welcome to the call
01:20 - - Topic One
08:54 - - Topic Two
Call Length: 21:00
etc...

Download Audio File (direct download link)


Before proceeding, I'm curious of the best way to store the "Play-By-Play" section. The HTML looks like this:



Should I just store that section WITH the HTML tags in a table? Or is there a more appropriate method?

Thanks!
#data #database #store
  • Profile picture of the author Mbolak
    Generally storing HTML within the database is an archaic practice. It would be more appropriate to store them in a key value format in one row and then call the specific columns within the HTML using whatever templating language you prefer. So here's an example schema I've created:

    A Pen by Captain Anonymous

    That's how I would format the schema for a relational database BUT if it were my data I would definitely use a NoSQL database like mongodb.

    That way I could add any type of data to each object that I want. I doubt all calls use the same type of formatting.

    Just always remember that your database should only contain data. Try not to put code in there as it's considered sloppy and bad practice.
    {{ DiscussionBoard.errors[10005088].message }}
  • Profile picture of the author Mbolak
    Additionally, you would probably want a key value pair that stores the URL of each audio file.
    {{ DiscussionBoard.errors[10005090].message }}
  • Profile picture of the author David Beroff
    You're describing two tables here, Recording and Event.

    The Recording table contains data about each recording, such as:
    • RecordingID
    • Recorded
    • Duration
    • Filename
    Each Recording has zero or more Events, and the Event table contains data about each event, such as:
    • EventID
    • RecordingID
    • Timestamp
    • Topic
    Notice that each Event relates to a specific Recording with a RecordingID, in a many-to-one relationship: zero or more Events for each one Recording.

    So, for the example you give, one row in the Recording table would contain, for example,
    (1357, 2015-04-15, 21:00, Call001357.mp3),
    and three rows of the Events table would have,
    (4321, 1357, 00:00, "Welcome to the call"),
    (4322, 1357, 04:20, "Topic One"), and
    (4323, 1357, 08:54, "Topic Two").
    The three Events relate to the one Recording using the RecordingID 1357.
    Signature
    Put MY voice on YOUR video: AwesomeAmericanAudio.com
    {{ DiscussionBoard.errors[10005649].message }}
  • Profile picture of the author cmaclean
    Thanks for the input guys, it's very helpful. David, I think your solution is perfect for my situation.
    {{ DiscussionBoard.errors[10006558].message }}
  • Profile picture of the author cmaclean
    Any thoughts on how I would handle an anchor link in one of the events?
    {{ DiscussionBoard.errors[10006626].message }}
  • Profile picture of the author David Beroff
    Just store it as http... in the text, and then your front-end software would look for that to convert it to [a href="http..."].
    Signature
    Put MY voice on YOUR video: AwesomeAmericanAudio.com
    {{ DiscussionBoard.errors[10006633].message }}
    • Profile picture of the author cmaclean
      Originally Posted by David Beroff View Post

      Just store it as http... in the text, and then your front-end software would look for that to convert it to [a href="http..."].
      Great, thanks!
      {{ DiscussionBoard.errors[10006760].message }}

Trending Topics