Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Livermore, California
    Posts
    2

    Choosing the right database for animation database

    I am embarking on a project which I have little experience in. I wish to create a database of scientific animations, which will contain entries with variable numbers of key:value pairs. This is because users can invent their own keys and values on the fly and because different animations have different attributes.
    For example, all animations have the key "author", but not all animations have the attribute "simulation type". Also, I expect that movie A might have n keys today and then in a month a few keys will be added as we improve our indexing scheme and people use the metadata more.
    There will be hundreds of animation in the table, not millions. Given the need to be flexible and grow the number of keys, I was considering some sort of json/xml format, but I don't want to get saddled with terrible performance in searching for entries or replacing them. I considered sqlite but I'm afraid I don't know how to structure the table.
    I'd love to hear suggestions as to a good approach. Obviously I am not an expert on this subject.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You are correct, a JSON/XML database would perform well for small amounts of data. Depending on the platform, this could be hundred or even thousands of key/value pairs.

    A simple SQL Schema with appropriate indexes ought to be able to handle 10e8 or more key/value pairs even on a relatively low powered system. This could also offer a lot better user experience too.

    I'm going to assume that you plan to host this on a web site. If that is the case, then I'd recommend using one of the better supported database engines like PostgreSQL or MariaDB. SQL-Lite is a decent file manager, but it does not have the vendor or ISP support that the other products do.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2002
    Location
    Livermore, California
    Posts
    2
    Quote Originally Posted by Pat Phelan View Post
    You are correct, a JSON/XML database would perform well for small amounts of data. Depending on the platform, this could be hundred or even thousands of key/value pairs.

    A simple SQL Schema with appropriate indexes ought to be able to handle 10e8 or more key/value pairs even on a relatively low powered system. This could also offer a lot better user experience too.

    I'm going to assume that you plan to host this on a web site. If that is the case, then I'd recommend using one of the better supported database engines like PostgreSQL or MariaDB. SQL-Lite is a decent file manager, but it does not have the vendor or ISP support that the other products do.

    -PatP
    Hi, thanks for the reply. Due to security restrictions, it is easier for me to simply maintain a database in a central location on our filesystems. Hosting on a website around here is a minor nightmare due to bureaucracy and poor feature support. Therefore, I think I'm leaning towards JSON, as it is integrated into python, my language of choice for this kind of thing, and is very flexible. How would I handle the changing number of keys in MySQL or even SQLite tables? They seem to be rigidly structured and not well suited for sparse key/value array tables from what I can see as a newbie.
    Thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd create a table for animations, a table for key names, and a table that associated an animation with a key name (which would also contain the key value). Each animation would have one entry in the animation table. Each key name (like author) would have an entry in the keys table. The animation-key table would provide the key value and links to the animation and the key name.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •