Announcement

Collapse
No announcement yet.

Music Chart Database

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #26
    Originally posted by kingofskiffle View Post
    The NME Singles chart in the UK is another example of EP and albums charting in the singles chart.

    One either point is how to handle B-sides, writers, producers, etc. Should they be included? If so, then when you get to CDs the process for album tracks seems more usual to apply. For me, I have each Track with the potential to link to a Track Listing table which can include just the album tracks, or the collected B-sides of CD issues.

    Is this the same way you deal with a track on say a studio album, a live album and two greatest hits packages, as well as a handful of compilation albums?

    Comment


    • #27
      Here are ALL the Billboard C&W charts:
      https://ufile.io/fkzxdhr7

      And the Adult Contemporary charts:
      https://ufile.io/2zyslkgv

      Comment


      • #28
        Originally posted by DrTravel View Post
        Here are ALL the Billboard C&W charts:
        https://ufile.io/fkzxdhr7

        And the Adult Contemporary charts:
        https://ufile.io/2zyslkgv
        Thank you DrTravel .

        Comment


        • #29
          So I recently caught a flu (hopefully not a Chinese one) - when I recover I will provide a status update and make a revised timeline overview (probably will do it regularly).

          Today I started drafting the chart list, here's how it looks so far - https://ufile.io/mymqjjrx . Basically all that is green is ready to be imported to the database.

          So you see there's a lot still to be added (more Cashbox, Record World, NME etc., missing Billboard charts (that were long dicontinued), other countries etc.), and I have burned out all available time for today, but I hope some of you can further help me out by enriching this list in the same manner or providing helpful links. What would be good in the time being is to search this forum for other Excel files or chart runs and drop links here, just a few examples I would start with myself (and will do after I recover) there are threads about 1960s UK EP Charts, "rare" Billboard charts like Billboard Comprehensive Albums, a Record World thread etc.

          And DrTravel , you can see I mentioned your most recent spreadsheets as the best data source (even added some extra "green" color for you) - so please share more!

          Comment


          • #30
            Logged in for a quick update, then will be gone for a few weeks - I will need to complete another project, but after that I will dedicate a lot of time for this one.

            Some people did offer their help with the list of charts - I still need to look closely at the output, but overall it looks promising. The way I see it at the moment:

            - End of February I will setup the "basic" schema to allow inserting "semi-raw" charts (to include calendar, charts, chart positions, the very minimum artist-album-song separations). Then I will insert every data I will have by that point into the database (work under way on Charts List table - need to finalize it based on other people's input and also need to have another dedicated long go myself). Result: Initial database file.
            - In March I will work on the basic admin website to view and modify the database, mid-end of March I will deploy a cloud instance with a limited number of private logins. This early version will be browseable and even clickable through very basic artist pages (auto-detected based on Excel input data with some minimal euristics applied), which will of course contain a lot of mistakes and inconsistencies, although I hope 70% will look more or less fine.
            - In April I will work through the list of "tricky cases" to understand how they fit with the data model. As a result I want to prepare the list of examples for each tricky case and implement these examples manually through the admin interface. For that I will in some cases need to adjust the model and implement new UI operations. I do want to document each case, so that in the future users who want to fix the mistakes of auto-imported charts will have a reference to know what set of actions to perofrm in each case. Result: adjusted data model, advanced user interface, documentation.
            - In May I will probably have a lot of time, that time could be used for various activitues - getting more charts, refining code or whatever else presents itself.
            - In June I can start working on analytics, but need to get more ideas on what should be implemented (I have some of mine, but for now don't want to spend time on details).

            You are free to propose any adjustments to this plan. Kinda looks too easy for me...

            Comment


            • #31
              This is what I have been working on for the last 20 years. I have my charts in a database and that can be tweaked any way that you want it by creating different queries. What I'm struggling with now is, before the collection of sales data electronically, the charts that are published by newspapers, radio stations, branch mags, etc, have all different ways of sampling. This would be the charts from the 50s, 60s, 70s and in some cases beyond. In the UK there were the NME charts but also Record Mirror, Melody Maker, Disc & Music Echo, Record Retailer Mag, BBC pick of the pops, Big L's Fab 40, Mersey Beat, Top pops, and probably other charts too. I have come to the conclusion of these early charts that you can't just trust one of these sources but there needs to be a pan-UK chart that is a mingling and weighing of the charts available. This should be done up until it can be trusted that the electronic sampling is reliable.

              Another bump in the road is the different publishing days the charts came out in different countries. Take as an example Switzerland which has their 'official' chart on every Saturday but the charts in Der Musikmarkt are bimonthly and are published on the 1st and the 15th. To get parity in dealing with differently published charts I am now using week numbers. As long as a chart is published in a certain week, they 'belong' together. The use of week numbers is very business like but once familiar with how they are making up quarters of a year, it becomes very easy to use.

              Also the use of telephone reporting of early charts, with all kinds of favoritism, and the radio based telephone-in charts that are still in use today (as far as I know), they are very different from the sales (or shipping) numbers that are the base for a lot of the charts. Should these charts also be counted or left out? Some examples are Sweden and Denmark with lots of charts but with starts and stops and overlaps. It can't be denied that phone-in charts influenced the sales of records but to what extent and are they legitimate charts?

              Have raised some of the things that are uppermost in my mind but my work has been very close to what you have proposed. I would like to see a global music chart database that can be accessed by anyone. Let me know if I can help.

              Comment


              • #32
                Hey all.

                Please tell me if there's still an interest for this project.

                It is March already. Just want you to know I have started coding both UI and database layer, let me now put a target date of March 23 for the first deployment. As previously announced it will have a big chunk of BB/OCC chart data browseable and searchable by artists/dates. I will allow friends on this forum to create read-only accounts, and will give limited admin accounts for those wishing to help edit the data.

                So right now I am focusing on the coding part, but when the first version of software is done I will focus on getting more chart data and inserting it into the database. And for this process to be efficient I will need your help. You can start helping now by specifying what chart data you want to see in the database (excluding what is currently available at BB/OCC as that is already going in by default, but including anything that is NOT currently available there) and if it is possible - provide the links to threads on this forum or external sites where that data can be obtained (e.g. complete or partial scans on americanradiohistory, pages archived at archive.org etc). Probably for most of the possible charts I would already know myself where that data can be obtained, but for each case it would take a tiny bit of effort on my side to recall it and search for it and download it and document it etc and it would add up to a grand total, so any help from you, even small, will free me up and help me focus on managing the data inside the database and improving the software.

                I am sorry for being so wordy again. In short, please give me your suggestions on what data you want - let's see if we can prioritize the order in which the data would go in.

                Comment


                • #33
                  Will this be a stand alone thing, or is it going to
                  be cloud only.

                  Comment


                  • #34
                    Originally posted by Chartaholic View Post
                    Will this be a stand alone thing, or is it going to
                    be cloud only.
                    At this point it will be a private website. As for the desktop version, I would want to wait for it to be more stable and functional, at the very least for the schema to be finalized and proven to handle various edge cases (to avoid issues with data migration for the desktop users). Desktop build is technically possible, but I don't want to multiply a big number of potentially incompatible versions of it, as I won't be able to support all of them. When website and data model is stable, I will make a desktop build. Desktop version would be a bonus, as a gift for people who helped with the data. Priority 1 for me is to have a shared private instance where we can collectively manage the data and I need to make sure it's functional and performant so it can stay online for free forever.
                    Last edited by xsergan; Mon March 2nd, 2020, 12:33.

                    Comment


                    • #35
                      I really like how the ISFDB does it. All the code and data files are available on the site for download and installation.

                      the main advantage of this is that should something happen to the key holder (%for lack of a better term) everyone’s work on the project is not permanently lost. Not to be morbid, but stuff happens unexpectedly to anyone,

                      the is just my view on the matter.

                      For example, I have asked on and off over the years if there is a legacy plan for ARSA and the answer has always been no. Tons of work has been done on that site by volunteers and it’s all held in one persons hands on business servers they own. The number of pedigree Possible problems with that just makes me cry,

                      Comment


                      • #36
                        Right, providing the copy of a database (allowing to download a copy at any time) to involved people is what I thought about from the start, I wrote about it. It still holds in principle, and time will tell that all I meant was true, that I will put effort into ensuring the work is not lost. However, I thought about it (and some people were worried about possibility of people snatching the data and leaking it somewhere, and I am myself worried about bringing too much public attention to this project), so I think I decided to introduce some limitations at first. I was going to write about it later, but I can do it now. So the way I see it - we can discuss it now - at first stage I will give read accounts to the website for all people on this forum registered here for at least 2 years (artificial limitation - let's discuss it) so people can browse the site data. I wil give some admin accounts to people who want to fix data mistakes. And I will provide a way to get database copies (and later desktop builds) to people who can help with big chunks of data (e.g. preparing chart spreadsheets or providing other helpful sources). In principle, I will ensure the result of work gets distributed and remains saved forever, maybe at first to a more limited set of people, but many others will still be able to browse online. It will never be commercial or paywalled and will not suddenly disappear. If there is at least one person here who is capable of maintaing/deploying NodeJS code (as I decided that using Java is overkill here and chose to use NodeJS) I will share source code with that person. Hope it makes sense.

                        Comment


                        • #37
                          I definitely have an interest in a common chart database. I'm more interested in the structure and content than the software for accessing the data - with good commonly agreed data structure, the data tables would be easily shared and should be transferable to most any database.

                          I'm happy to participate in a discussion on that basis, to contribute my current structures, such as they are and explain why. I notice some folks have already posted some points of concern above. Would that be too detailed for this discussion board?

                          FYI - I posted some data along these lines under the "Some Billboard Chart Collections" discussion.
                          Cottonwood
                          Texas

                          Comment


                          • #38
                            Just briefly, I think a common chart database needs separate tables of:

                            o songs (with authors, alternate song titles, sort titles, comments (for other random info) and maybe year of publication)

                            o artists (with group members, alternate group names, unique common name (for those whose name changed over time, comments (for other random info) and when other groups used the same name), sort name, year born/formed, year died/broke up and maybe home town)

                            o tracks (with artist common name, artist as listed on the label, track name as listed on the label, year first released, is it instrumental, label no., alternate label no.'s for re-releases, comments (for other random info) and maybe b-side song, vocalist). The tracks table also needs links to the songs and artists tables but needs some duplicate information for efficiency.

                            There is a lot more to say about it, but for now, just for a start...for the tracks list, we would need to be clear whether it is a list of
                            a) songs by an artist
                            b) recordings by an artist (so re-recordings are separate entries)
                            c) releases by an artist (re-releases are separate)

                            I tend to favor b), but didn't figure this out until after i'd done a lot of work, so it isn't 100% consistent in my data (I'm working on it).
                            Cottonwood
                            Texas

                            Comment


                            • #39
                              Originally posted by Cottonwood View Post
                              I definitely have an interest in a common chart database. I'm more interested in the structure and content than the software for accessing the data - with good commonly agreed data structure, the data tables would be easily shared and should be transferable to most any database.

                              I'm happy to participate in a discussion on that basis, to contribute my current structures, such as they are and explain why. I notice some folks have already posted some points of concern above. Would that be too detailed for this discussion board?

                              FYI - I posted some data along these lines under the "Some Billboard Chart Collections" discussion.
                              Thank you for your interest. I think I will include a simple issue tracker on the website, so that we can discuss various things in details and not be constrained / lost in a single big thread.

                              I want you to know that I am not ignoring your suggestions - after I have something material ready as a starting point, I will go back to all questions and suggestions raised here.

                              Comment


                              • #40
                                As for the date, I have to shift it a week to March 30. But good news is that I took a week off from the main job for the next week to devote my full time to this.

                                Comment


                                • #41
                                  So I've made a good progress this week, but not yet ready for initial release.

                                  What's taking so long? I underestimated the effort needed even for the most basic setup going completely solo on this (as I don't usually work alone). I also got lost in various work and forgot that I only wanted to have a basic chart browser in the first release, so I also did some basic work and setup on edit/admin functionality, gave (yet another) thought on the database structure and how to specify tricky relationships between entities (where I ended up having most generic ENTITY and ENTITY_REL tables with infinite possible types of relationships giving both a lot of flexibility and extra level of complexity to writing queries - I even started wondering if some kind of graph database could be a good choice here, but since creating the single-file database is the most essential point for this project I don't see any other option except the most universally applicable sqlite format). So really spent some time on thinking and slightly adjusting schema based on how these relationships should be specified.

                                  Anyway, I also worked on the planned things, including searching by artists and titles, browsing charts by dates, worked on refining a procedure that imports charts, worked on importing a subset of available charts. Worked on user management.

                                  What's left to do - refine presentation of artist pages, refine presentation of charts, import the remaining huge bunch of charts, including writing a parser for DrTravel Excel files. Then at least one day is needed for actual deployment and server configuration.

                                  Good news is I will have another full-time week on this next week, so let's say I shoud finish by next Friday, April 3rd.

                                  I know some of you are really waiting to see how it will look, that's why I give you these dates, and even though I keep adjusting them, with each day I have more vision on how this project will be shaped and developed and have more confidence in it. I think some of you may like it.

                                  And to make sure we have realistic expectations, once again need to repeat what was said before - in this first release it will be somewhat dirty, i.e. the data will be auto-imported from various sources with minimum euristics applied - at this point I can detect the lack of presence of "The", fix any white-space noise, can parse "featuring" or "feat." to auto-label featured artists so that we don't have "Lil Wayne feat. Drake" in the list of artists, but overall the data will be raw and will need to go through cleanup stages which can take many months (or years). But obviously without raw data nothing else can happen, so this is the necessary first step.

                                  Right now overall timeline seems to look like this; April 3 I will release a read-only UI with a huge chunk of chart data. I won't give admin/edit UI rights yet - I did something already but will still need to work on editing functionality, in particular to allow for specifying relationships between entities, and some cleanup-specific issues like merging artists that should be one but were auto-imported as different ones etc. But in April I will have to park it and go back to other jobs, so next time I can work on this is early May where I will have 2 week full-time on this again. So I hope in April I will receive alot of feedback from you all. In early May I will complete Admin UI and refine the database structure. I plan to provide examples for different tricky cases and how we can use UI to fix them. After that in late May I will start giving editing rights for those who would want to work on it, and will share initial version of database file with in exchange for some chart data or other help. The database file might not be particularly useful without software to some people, but it is still a valuable means for preserving this data, and at some point (when database is finalized and Admin UI is stable and functional) I will build desktop applications for private use so that database file can be opened with the same database and same UI on your PC.

                                  Comment


                                  • #42
                                    Re: ENTITY and ENTITY_REL

                                    excellent. This is the proper way to handle such issues. As you say, it complicates queries but correctly allows for the accuracy of data. Most queries only need to be written once and stored, using variables, so it is at least not an ongoing pain

                                    dont stress about meeting estimated dates. This is supposed to be a hobby and fun. Delays are always preferred over burnout lol.

                                    Comment


                                    • #43
                                      Originally posted by Chartaholic View Post
                                      Re: ENTITY and ENTITY_REL
                                      dont stress about meeting estimated dates. This is supposed to be a hobby and fun. Delays are always preferred over burnout lol.
                                      Thanks. But this is a mission, not just a hobby!

                                      Comment


                                      • #44
                                        So I haven't completed everything I wanted to. I haven't yet imported all the charts that I wanted to (but I did import about 130K of them). I haven't designed all the pages the way I wanted to. And I have just barely started work on the edit functionality (aka admin UI), so at this moment it is completely disabled.

                                        But I am still eager to share what I did do. Just putting some finishing touches and will start giving out invite links later today or tomorrow. PM me if you want to get one. I will PM many of you myself when ready.

                                        Comment


                                        • #45
                                          I have some issue with session cookies at the moment. For now the site only works in some browsers (apparently it works in Chromium based browsers on non-Apple devices only). In every other browser session cookie is not passed, so app keeps redirecting to /login page. I wil try to fix it today.

                                          Comment


                                          • #46
                                            Found a bug in redirect function and fixed it. Tried in Firefox - now works well. Can't test on Apple device though, so hopefully others can

                                            Comment

                                            Working...
                                            X