Home of the Squeezebox™ & Transporter® network music players.
Page 14 of 14 FirstFirst ... 4121314
Results 131 to 137 of 137

Thread: New Schema

  1. #131
    Senior Member gharris999's Avatar
    Join Date
    Apr 2005
    Location
    Santa Fe, NM
    Posts
    3,509
    For those of us who aren't such db wonks: http://en.wikipedia.org/wiki/Entity-...te-value_model

    In particular, quoting from the "downsides" section:

    Inefficient queries. Where you would execute a simple query returning 20 columns from a single table, you end up with 20 self-joins, one for each column. It makes for illegible code and dreadful performance as volumes grow (scalability is very bad). This downside can be mitigated by use of any PIVOT extensions in a database's query language or through the use of complex expressions—one per "column"—that allow the table to be joined to only once by ignoring the values seen for columns the expression is not targeted for.

    I certainly don't have the db coding chops necessary to adequately asses the validity of those criticisms. But can we agree that the minimum test db for Brenden ought to be >50k tracks? Otherwise, I'm afraid that those of us with large-ish libraries will end up with a boat anchor.

  2. #132
    Junior Member cmcneil's Avatar
    Join Date
    Feb 2006
    Location
    USA
    Posts
    18
    Quote Originally Posted by gharris999 View Post
    For those of us who aren't such db wonks: http://en.wikipedia.org/wiki/Entity-...te-value_model

    In particular, quoting from the "downsides" section:

    Inefficient queries. Where you would execute a simple query returning 20 columns from a single table, you end up with 20 self-joins, one for each column. It makes for illegible code and dreadful performance as volumes grow (scalability is very bad). This downside can be mitigated by use of any PIVOT extensions in a database's query language or through the use of complex expressions—one per "column"—that allow the table to be joined to only once by ignoring the values seen for columns the expression is not targeted for.

    I certainly don't have the db coding chops necessary to adequately asses the validity of those criticisms. But can we agree that the minimum test db for Brenden ought to be >50k tracks? Otherwise, I'm afraid that those of us with large-ish libraries will end up with a boat anchor.
    I do

    Hey I could be WAY off base... maybe they aren't going that way at all. As I said I didn't read the whole thread, it seemed to be a discussion of the benefit of surrogate keys for the non-technical... that's a religious question anyway

    This is just my inference from the notes posted, so take it with a large grain of salt, more a heads up than a criticism... from a big BIG Squeezebox fan.

    I read the wikipedia pointer you posted and I agree with most of the criticism of the EAV model - which every smart db architect "invents" at some point in their career by the way - but the real problem that I could see looming down that road is scalability, especially if there are any future plans for running squeezecenter on an embedded appliance type device.

    The challenges of actually implementing the design... on sqlite no less... well I'll leave that to the guys at Squeeze who I'm sure will test their solution rigorously. The part that drives me nuts is that people are always looking for a solution to relational databases. Well guess what - RDBs ARE the solution to the problem of managing large volumes of data, not a problem in search of a solution.

    Well I'm going to ring off of this now and leave it to the folks who have brought us all a truly great music streaming system... keep up the great work Squeeze team.

    But do look into the literature on the EAV db model (if you haven't already) before you commit to it if that is indeed where you're headed.
    Regards,
    cmcneil

  3. #133
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,039
    Quote Originally Posted by cmcneil View Post
    From a quick gander at the notes, and without reading this very long thread, this sounds suspiciously like the dreaded Entity Attribute Value model - a well known db design anti-pattern. I sure hope that's not where you're headed with this
    I don't think you have to worry, if I have understood it correctly the plan is to create a description file with the entities/attributes you like to handle and then generate a specific database structure based on that description. This should be completely different compared to the EAV model which is designed to make it possible to put anything in a single generic database structure.

    Of course I might be completely wrong since we haven't seen any details yet, but based on the answers we got earlier in the thread I'm pretty sure they aren't targeting a EAV design.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets

  4. #134
    Senior Member
    Join Date
    Mar 2008
    Location
    Calgary, AB
    Posts
    723
    Quote Originally Posted by gharris999 View Post
    For those of us who aren't such db wonks: http://en.wikipedia.org/wiki/Entity-...te-value_model

    In particular, quoting from the "downsides" section:

    Inefficient queries. Where you would execute a simple query returning 20 columns from a single table, you end up with 20 self-joins, one for each column. It makes for illegible code and dreadful performance as volumes grow (scalability is very bad). This downside can be mitigated by use of any PIVOT extensions in a database's query language or through the use of complex expressions—one per "column"—that allow the table to be joined to only once by ignoring the values seen for columns the expression is not targeted for.

    I certainly don't have the db coding chops necessary to adequately asses the validity of those criticisms. But can we agree that the minimum test db for Brenden ought to be >50k tracks? Otherwise, I'm afraid that those of us with large-ish libraries will end up with a boat anchor.
    I say > 100,000, I'm already approaching 70,000 and will be approaching 100,000 before the end of the year.

  5. #135
    egd
    Guest
    Quote Originally Posted by Jeff Flowerday View Post
    I say > 100,000, I'm already approaching 70,000 and will be approaching 100,000 before the end of the year.
    Agreed, I'm already over the 100k mark and with Erland's plugins the tables are massive.

  6. #136
    egd
    Guest

    Can someone shed some light?

    I'm in the process of adding a few tables to the SC db which I would like to make persistent. In order to achieve this and integrate with SC I need to find a common link. The obvious choice (unfortunately) is the actual track name and path string, but before I go down this path, has/ is the new schema likely to settle on a unique identifier (with persistence) of some sort? Are there any details of the revised schema available?

  7. #137
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,039
    Quote Originally Posted by egd View Post
    I'm in the process of adding a few tables to the SC db which I would like to make persistent. In order to achieve this and integrate with SC I need to find a common link. The obvious choice (unfortunately) is the actual track name and path string, but before I go down this path, has/ is the new schema likely to settle on a unique identifier (with persistence) of some sort? Are there any details of the revised schema available?
    If you need anything soon, I would built upon what's available. In TrackStat I use tracks.url and optionally tracks.musicbrainz_id if available. SqueezeCenter also uses this internally for its tracks_persistent table. Musicbrainz ID's are good since they make it possible to handle renaming and restructuring of the music files. You can't rely on only Musicbrains ID's since most users don't use them. If you use Musicbrainz ID you probably also want to perform a synchronization operation after a SqueezeCenter rescan to refresh the track urls in your tables.

    The last information I've seen indicates that the next major SqueezeCenter version will be released early summer, there is no way the new schema will be introduced into this version since it hasn't even been released for alpha testing yet.

    I would personally be surprised if we will see the new schema in an official release this year, my guess would be sometime next year. However, it has been strangely quiet in this area, so I'm personally not sure if the plans for a new schema is still alive. It would be nice to get some kind of indication from Logitech what's going on regarding the new schema and when they plan to release some more details regarding it.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets

Posting Permissions

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