Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[hit_ny]

You mean we're not discussing "Why I want MC to be a relational database."?

I've come to appreciate the value of a true relational database for managing movies and their relation to people. I imagine some music lovers have the same interest, and they probably find a relational database (and probably just using like AllMusic) is much more effective in providing biographical information for artists and relating them to their works.

Relational comes up once a year, every year for the last few years.

Since you've used a relational dB, can you tell us what it has that is so appealing ?

There has been one attempt that goes in that direction but its not exactly the same thing tho kinda acts in a similar manner. It called stacks. Set one value and all the others in the stack get it also.

Maybe some toggle could be introduced at the field definition level that says global (or singleton or something better) implying just one value will apply to all items.

Would this bring us closer to this relational place you so crave ?

[rick.ca]

Would this bring us closer to this relational place you so crave ?

I'm already there. As I explained, I use an application built on a relational database to manage my video information. That works for me because I value the handling of the relations between people and the videos they are associated with. Not surprisingly, a relational database works particularly well at handling such relations. There are different kinds of relations, of course, and database applications don't have to be relational to handle them well.

MC is a particularly good example of this. Most of its relations are "flat", and the key ones that are not are probably handled in a relational manner under the hood. I'm quite happy with MC, recognizing it's just not intended to so some things. Considering how well it does what it does, I think it might be a mistake to push it too far to do things it wasn't intended to do. Even if the developers can figure out how to do so without hindering its performance and reliability, there would still be a risk of it become to complicated for users to understand. It already makes my brain hurt!

I don't understand the technical issues, but I suspect the nature of the data has some bearing on which database architecture is most effective. Compared to a typical MC database containing music, images and videos, a typical movie database will have a relatively small number of movies, a much larger number of people, a lot more information per item, and a huge number of movies stemming from the relations between the primary records (i.e., the movies making up the filmographies of the people related to the primary movies). The way the user interacts with the information tends to be different. MC's grid view is clearly the most suitable for music data—where there is a large number of records (tracks) and a relatively small, clearly defined set of data recorded for each. In my movie database, I'm presented with a simple filtered list, and all the information for just one movie at a time.

I suppose stacks are a way of dealing with a simple one-to-many relation, but that doesn't help much for handling other relations. To record something using stacks, I have to create a stack. If I can do that, I can almost as easily record the data in all the records. I doubt it makes any difference to how the data is actually recorded in the database. I trust that MC is smart enough to recognize it's one piece of data that should be displayed as if it is in many records, regardless of how I enter it. But if I want to record a bio of an actor, what am I to do? Do a search to find all the movies he is in, turn them into a stack, and record the bio. That's not going to work very well. I have to accept the database isn't designed for maintaining that sort of information.

[darichman]

Haha... That title gives me the uncanny feeling that the end product of this thread will be printed out, cut into small pieces and used as confetti in the J River office

Here's something I prepared earlier:

Darichman,
You've mentioned a relational database in several threads.
It sounds like what you're looking for is a way to say "Keep this field the same for all files in the same album or artist" for any field.
Note that there's no efficiency to be gained from this as Media Center already pools similar data.  It would only be an ease-of-use enhancement.
Is there more to it than that? Thanks.

....The benefits I'm suggesting are functional, rather than efficiency-related
What I was hoping for was a way to have a field (let's say [Artist]) as its own entity in the database. This artist could be taggable just as any file is now in the current database implementation. One of the functional consequences of this is, as you mentioned, that a subordinate field would be the same for all files belonging the particular parent field. But there is a lot more to it than this. I think the best way to describe this is with a few examples.

Example 1 - Inheritance (as you mentioned)
I have 20 files by Abba.
The [Bio] field should be the same for all of the 20 files.
In the current database, while the info is only stored once, it must be re-entered for any future files I add by Abba
If [Bio] is set up as a subordinate field of [artist] any files added for a particular artist would inherit the bio of the other fields (and thus would never have to entered again or copied from another file)

Other examples of inheritance of files might include Bio, Year Formed, Years Active, Nationality, Primary Genre, Members etc for [Artist] --this sort of info would look really cool in an "Artist Summary" page
For albums: Album Year, Album Artist, Album Type, Review (same goes for an "Album Summary" view)
Such an approach might be particularly useful for all the movie fields as well...

Example 2 - Specificity
Some fields at the moment are ambiguous, as it is unclear what exactly they are referring to.
For a compilation album or a "Best of" album, what does the field [Year] mean?
Is it the year the song was released? Or is it the year the album was released?
It would be fabulous to be able to Tag an [Album] with a year, separate to the standard year field for the individual files.

It gets even more complicated with classical music, where multiple dates are important
As an example, Johann Sebastian Bach: Prelude & Fugue No. 8 in D♯ Minor from the Well-Tempered Clavier Book II album
Let's look at the [Year] field again...
Is it the year the song was written? The year it was recorded? The year the album was released?

Example 3 - Discrimination
Here we are talking about matching entries in a field A with entries in a Field B... there is no way to do this currently
I have a list field [People] which may contain multiple entries
Let's say a file is tagged for Fred;Mary;Sally
Fred is my brother, and Mary and Sally are friends -- but there is no at the moment to tag "Fred" as "Family" or the others as "Friends"
If I create a field called [Association], the photo will appear under "Family" and "Friends" (this is as expected)...
...but "Fred" will appear under "Friends" and Mary and Sally will appear under "Family", when they're not... just because the photo has OTHER people belonging to those entries
I hope that makes sense!

Nested keywords has made something like this possible.. but there are advantaged to doing it with a separate field:

If I want to create another custom field [Birth Date] for a person, there is no way to tag this information in the current database
Sure I can create a [Birth Date] field and enter a value, but which person does it apply to? Because the file will have three people entries, there is no way to match up the correct [Person] with the correct [Birth Date] or any other field which may be used to describe [People]

It would mean users could store an Artist and Album and Movie database (among other fields) as well as a "File" database...
Once subordinate fields are created for parent fields, and populated, users need only "Add" a file to that group, and any relevant data is inherited.

[darichman]

Also from another thread:

1. Setting Up fields to be relational
2. Databse handling of relational fields
3. Implementation into current Tag AW
4. Implementation into view schemes
5. Implementing online submission and lookup

Here are some ideas I had.

1. Setting up parent/child fields
We need a way to tell MC that we want one field to be subordinate to another. Eg. We want [Nationality] as a field to describe [Artist]. Or we want [Association] as a field to describe [People].

Option 1: In the create new field / edit field dialogue, have a tick box for “Make Child Field of…” and then select the field you wish to link it to. Eg. Create a field called [Nationality] and make it a child field of [Artist]. Each child field should only be linked to one parent field.

Option 2: In the create new field / edit field dialogue, have a button saying “Add Child Fields”. Eg. Click on edit field for [Artist] and add a child field called [Nationality]. This is essentially the reverse of option 1. Ideally we could do it either way and the end result would be the same. We’d need the ability to add more than one child field.

2. Database handling of relational fields.
Relational fields should be available as per regular fields in view schemes, panes, columns, the tagging window etc, with a few minor differences as described below.

Editing child fields will be reflected in any files belonging to the parent field.

3. Implementation Into Tagging AW
Parent fields should display as per the current implementation, but subordinate fields need to appear under the parent field. They can be viewed or hidden with a drop down to the left of the parent field (just like tree entries). We should be able to treat child fields as regular fields in terms of selecting which ones to show in the Tag AW and in terms of editing them. The only difference is how they are viewed in the Tag AW (as subordinates of parent fields, to denote the fact they are describing another field, as opposed to the file itself.

Here is an example of what the Tag AW might look like

>   Artist: AC/DC
            Bio: … (extended field)
            Nationality: Australian
            Members (a list field): Bon Scott; Malcolm Young
            Year Formed: 1973
>   Album: Stiff Upper Lip
            Album Genre: Pop/Rock
            Album Year: 2000
            Record:
>   Rest of the fields etc etc

4. Implementation Into View Schemes
Child fields should be available to use as panes in view schemes, filters etc just like any other field. For example, I could create a scheme [Nationality] -> [Artist] to navigate. If we are viewing “artists” in a view scheme, it might be nice to see a summary of any child fields pop up if I hover over it. Double clicking on an artist will drill down into the next pane in the view scheme. Right clicking might bring up options like “Play all files” , “Show albums by this artist”, “Show artist info” etc etc. Tagging would bring up the Tagging AW as described above. If I try to retag a child field (eg using column view) it might be wise to bring up a popup “Are you sure you wish to change the Artist: AC/DC” or something similar, so that the user knows they are changing all references to AC/DC, and not just the file in question.

It would also be really great to right click on an artist (or whatever field we’re talking about) and say “add coverart”. This artist coverart could replace the moving slideshow of album images when in artist mode. I would personally find this really useful for tagging different [People] with portraits… these could then be used as a visual way of navigating through people by their faces, not a random slideshow of photos they appear in.

5. Implementing online submission and lookup
This is probably something that would come much later, but wouldn’t it be great if we could submit artists, or movies, or TV shows or books etc as we currently do albums? An online MC database like this with lookup and submission would be incredibly powerful and would be a major selling point of MC (at least in my humble opinion). We’d be submitting child fields for each of the major categories described above.

I rip a dvd (which I own!!!) and convert the movie to divx or something. I enter fields like [Director], [Composer], [Studio], [Actors] etc (which I’ve set up as child fields of [Movie]) and upload them. Then if another user has the same movie… he need only match it by title and can download the above info!

A database like this could grow really quickly… and doesn’t rely on recognising the exact same DVD or audio album. We just need to compare a field like [Artist] or [Movie] and the information is accessible. A “closest match” option might be useful!

[hit_ny]

I think it might be a mistake to push it too far to do things it wasn't intended to do. Even if the developers can figure out how to do so without hindering its performance and reliability, there would still be a risk of it become to complicated for users to understand. It already makes my brain hurt!

This question came up a yr or two ago when it became apparent JRiver wanted to go from "plays it all" to "manages it all".

The way the user interacts with the information tends to be different. MC's grid view is clearly the most suitable for music data—where there is a large number of records (tracks) and a relatively small, clearly defined set of data recorded for each.

a typical movie database will have a relatively small number of movies, a much larger number of people, a lot more information per item, and a huge number of movies stemming from the relations between the primary records (i.e., the movies making up the filmographies of the people related to the primary movies).

The way the user interacts with the information tends to be different. In my movie database, I'm presented with a simple filtered list, and all the information for just one movie at a time.

Yeah, movies are unique this way compared to music & images.

Looking back at the last year, they have busy in the video side of things, perhaps JRiver is serious about handling more than just music videos, in the future.

But if I want to record a bio of an actor, what am I to do? Do a search to find all the movies he is in, turn them into a stack, and record the bio. That's not going to work very well. I have to accept the database isn't designed for maintaining that sort of information.

Certainly not, was only observing the kind of pseudo-relational behaviour you want is already present in a rough form. More work needs to be done for it to work the way you want.

[rick.ca]

...More work needs to be done for it to work the way you want.

Here are some ideas I had...

I must confess probably didn't fully understand these ideas when I first read them. Now I see the "child field" concept would be an effective way (from a UI point-of-view) to handling relational fields within the existing framework. By that, I mean it would add the capability without "getting in the way" where it's not used. It seems intuitive, easy to understand, and rather than making things too complicated, might even make some aspects of MC easier to understand. I like it!

But I wonder if it's feasible to add such a capability without an undue hit in performance. Maybe it's the sort of thing that would only impact negatively on performance in proportion to the extent it is used. If so, users with 100,000's of items in their database might curtail their use of child fields, while those of us with 10,000's items might not need to care.

But I want the ability efficiently import my movie information and display it in Theater View first!

[darichman]

But I wonder if it's feasible to add such a capability without an undue hit in performance. Maybe it's the sort of thing that would only impact negatively on performance in proportion to the extent it is used.

This I honestly can't claim any knowledge about. Theoretically, I'm not sure the "Inheritance" idea described above should impair performance, navigationally at least. It may affect editing of fields involved (as multiple records need to be updated - eg Artist and all the files belonging to that artist).

It's a clear case of risk, benefit and investment, to be sure. The risks are speed and, potentially, ease of use... although such a system might make many areas of the program more intuitive. There are certainly benefits to be gained, however... Movie sites like IMDb/allmovies and music sites like allmusic are very popular and use relational systems emphasising Artists, Works, People etc as discrete entries just as important (if not more so) as files/tracks/movies.

[hit_ny]

darichman,

your post is very good, now need to consider the 2nd order effects and i'm not referring to speed.

[Listener]

Also from another thread:

1. Setting Up fields to be relational
2. Databse handling of relational fields
3. Implementation into current Tag AW
4. Implementation into view schemes
5. Implementing online submission and lookup

Here are some ideas I had.

I understand the value of a fully relational database in a music player application.  I understand how I would employ a relational database in this application.  In particular, having separate tables for composers, artists and works would be elegant.

However, MC works very well now.  It has been tuned for years; response is instantaneous when I select something in the UI.  It handles very large collections without breaking or getting slow.  I don't want to lose the virtues we have to get a much less polished system with relational database features.

Many of the suggestions Darichman and others make are quite specific The argument is made that if JRiver just stretches the current system in a few ways, the poster would be happy.  Each poster has his own pet list.  Most posters seem to have a limited understanding of relational databases at a programmer level.  It all sounds like a recipe for disaster to me.

Bill

[gappie]

i dont know if it will be a disaster, but i think it will be overkill, and we will loose flexebility.. besides that by far most of the users wont notice the difference. so i agree with listener..

 
gab

[newsposter]

There are some extremely fast, very small, and very inexpensive rdbms engines that I've provided pointers to in the recent past.  Going from flat files to a (potentially) rdbms would actually add to the simplicity of MC because the dB engine would handle the meta-data management in a standard way.  JR could then really, really concentrate on UI, codec, and playback issues.  A full-featured rdbms would add things like redundancy and recovery to the metadata that we do not have now.

[rjm]

For whatever it is worth...

ThumbsPlus uses an Access relational database. When I select the view that displays all 15,000 of my images it takes 16 seconds to render the thumbnails.

When I select the equivalent view in MC, it takes 1 second to render.

[rick.ca]

The argument is made that if JRiver just stretches the current system in a few ways, the poster would be happy.

One might infer that from the fact someone goes to the trouble to make a suggestion. That doesn't mean the intent is anything other than to help make the program better. I think most people who offer ideas here understand that might even happen in the rejection of their idea.

It all sounds like a recipe for disaster to me.

How would this "disaster" happen? Do you have so little confidence in the development team that you believe they might implement an idea which has an overall negative impact on the program?

it will be overkill, and we will loose flexebility.. besides that by far most of the users wont notice the difference

I'm sorry, but none of these statements are justified based on the ideas presented. Perhaps you mean you don't want these additional features/improvements, you fear they would be implemented poorly—so as to reduce, instead of increase flexibility—and you're sure everyone else agrees with you.

As the title of this topic suggests, many here consider any discussion about relational database concepts to be a joke and a waste of time. There's only one circumstance I can think of where that might be true. That would be if the developers had already explained something like, "It is impossible to implement anything remotely resembling this without a complete change to the architecture of the database and a rewrite of the program. That is never going to happen." That, surely, would go a long way to shutting the door on an obviously pointless discussion. I don't believe they've said that, so I assume these ideas are just as valid and worthwhile as any others. While we may never see them implemented outright, I trust they are nevertheless useful feedback to the developers.

[JimH]

As the title of this topic suggests, many here consider any discussion about relational database concepts to be a joke and a waste of time.

If you can suggest another title, I can change it.

[rick.ca]

Thanks, Jim, but I think you can tell it's not the tongue-in-cheek title that bothers me...   

...

[JimH]

The argument is made that if JRiver just stretches the current system in a few ways, the poster would be happy.  Each poster has his own pet list.  Most posters seem to have a limited understanding of relational databases at a programmer level.  It all sounds like a recipe for disaster to me.

I've been trying to listen to the dialog, but I must say ... I always enjoy seeing Listener bring the discussion back to earth.  Thanks, Bill.

[JimH]

Thanks, Jim, but I think you can tell it's not the tongue-in-cheek title that bothers me...   

I think the discussion has been very scholarly, and I mean that in a good way.

[MrHaugen]

Think you have some excellent suggestions there Darichman.
I think a good implementation for relation database and the use of a big online database would only bring joy.

I do belive that a way of showing this data correctly is more important to do first though. Especialy for theater view.

[newsposter]

Oh, everyone needs to FORGET about their experiences with Access, MySQL, Oracle Personal, etc, etc.  Those engines are way too big and complex (which in turn tends to result in slow and prone to error) for what MC might need.

Think more about rdbms engines that are purpose-designed to be embedded in 'appliance' hardware and applications.  These little gems 'weigh in' at around 500kb in size and can easily run transactions at the rate of many thousands per second on pedestrian hardware.  One or five of them are even able to hold graphics data (think cover art) as database objects.

Expand your minds............

[CadErik]

I understand the value of a fully relational database in a music player application.  I understand how I would employ a relational database in this application.  In particular, having separate tables for composers, artists and works would be elegant.

However, MC works very well now.  It has been tuned for years; response is instantaneous when I select something in the UI.  It handles very large collections without breaking or getting slow.  I don't want to lose the virtues we have to get a much less polished system with relational database features.

I can't agree more! Not to mention QUERIES. I don't want to be writing complex SQL joins to find my music and videos.
MC has a fantastic tool to write complex playlists which are sort of queries.
In addition, I believe they fine tuned their backend for years and it is extremely fast with frequent improvements. Not only they handle metadata (which would be easy to put in a rdbms) but also thumbnails and probably more. That kind of data would also take a while to store in a RDBMS and tune to the current speed.

And there is no such thing as RDBMS "features". I believe the original poster made comments about the way the data is organized. It might make sense that the data would be organized that way but I'm not sure that adding tables, SQL, queries is the solution. And reorganizing RDBMS data is not an easy task.

The more I use RDBMS the less I think they are appropriate for many common tasks. One has to remember that the core of the underlying design (of all RDBMS) is to provide ACIDity, and unless you are building a financial application, I am not sure these are necessary properties.

Erik.

[Listener]

How would this "disaster" happen? Do you have so little confidence in the development team that you believe they might implement an idea which has an overall negative impact on the program?

Fred Brooks wrote a famous book (titled "The Mythical Man-Month" about managing the development of IBM's OS/360 operating system.  One chapter was about the "Second  System Effect" where everyone inserts their favorite ideas that didn't make it in previous systems.  The result is a bloated system that takes far too long to finish and then takes years to get to real usability.

Both Data General and HP suffered these problems with their second generation mini-computer systems.  Neither system was usable until new hardware with faster processors and more memory became available.  Rolm's second generation PBX software never achieved feature parity with their original architecture.  SlimDevices converted from in-memory Perl data structures to using an external relational database  some time ago; Slimserver took quite a while to become stable and usable.

Replacing a finely-tuned system with an entirely new more elegant architecture is very seductive but it has caused a lot of grief.

I'm sorry, but none of these statements are justified based on the ideas presented. Perhaps you mean you don't want these additional features/improvements, you fear they would be implemented poorly—so as to reduce, instead of increase flexibility—and you're sure everyone else agrees with you.

As the title of this topic suggests, many here consider any discussion about relational database concepts to be a joke and a waste of time. There's only one circumstance I can think of where that might be true. That would be if the developers had already explained something like, "It is impossible to implement anything remotely resembling this without a complete change to the architecture of the database and a rewrite of the program. That is never going to happen." That, surely, would go a long way to shutting the door on an obviously pointless discussion. I don't believe they've said that, so I assume these ideas are just as valid and worthwhile as any others. While we may never see them implemented outright, I trust they are nevertheless useful feedback to the developers.

It would be much more useful for users to describe what they want to do with MC that they can't do now.  Leave the decisions on implementation to JRiver.

> These little gems 'weigh in' at around 500kb in size and can easily run transactions at the rate of many thousands per
> second on pedestrian hardware.

You are comparing apples to oranges.

MC manages to react to UI input so quickly that there is no sense of delay.  It keeps up with mouse clicks and keystrokes so that I am not brought up short.  Good personal computer applications have behaved that way for years.  Web applications that retrieve data from a server rarely have that instantaneous delay.  Delivering  consistent < 0.2 sec response to every mouse click for a single user is a different requirment from executing many transactions with an average but varying 1-2 sec. response time.

Modern relational databases cache lots of data in RAM.  That RAM cache is often larger than the actual code size of the database s/w.  However, its use is not tailored to the MC application.  JRiver is also caching data in RAM.  It is possible that many of the improvements in response time  in MC have come from using more Ram to cache data and using it better.  That use might be more effective than the Ram cache in a relational database.

Bill

[tunetyme]

I have watched this discussion pop up periodically and I would like to put my 2 cents in.  I spent a significant amount of time reviewing a host of different audio players (I use MC exclusively for audio at this time).  I chose MC for the database and the flexibility given the user to add fields.  I have not found a competitive product yet.  There are some things that I would like to be able to do, or figure out how to do them.  Since the offer is out there to solve users needs I would like to take you up on it.

For example, for some of my friend’s birthdays, I have dug up the top 20 songs that were playing on their birthday when they turned 16, 18, 21 or when they got married or other memorable events during the life.  Everyone loves it, its nostalgia on steroids.  I would love to be able to store the top 20 for every month since 55’.  The problem becomes how do I store and search a song that may have been in the top 20 for 2, 5, or 10 months.  There are few that made the top twenty multiple times and years apart.  In a relational database this would be a simple problem to solve.  Link the song with the date and add as many dates for that song as needed.  How do I add a list of songs that I do not have in my database to find and buy?

How can I do it with MC's current database? 

Tunetyme

[CadErik]

In a relational database this would be a simple problem to solve.  Link the song with the date and add as many dates for that song as needed.  How do I add a list of songs that I do not have in my database to find and buy?

This is so not true!!!! It's not because you have a RDBMS that it is suddently easy to solve. To do what you are suggesting, you would need to customize the schema and write your own (complex) queries. These two items stay the most difficult to implement, not manipulating tables. Not to mention all the GUI stuff necessary to support that...

[rick.ca]

Replacing a finely-tuned system with an entirely new more elegant architecture is very seductive but it has caused a lot of grief.

It apparently is seductive to some. But I never came close to suggesting this should be done. In fact, I'm sure I said that would be a bad idea, and that I'm quite happy with the way MC works.

It would be much more useful for users to describe what they want to do with MC that they can't do now.  Leave the decisions on implementation to JRiver.

Isn't that exactly what darichman did? Requesting features or behaviour which happen to have something to do with relations in no way suggests one is advocating the application be rebuilt on a relational database engine. If you are willing to leave decisions on implementation to J River, why are you deciding these feature suggestion have no merit, are technologically infeasible and/or will adversely affect performance. If any of these things are true, I'd love to hear it from the developers. Then we could stop wasting time discussing it.

[rick.ca]

I don't want to be writing complex SQL joins to find my music and videos.

That's understandable. But why do you think this would ever happen, or that anyone else thinks it should? ?

[gappie]

and you're sure everyone else agrees with you.

[darichman]

It would be much more useful for users to describe what they want to do with MC that they can't do now.  Leave the decisions on implementation to JRiver.

Okay, I think that's fair enough.

I would like to be able to store information about artists, albums, movies... without having to manually re-add the information each time I import a new file belonging to an artist, album, movie... If I have bio, primary genres, nationality etc filled out for Pink Floyd and add a new file by Pink Floyd, I want MC to be smart enough to know that the new file should inherit those values.

[gappie]

Okay, I think that's fair enough.

I would like to be able to store information about artists, albums, movies... without having to manually re-add the information each time I import a new file belonging to an artist, album, movie... If I have bio, primary genres, nationality etc filled out for Pink Floyd and add a new file by Pink Floyd, I want MC to be smart enough to know that the new file should inherit those values.

the problem is that there are enough artists playing different genres on different albums. should genre be a table on its own, to what should it relate, to artist, album or song, and in which direction should the relation go?
it could be that there are other ways to accomplish this.

 
gab

[hit_ny]

I would like to be able to store information about artists, albums, movies... without having to manually re-add the information each time I import a new file belonging to an artist, album, movie... If I have bio, primary genres, nationality etc filled out for Pink Floyd and add a new file by Pink Floyd, I want MC to be smart enough to know that the new file should inherit those values.

Which is precisely what Matt asked about right at the top, and is why i don't think there is much of a problem under the hood in getting this functionality.

The much harder problem is how the user will interact with it and what changes this will cause to the workflow we are so accustomed to, ie the 2nd order effects

We already have nested fields, this is taking it a step further.

How will it work in viewschemes ?

How do the existing Library tools work with this extra functionality ?

etc.

+1 to gappie's last question.

[tunetyme]

This is so not true!!!! It's not because you have a RDBMS that it is suddently easy to solve. To do what you are suggesting, you would need to customize the schema and write your own (complex) queries. These two items stay the most difficult to implement, not manipulating tables. Not to mention all the GUI stuff necessary to support that...

With the little experience I have developing a RDBMS I found it to be quick and straight forward.  The basic objective is to enter and store the data only once.  The example I used created multiple dates that a song was in the top 20.  This would be a transaction DB with a link to the song.  All I did was add the fields from the transaction DB to the primary display (with artist album etc.) and it was all right there and I could sort and select what I wanted.  There were no complex SQL queries just point and click.  I have 14,000 songs in my database so far and MC is very fast but I am not sure that by converting to an RDBMS that it would become faster or slower.

I am not advocating that MC should be a RDBMS.  RDBMS certainly isn't the boogie man that people are trying to make it out to be.  If nested fields will solve this I would be happy to use them.  But as I requested, please show me how?  Can you have a nested field attached to a particular song and can I search and sort on those fields? 

Frankly my biggest concern is not the database but is MC trying to be all things to all people?  For me I would like to see more development with tools to develop play lists for music.  One feature that would be nice is to be able to identify the key the music is played in.  Here is a site that might be of interest: http://www.mixedinkey.com/HowTo.aspx.   

For me it would be nice if this could be a little more oriented to dj software as well.  I also recognize that others have their priorities as well.  I know there is a growing number of people who would like to see MC run under Linux.  If that happened I would convert in a heartbeat to linux and dump Microsoft.  Like all businesses, MC must choose what they want their products to be and then strive to be the best in the business.  Of course good marketing helps too.

As you can see we all have our wish lists and J River Santa is supposed to deliver what we want when we want it.  What ever you decide to do, be the best and that doesn't mean that you have to have one product that satisfies everyone's bells and whistles.  You could make two or three different products.

Tunetyme

[cncb]

If this would bring us closer to true "client/server" operation (i.e. being able to make library changes from multiple clients at the same time) I'm all for it...

[darichman]

the problem is that there are enough artists playing different genres on different albums. should genre be a table on its own, to what should it relate, to artist, album or song, and in which direction should the relation go?

Yes, perhaps Genre isn't the best example ... as it could be applied to any level of artist, album or song as you mention - that's why I specified Primary Genre, which I use as a custom field and I think it is valid to pick a "primary" genre for an artist, even if they venture into other genres at times. The only way to do this at the moment is with three fields (Genre, Album Genre, Artist Genre). I understand not all users would want to specify and maintain three levels of genre however... There are other examples of fields where simple inheritance would be valuable, however.

The much harder problem is how the user will interact with it and what changes this will cause to the workflow we are so accustomed to...
We already have nested fields, this is taking it a step further.
How will it work in viewschemes ?
How do the existing Library tools work with this extra functionality ?

That was the aim of this post:

1. Setting Up fields to be relational
2. Databse handling of relational fields
3. Implementation into current Tag AW
4. Implementation into view schemes
5. Implementing online submission and lookup

Sorry if I wasn't clear

[darichman]

After reviewing some of the posts above, I'm prompted to pose the question... perhaps the goals/functionality we described early on in the thread could be achieved within the current infrastructure? Ie, without the need to change to a relational database at all?

Maybe someone, with more programming knowledge/expertise than my humble self, could offer an opinion?

[)p(]

If we could have fields that do not need a file assigned this could potentially bring some of the benefits of a relational database. For example:

1. Organize offline media without the need to create dummy files.

2. Organize meta data in a quasi relational way. Lets say I have an entry for a movie X that has actor Y. And we have a non file based entry that has a field named "Bio_Actor" with Y in it then with a simple rule we could display the biography for this actor when needed.

peter

[rick.ca]

2. Organize meta data in a quasi relational way.

Yes, this might be a productive way to think about how some meta data might be "accommodated" in the existing structure of things. Much of what we're talking about has to do with relating artists with their works (i.e., our media files). MC already handles the relationship between artist names, album names and their works very well. All we're missing are the means to record meta data about the artist. And our interest in that is generally just "one way." We'd like to be able to somehow bring this additional information into our normal views, but we can do without the reverse—the ability view artist records and incorporate into that view information about their works. MC already very good at things like displaying all the media of one artist.

So I wonder... What if there were a special "record type" for People that was completely separate from the existing record type for Media? For the sake of (avoiding) argument, assume it's stored in a completely separate database or table. It has no impact whatsoever on how media records are currently handled. It would only be accessed when requested. That would be facilitated by the automatic linking of names (wherever they occur they occur in Media records—artist, people, actors, directors, etc.) to existing People records. In a view in which this additional information is not used, it would have no impact on performance or behavior. It might still be readily available with a mouse-over feature. If performance were not an issue, people information fields could be incorporated into the view as expression columns. Probably better would be using a split view where the records of all the people existing in any field of a selected media file could be shown in the other "people view." This ability to "sync" views, of course, would be a new feature—perhaps an option in defining a people view to be used for this purpose.

Thinking of "people" very loosely, this could be a general approach to saving a wide range of meta data. "People" could include not only individual artists, but bands, orchestras, conductors, actors, directors, etc. They could be categorized according to these types, and/or by any other means in the people records. And that might be all that is necessary to deal with inevitable name duplications (i.e., "John Doe" the musician vs. the conductor vs. the actor). Certain fields would have to be designated as the ones that will be linked in this manner, but those might include one called "Other information." This could be used to link to any sort of information one cared to accommodate within their People (or Other information) custom field definitions.

[hit_ny]

After reviewing some of the posts above, I'm prompted to pose the question... perhaps the goals/functionality we described early on in the thread could be achieved within the current infrastructure? Ie, without the need to change to a relational database at all?

That's the goal of this thread, at least if we want to stay within the realm of feasibility

MC is nothing more than a fancy files lister. It is NOT a flat database, the only one to many relationship it knows about is at the files level ie MC can have many files. Those files can have tags or meta-data associated with them. So, the only entity that exists in MC is the file entity.

If we boil this thread down to just one requirement. It would appear that you want to share the meta-data of an existing entity with newly imported files that belong to this entity. You want to say when you import new files that they should inherit or acquire attributes from already exisiting entities (if already present in the library).

But the entities you have in mind are not file entities, rather they are more abstract in nature.

So MC needs to know how to define what that custom entity is.

You mentioned earlier about being able to assign child fields. This could form the basis of defining what a custom-entity means. If you could create a custom entity that allowed you to assign fields (could also be other custom entities) then you have the basis for a record at the database level.

Now the question of how do you point a newly imported file to that entity arises. I don't think there is a way to do so except to copy that entity into your newly imported one. So instead of inheriting meta-data think assign & acquire.

Currently you can only copy fields within A file but NOT across files. Sure, you can do it manually but not automatically, is the point. So a mechanism needs to be put in place to allow a copy of a custom entity from one file to the other. Now we don't care how MC does this under the hood, its more from a user perspective.

So as a user, all you would do is import then assign which entity the new files belonged to and have MC automatically populate the required data

eg.
- let's say you already defined a custom entity for director.
- When you import a new movie that was also made by the same director, all you would do is select the files then go to the action window (or even in the files view) and type, say [director(name-of-director)].
- MC would then look up that particular director (if it exists in the library) and assign all the data defined for that director into this field for the new file.
- Then you would do the same for other entities for the movie and you would be done.

I think an extra dialog would be required to help enter in data for a custom entity. as don't see it being possible to do so within the existing setup. Within viewschemes or fileviews the custom entity would behave just like any other custom [Field].

Would this be acceptable from a user perspective ?

If so, all that i've suggested to JRiver, if this post were reduced to just one line is..

..find a way to transition MC from custom [Field] to custom [Entity]...

To go from a file is a bunch of [Fields] --> custom [Entity] is a bunch of [Fields].

Once that's done, the expression language needs to know how to deal with it and you have the makings of a flat database, along with the benefits but *without* the loss of speed & extra cost

[rick.ca]

..find a way to transition MC from custom [Field] to custom [Entity]...

Is there any substantive difference between your idea of custom entity and my idea of people records? I don't see the necessity of any new mechanism for copying or assigning these values. Media file records can continue to be tagged exactly as they are now. If a people record is created with the same name present in a media file record, then that name, regardless of what field it exists in, is automatically linked to the people record. Actually, it wouldn't be "automatic" or "linked"—it would just seem that way. The additional information would be retrieved only when requested (e.g., it's been specified in a view, and it's therefore retrieved for the records currently displayed).

The obvious addition to the Expression Language would be a function for the direct retrieval of the additional information—something like OtherInfo([name], [field]).

[ThoBar]

Without making any promises, I'm working on something that *may* solve some of these issues using rules and queries to auto-fill fields. What's being discussed here is on my roadmap, but will take me a while to get to (think v1.5 or 2).

Ultimately, the plugin architecture allows for people to solve a lot of these problems themselves if they have a real desire to.

I believe, though I have yet to try it, that Mr ChriZ's scripting plugin may actually have the capability to allow users to achieve some of this functionality already.

FWIW, I have a foot in both camps. I'd love to see the relational functionality (however its achieved), but really want to keep MC's performance as the priority.

C.

[hit_ny]

Is there any substantive difference between your idea of custom entity and my idea of people records?

No, i call it entity, you call it record

A record could also refer to a music album thats why for lack of a better word went with entity.

Also entity refers to an abstract grouping of [Fields] whilst record in database speak is the implementation of said entity.

Once you've defined a people entity, you would refer to it as a [People]  or even more specifically as [Actor] or [Director].

I don't see the necessity of any new mechanism for copying or assigning these values. Media file records can continue to be tagged exactly as they are now. If a people record is created with the same name present in a media file record, then that name, regardless of what field it exists in, is automatically linked to the people record. Actually, it wouldn't be "automatic" or "linked"—it would just seem that way. The additional information would be retrieved only when requested (e.g., it's been specified in a view, and it's therefore retrieved for the records currently displayed).

The obvious addition to the Expression Language would be a function for the direct retrieval of the additional information—something like OtherInfo([name], [field]).

Well i suggested the need for a new mechanism with entities because there would be no other way otherwise to copy the info contained within and assign to a new file.

Currently with [Fields] thats how you do it, select from the drop down menu and assign.

But a [Field] is just one value. An entity is a bunch of [Fields], thats why thought this way would get them all in one go. Admittedly just picking from a list would suffice as well, provided MC understood it was assigning an entire entity when you chose it.

So yes, ultimately, it would be just as transparent as it is with any [Field] currently.

[rick.ca]

...there would be no other way otherwise to copy the info contined within and assign to a new file.

This is not necessary or desirable. The additional information would be linked simply by the value in the media file record. Entering "John Doe" as an artist, actor or director would automatically provide a link to a "John Doe" additional information record—if one exists. Or if you prefer, entering "John Doe's Greatest Hits" as an album would automatically link to an additional information record with that name. There's nothing to copy or assign. That's only done with/to media record fields, and there's nothing in what I'm proposing that would change how that works now—whether it be tagging, copying, downloading, importing or entering the data directly.

Consider this from the perspective of the additional information record: You add one of those with the name (in effect, the "key") "John Doe" along with whatever additional information you want to record (e.g., birth date and a biography). If there are any "John Doe's" in your media records, then this additional information would be available in relation to their occurrence, as I previously described. If there weren't any, then it would just stand alone in relation to nothing. If you then added some media where "John Doe" was, say, the artist, the additional information would be available. It might be presented in expression columns in a view, or the matching additional information record might be displayed in a split view, but you wouldn't need to copy or assign anything.

The more I think about it, the more it seems like this is an easy and flexible way to provide for the recording of many-to-one additional information—for artists (including actors, directors, etc.), albums or anything else (genres, people or places in photos, background information about TV series). It doesn't appear to be difficult to implement, doesn't change how media records are handled, and shouldn't affect performance except to the extent the information is included in views. It might even provide an opportunity to improve performance—by choosing to handle some data in "additional information" records rather than as regular media record fields. Of course, all this is based on the assumption the additional information records can ignored for speed-sensitive operations, and then "looked-up" "fast enough" when required. Maybe that's easier said than done, or just not feasible.

[hit_ny]

Entering "John Doe" as an artist, actor or director would automatically provide a link to a "John Doe" additional information record—if one exists.

How do you define, artist, actor, director in your system ?

Or if you prefer, entering "John Doe's Greatest Hits" as an album would automatically link to an additional information record with that name. There's nothing to copy or assign.

It happens behind the scenes tho from the user's perspective, currently when you select a  [Field] from a dropdown that what happens. As one expect it to when using entities.

That's only done with/to media record fields, and there's nothing in what I'm proposing that would change how that works now—whether it be tagging, copying, downloading, importing or entering the data directly.

Surely there has to be a way to define an entity. Otherwise yes the way a user interacts will be the same.

Consider this from the perspective of the additional information record: You add one of those with the name (in effect, the "key") "John Doe" along with whatever additional information you want to record (e.g., birth date and a biography). If there are any "John Doe's" in your media records, then this additional information would be available in relation to their occurrence, as I previously described. If there weren't any, then it would just stand alone in relation to nothing. If you then added some media where "John Doe" was, say, the artist, the additional information would be available. It might be presented in expression columns in a view, or the matching additional information record might be displayed in a split view, but you wouldn't need to copy or assign anything.

All i'm saying is if you have an enity column in a viewscheme or files list view, say Director, Actor, Artist etc. Then only those ppl defined for that [entity] appear in the respective dropdown when tagging, if already they exist.

I think we are stating the same thing here

The more I think about it, the more it seems like this is an easy and flexible way to provide for the recording of many-to-one additional information—for artists (including actors, directors, etc.), albums or anything else (genres, people or places in photos, background information about TV series).

The more you think about 'what' ?

Currently the system works as one-to-many.  An Artist might be on many Albums. Those many albums might contain the same artist, but when you search for that [Artist] you see both.  You say show me all [Albums] this artist is in. So the way you interact is more one-to-many i think.

What i've suggested is the same thing, one director, artist, etc might be in several albums or movies. But with the extra ability to contain more meta-data per Entity.

[rick.ca]

How do you define, artist, actor, director in your system ?

Surely there has to be a way to define an entity.

I suppose it would be the first option one would choose in defining a view—the view is for media file records, or for additional information records. From there, the interface could be very similar—the additional information fields displayed in columns.

All i'm saying is if you have an enity column in a viewscheme or files list view, say Director, Actor, Artist etc. Then only those ppl defined for that [entity] appear in the respective dropdown when tagging, if already they exist.

This is not necessary or desirable. The additional information would be linked simply by the value in the media file record. Entering "John Doe" as an artist, actor or director would automatically provide a link to a "John Doe" additional information record—if one exists.

So the way you interact is more one-to-many i think.

Think of it as you like, but what bearing does this have on what we're talking about? ?

[hit_ny]

This is not necessary or desirable. The additional information would be linked simply by the value in the media file record. Entering "John Doe" as an artist, actor or director would automatically provide a link to a "John Doe" additional information record—if one exists.

I don't understand how your system works then.

How do you qualify if a person is a director or other ?

Think of it as you like, but what bearing does this have on what we're talking about? ?

Because you mentioned...

The more I think about it, the more it seems like this is an easy and flexible way to provide for the recording of many-to-one additional information—for artists

i did not follow what you were saying here ?

[rick.ca]

How do you qualify if a person is a director or other ?

Exactly as they are now—by their name being in that "category" (field of the media file record).

i did not follow what you were saying here ?

From the perspective of a media file table, the relation of media files to artists (for example) is many-to-one. Your observation that much of the way the user interacts with the data is the reverse is true. That, in fact, is why I propose the present way of handling that be left alone—so as to no impair performance or require the introduction of a relational database.

Try to imagine being able to completely ignore the additional feature I'm proposing—as you would if you chose not to use any "additional information" records. There would be no such records, and your database would function exactly as is does now. You could still add many-to-one "additional information" in the same way can now—by adding it to all the records to which it relates (e.g., select all of one artist and add a bio). You wouldn't need to relate anything to any "entity"—no such thing exists!

Now, if you chose to use this feature for adding additional information, you would do so by creating a separate (and completely distinct from media file records) "additional information record" with whatever fields you want. It would have one field that would serve the special purpose of linking the record to media file records that contain the same data in designated fields. It is this field in which you would enter the "John Doe" of my previous examples. Doing that (and only that) links this additional information to all media file records in which that name exists.

[hit_ny]

Exactly as they are now—by their name being in that "category" (field of the media file record).

give examples.

What is 'category' now ?

[rick.ca]

I put "category" in quotations because I was referring to the MC use of the term. When a field is added to a view as a column, it's referred to as a "category." I suppose this is because it doesn't have to be a field—it can also be a file path, playlist group, expression or search list. But in this case, I'm talking about fields, which is why I added the qualifier "(field of the media file record)."

[hit_ny]

Now, if you chose to use this feature for adding additional information, you would do so by creating a separate (and completely distinct from media file records) "additional information record" with whatever fields you want. It would have one field that would serve the special purpose of linking the record to media file records that contain the same data in designated fields. It is this field in which you would enter the "John Doe" of my previous examples. Doing that (and only that) links this additional information to all media file records in which that name exists.

This is the difference between our ideas, one i made re:entities has no notion of a link per se. I was hoping one could make arbitrary ones or joins if you like via expressions. This way it might offer the possibility to compare values ACROSS files

But i'm trying to understand your idea and how this notion of a link can be implemented at the "additional information record" or entity level as there is no current equivalent in MC. It seems you are asking for a foreign key in database speak.

So can you elaborate further ?

[rick.ca]

So can you elaborate further ?

No. If anyone else were still following this, they'd be amazed at how much I've elaborated—on such a simple idea.

[darichman]

I'm still hovering around

I think what Rick is saying (correct me if I've got it wrong!)
1 (a). The user could create "entities" (to use hit_ny's terminology) in the database
                   or
  (b). Entities could be automatically created as values are entered in specified fields (eg adding Abba as an artist will create Abba as a taggable entity also?)
2. These entities could be 'tagged' with fields as current files are now. There doesn't need to be any functional difference between fields used to tag entities and the current fields we use to tag files.
3. Values of these entities (eg. Abba) could be linked to similar values in other fields, such that if a file is tagged with Abba, it will automatically be linked to the "Abba" entity and inherit the associated entity metadata...

So, if users do not associate or tag any fields to an entity, then things will effectively work exactly the same way they do now.
If the user chooses to add "additional information" to an entity, they may do so.

The "Parent Field" / "Child Field" idea proposed way up the top seems to be a somewhat simplified implementation of this. Parent fields could act as an "entity" (Artist, People, Movie etc) and Child fields as fields one may add "additional information" to. Once a file is "assigned" to a parent field value, it will inherit the associated child field values as well. If users choose not to use this feature, they can simply not "add child fields". If they do wish to add additional metadata they can do so and enjoy the benefits of not having to re-enter the same fields over and over each time they add a file belonging to an artist/movie etc already in the database

[rick.ca]

I think what Rick is saying (correct me if I've got it wrong!)

Yes, that's what I'm saying, except for 1(b). I imagined "entities" being created only manually, as needed. Perhaps it would be better if they were created automatically. Then, in an "entity view," one would be able to see all entities, whether or not any additional information exists. It would be more consistent with how things work now in that the user doesn't add records—they are added by the system based on the existence of a media file. Also, it doesn't matter to the concept how these are handled internally—whether a record is created for every entity, or if they are just "pseudo-records" (i.e., the system displays the name in a list) until some additional information is actually recorded. If it did work this way, the user would need a way of specifying which entities are to be shown in a view. In the view schema, there would be something like, "Show entities from [fields]..." Also, there should be a system "Type" field in the entity record that is set to the name of the field in the media file record the entity comes from. This would be useful (essential?) for classification purposes, and would also serve to distinguish duplicate names (e.g., where the name of an artist is the same as the name of an actor).

The "Parent Field" / "Child Field" idea proposed way up the top seems to be a somewhat simplified implementation of this.

I don't know if "simplified implementation" is an appropriate characterization. I actually prefer your proposal, but suggest mine as a less relational, simplified alternative. It's based on the notion "additional information" is that which doesn't need its relation to media records managed both ways—it's sufficient to link it so it's available in relation to the media record. For example, let's say you're interested in recording the nationality of artists. If you consider that information to be "additional," you would include it as a field in the entity record. This would not have the effect of nationality being "inherited" as an attribute of media records—as it would under your proposal, or in a relational database. If that's what you wanted, you would include it as a field in the media file record—as you would now. You could still include an additional information field in a media record view as an expression column, but you would have to expect the same (or worse?) drag on performance as that can cause now.