Databases

Moderators: trebor, kingofskiffle, nympho

 

Postby deepheat » Mon Oct 11, 2004 2:52 pm

Greetings fellow pop-pickers

Having recently completed my aquistion of both the US and UK Singles charts from 1952 to 2004, I am now in a position to dump this information (around 400,000 lines of data) into a database. Now, I know from posts to this board that some of you are utilising some absolutly fantastic database structures and was hoping for tips and tricks.

My first choice of database would be Access (since I already have that installed on my PC), however my knowledge of it is best described as poor at best so I could be tempted onto another (MS Windoze based) software package.

Ideally, I would want to carry out some fancy database queries in much the same vein as some of the chart information that is pumped out on this forum. So I will need some advice has to how to structure the initial tables.

If database assistance is not possible, are you guys aware of any on-line resources that have ready made sample databases that could help me?

All help would be grately received.

Many tanks in a dance

Dave :)
User avatar
deepheat
Groupie
 
Posts: 17
Joined: Fri Oct 08, 2004
Location: Out there, dude

Postby jszmiles » Mon Oct 11, 2004 2:54 pm

You should ask Hanboo, Flatdeejay or Tobias "Zobbel" - I know that they have their own databases - They will help You
User avatar
jszmiles
Legend
 
Posts: 39572
Joined: Fri Jan 02, 2004
Location: Radom / Warsaw [POLAND]

Postby kingofskiffle » Mon Oct 11, 2004 3:11 pm

Hey, I use Access 2000 Database in a style that I worked out with some help from Polyhex and a few hints and some stealing from Hanboo (at least as far as output goes).

Without going into details and boaring the pants of everyone, their are two types of database (speaks the A-Level IT student).

Type 1 - Excel View. Basically, this has all the data typed in every time it's used - creating lots of duplication.

Type 2 - Database View. There is a fancy name for this which I'll remember on the bus home tonight (when it's of no use at all to anyone!) but this is simply a lot of linked tables. One for artist, one for title, etc.

Each view has good points and bad points. Excel view is easier to update because each record, once the main chunk of the data has been added, has to be added by hand, as it where.

When setting up the database the first thing to decide is 'what is it I want this to do?'

If the end result is to produce charts for your own ue or to post here then time is not as importnat an issue as it is for, say, Polyhexe's database, which he uses on the Internet.

This now done you have to decide if you are limiting yourself to one chart or lots for this database. This is essential at this stage since now is the eaisest time to sort this stuff out.

My own database has a table with 768,000 records in (roughly) and more being added all the time. The main table is in Excel View (easier to update) and has a chart code so I can differentiate betwene the charts in the database.

This is linked to a ChartSorting table which contains the chart code, a number that it's sorted with and the name of the chart - also the country - either UK or USA at the moment (but even that has 17 chart codes).

From here on in it's a matter of style what to add or how to output the data. I can help you with that (without sending you a carbon copy of my own database) if you like.

I can do a couple of things for you if you want. Firstly, I love building databases - though this usually means I go away and re-work my own at the same time because I find a better way to do things.

I am quite happy to help you put it together - and I'll even walk you through the advanced stuff (such as getting the database to auto compile the Download Chart postings I do here)

Drop me an email at top75charts@hotmail.com and we'll have a chat and see whats what. It really depends how much you want to leanr on your own and how much you'd like to be taught and how much, if anything, you want done for you.
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby jszmiles » Mon Oct 11, 2004 4:08 pm

I didn't mension kingofskiffle... :)
User avatar
jszmiles
Legend
 
Posts: 39572
Joined: Fri Jan 02, 2004
Location: Radom / Warsaw [POLAND]

Postby Dreamweaver » Mon Oct 11, 2004 5:46 pm

If database assistance is not possible, are you guys aware of any on-line resources that have ready made sample databases that could help me?
I Can sort out some access links that might come in handy if you chose access.

mick
Dreamweaver
Groupie
 
Posts: 151
Joined: Thu Jan 01, 2004

Postby aaag » Mon Oct 11, 2004 7:08 pm

deepheat wrote:Having recently completed my aquistion of both the US and UK Singles charts from 1952 to 2004, I am now in a position to dump this information (around 400,000 lines of data) into a database. Now, I know from posts to this board that some of you are utilising some absolutly fantastic database structures and was hoping for tips and tricks.

My first choice of database would be Access (since I already have that installed on my PC)
Out of curiosity: how much time does it take to get all that stuff?! :o I assume you have been talking about electronic data, right? It's not easy to track it down. Any suggestion where to start/end? :wink:
As far as MSAccess is concerned, be careful. With database such enormous like yours (i.e. 400,000 records) MSA appears to be bloody slow! Unless you have got very powerful machine.
Verdict: you better find someone who can create such DB system for you. It's not an easy task.
Andrzej
User avatar
aaag
Roadie
 
Posts: 380
Joined: Mon Dec 29, 2003
Location: Poland

Postby Dreamweaver » Mon Oct 11, 2004 7:31 pm

aaag wrote:
deepheat wrote:Having recently completed my aquistion of both the US and UK Singles charts from 1952 to 2004, I am now in a position to dump this information (around 400,000 lines of data) into a database. Now, I know from posts to this board that some of you are utilising some absolutly fantastic database structures and was hoping for tips and tricks.

My first choice of database would be Access (since I already have that installed on my PC)
Out of curiosity: how much time does it take to get all that stuff?! :o I assume you have been talking about electronic data, right? It's not easy to track it down. Any suggestion where to start/end? :wink:
As far as MSAccess is concerned, be careful. With database such enormous like yours (i.e. 400,000 records) MSA appears to be bloody slow! Unless you have got very powerful machine.
Verdict: you better find someone who can create such DB system for you. It's not an easy task.
Stange as I have a db with a lot more than 400,000 entrys and working on a p4 850 without any trouble.

All the systems I have delt with that run slow are normally down to the design of the thing I will admit access is slower then most but a good design helps no end and regular maintenance is essentual.

there is another downside to access you really need to know before makeing a decition and that is it uses a single file system so you need to back the datafiles up daily as if it goes arse about face it does it big time.
Dreamweaver
Groupie
 
Posts: 151
Joined: Thu Jan 01, 2004

Postby BoroButch » Mon Oct 11, 2004 9:00 pm

I would also like to make a Database. I've got Access which seems to be the one most people use. I would like my Database to contain a single entry for each song containing it's chart run, with a way of outputting Weekly Charts and Artists Discography. Any help would be greatfully received. :roll:
User avatar
BoroButch
Manager
 
Posts: 2262
Joined: Mon Oct 11, 2004
Location: Strawberry Fields, forever.

Postby deepheat » Mon Oct 11, 2004 10:54 pm

Wow :o

Thanks for all the responses so far!

jszmiles wrote:
You should ask Hanboo, Flatdeejay or Tobias "Zobbel" - I know that they have their own databases - They will help You
I didn't mension kingofskiffle...
It was when I started looking at the type of data that these guys pump out to this forum that I thought that this was probably the best place to get some database advice!

Dreamweaver wrote:
I Can sort out some access links that might come in handy if you chose access.
Hi there Dreamweaver (btw absolutley fantastic website - brilliant demonstration of how to manipulate and display this type of data)

Yes please pass on any Access links that you feel would be useful - as I have already discovered that while there is much information regarding MS Access out there, it takes a considerable amount of time to extract the "decent" and pertinent stuff!

aaag wrote:
Out of curiosity: how much time does it take to get all that stuff?!
A couple of months of visits down to the library looking through archive copies of NME and Billboard. A couple more months searching for missing weeks/months on the internet. Then a few more months to transcribe the data onto my pc (either typed in or scanned photocopies). Then about a month writing a small program to get all the data in the same format. Then, finally another month sorting out errors! All in all about seven-man-months - not too bad since I'm semi-retired and have the time to spare! If I had found this forum a little earlier, then perhaps I could have gathered the necessary data in about half the time!

aaag continued:
As far as MSAccess is concerned, be careful. With database such enormous like yours (i.e. 400,000 records) MSA appears to be bloody slow! Unless you have got very powerful machine.
This is true. This is part of the reason why I have asked for help here - I realise that the database has got to be decently configured before any real manipluation can take place. But since I'm not producing the data for a website, I don't think that a minutes worth of processing time will get in my way!

and finally, but by no means least...

kingofskiffle wrote
I am quite happy to help you put it together - and I'll even walk you through the advanced stuff (such as getting the database to auto compile the Download Chart postings I do here)
Many, many thanks for the offer of help. Since I notice from some of your previous posts you are currently at university, I would be happy to exchange some of you Access knowledge for a student pint or two (or three as was often the case in my day :wink:)

Cheers


Dave
User avatar
deepheat
Groupie
 
Posts: 17
Joined: Fri Oct 08, 2004
Location: Out there, dude

Postby kingofskiffle » Tue Oct 12, 2004 10:37 am

aaag wrote:Out of curiosity: how much time does it take to get all that stuff?! :o I assume you have been talking about electronic data, right? It's not easy to track it down. Any suggestion where to start/end? :wink:
I found all the data in several forms (mostly written down or in books) and some off the postings here. Some I got sent electronicly but the vast majority somebody had to type in.

I have been very lucky in that Polyhex taught me a way to input the new chart in Excel from the old chart. Basically, type in the last week positions and the chart gets filled in and all that has to be typed in are the new entries and re-entries. I think I modified this to add up thwe weeks on chart correctly, but I might not be remembering correctly.

As for the rest, a lot of typing still had to be done from the ChartWatch Annuals and some from Hanboo's excellent Chart Histories here and on the old Dotmusic forum.

So thats how it's taken me only a year to go from 150,000 to 778,000 records - And counting.

Well, that and the fact the Billboard data was actualy already in electronic form when I got it.
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby kingofskiffle » Tue Oct 12, 2004 10:39 am

I will happily help anybody out with databases - mine may not be the best (hopefully not the worst :wink: ) around but I think ti does most things people could want. Therefore, logically, I must know how to make the database do these things so I don't mind helping others as much or as little as they want (within reason, naturally!)
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby kingofskiffle » Tue Oct 12, 2004 10:45 am

BoroButch wrote:I would also like to make a Database. I've got Access which seems to be the one most people use. I would like my Database to contain a single entry for each song containing it's chart run, with a way of outputting Weekly Charts and Artists Discography. Any help would be greatfully received. :roll:
Most people use Access because of Mr Gates in America and Microsoft. Oh to have one tenth his money. . . But I digress.

Not sure you can have the database be Primarily chart run and secondarily weekly charts.

A single line of data can not be made into a weekly chart, but a weekly chart can be made into a single line of data. I have a program at home which does this automatically (if programmed correctly!) See Download Chart Threadhere. Producing artist histories from weekly chart data is what this does as well (or a part there of).

So I think it's easier to input weekly charts and then creat histories for the artists concerned.
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby BoroButch » Tue Oct 12, 2004 2:19 pm

Kingofskiffle wrote:Not sure you can have the database be Primarily chart run and secondarily weekly charts.

A single line of data can not be made into a weekly chart, but a weekly chart can be made into a single line of data. I have a program at home which does this automatically (if programmed correctly!) See Download Chart Thread here . Producing artist histories from weekly chart data is what this does as well (or a part there of).

So I think it's easier to input weekly charts and then creat histories for the artists concerned.
So will I have to input each chart every week?

Regards Borobutch.
User avatar
BoroButch
Manager
 
Posts: 2262
Joined: Mon Oct 11, 2004
Location: Strawberry Fields, forever.

Postby kingofskiffle » Wed Oct 13, 2004 10:02 am

BoroButch wrote:So will I have to input each chart every week?

Regards Borobutch.
Yeah - sorry. But you only need

ChartCode - Date - Twk - Artist - Title - RIN

(RIN needed to combine with Title to give the re-entry lissts similar to BHS)

and then the chart run programs can be made to combine so you get

Artist - Title - EntryDate (MinOfDate) - Peak (MinOfTwk) - Weeks (CountOfTwk) - ChartRun(Twk-Twk-Twk. . . etc)
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby BoroButch » Wed Oct 13, 2004 10:31 am

kingofskiffle wrote:ChartCode - Date - Twk - Artist - Title - RIN

(RIN needed to combine with Title to give the re-entry lissts similar to BHS)

and then the chart run programs can be made to combine so you get

Artist - Title - EntryDate (MinOfDate) - Peak (MinOfTwk) - Weeks (CountOfTwk) - ChartRun(Twk-Twk-Twk. . . etc)
Thanks, just a few questions.
Would ChartCode be somthing like "NME" and "RR". What is RIN, and what is the chart run program.
User avatar
BoroButch
Manager
 
Posts: 2262
Joined: Mon Oct 11, 2004
Location: Strawberry Fields, forever.

Postby flatdeejay » Wed Oct 13, 2004 11:19 am

BoroButch wrote:
kingofskiffle wrote:ChartCode - Date - Twk - Artist - Title - RIN

(RIN needed to combine with Title to give the re-entry lissts similar to BHS)

and then the chart run programs can be made to combine so you get

Artist - Title - EntryDate (MinOfDate) - Peak (MinOfTwk) - Weeks (CountOfTwk) - ChartRun(Twk-Twk-Twk. . . etc)
Thanks, just a few questions.
Would ChartCode be somthing like "NME" and "RR". What is RIN, and what is the chart run program.
Yes, if you like. But if you'd like to add charts from various countries or different magazines I suggest you to separate ChartCode column to two separate ones, one for the country or magazine codes (like 'us', 'uk' or 'nme', 'rr'...) and another one for the chart codes (like 'hot100', 'singles', 'albums'...)

Btw what is RIN good for???
User avatar
flatdeejay
Manager
 
Posts: 2021
Joined: Tue Dec 30, 2003
Location: flatland

Postby BluSquirrel » Wed Oct 13, 2004 11:53 am

Hello

I thought I'd join in too :P Using kingofskiffle's method in Access I can get the Peak, Entry Date and Weeks using a query but I don't understand how you can get a chart run :-?

Also I'm guessing the method Polyhex taught you was using VLOOKUP and/or HLOOKUP in Excel?
BluSquirrel
Groupie
 
Posts: 6
Joined: Wed Jun 16, 2004

Postby flatdeejay » Wed Oct 13, 2004 1:03 pm

BluSquirrel wrote:Hello

I thought I'd join in too :P Using kingofskiffle's method in Access I can get the Peak, Entry Date and Weeks using a query but I don't understand how you can get a chart run :-?

Also I'm guessing the method Polyhex taught you was using VLOOKUP and/or HLOOKUP in Excel?
Use your brains! :roll: No VLOOKUP/HLOOKUP needed (although MSA doesn't support them), just a simple query :)
User avatar
flatdeejay
Manager
 
Posts: 2021
Joined: Tue Dec 30, 2003
Location: flatland

Postby BluSquirrel » Wed Oct 13, 2004 1:14 pm

lol i feel really stupid not knowing how to get the chart runs but you might have to explain it to me :roll: sorry
BluSquirrel
Groupie
 
Posts: 6
Joined: Wed Jun 16, 2004

Postby flatdeejay » Wed Oct 13, 2004 1:48 pm

BluSquirrel wrote:lol i feel really stupid not knowing how to get the chart runs but you might have to explain it to me :roll: sorry
Sorry for that! 8-)

The simpliest way is:
1. set the primary keys in the tables (pls DO NOT use the default autonumber key!!!!)
2. link the tables via primary and foreign keys (Tools - Relationships menu I think)
3. then open the titles table and click the + in front of any record. if the links are setted up correctly, and the data are inserted well, the chart run sheet will appear under the selected title's record :roll:
User avatar
flatdeejay
Manager
 
Posts: 2021
Joined: Tue Dec 30, 2003
Location: flatland

Postby kingofskiffle » Wed Oct 13, 2004 1:59 pm

The ChartCode in my database is a number (between 0 and 17 or 18). This is linked to another table where I type the number and then the sorting and then the name and then uk or usa, so

Billboard would be
Chart - Sort - Name - UK or USA
9 - 5 - Billboard Hot 100 Chart - USA

As for the lookup stuff - this I use in Excel to quickly get the new chart from the previous one. I don't use this is Access (can't) and I don't use it to make a chart run. (Though I might not have been clear about it!).

The Chart Run Checker Program

This is what I call it, anyway - though I supose it should really be called 'The Chart Run Maker Program'. But thats not important.

The code is very long and incomprehensible to anybody below Advanced Coding Genius (so don't ask me to explain it or tell you why it won;t work if you get it.)

Polyhex got it of a website and then modified it to fit my database (which means I can use it to generate the chart runs for a particular record.)

Polyhex uses it to generate the runs for his website and you will see differences between his and mine (though they all came from the same basic program). To see why his is different search for Angels by Robbie Williams and you'll spot this run

13/12/1997 - Robbie Williams - Angels - 4 - CHART RUN: 7-5-7-6-7-6-9-7-6-7-4-9-12-15-21-23-32-43-58-71-57R(36)-57-62-73-75R(5)-75R(8)-71R(49)->27

Mine would generate a seperate run for each re-entry. It would be possible to modify the code to do this but I can't and so won't.

How does it work? Access uses a Visual Basic coding system. Firstly the program finds an artist - either one entered or the whole system - whichever is specifed by the user. Then the system concatinates the chart run by artist and title, sorted by date. If a new artist appears, then a new run is started - similar for old artist but new title.

Since this only generates a chart run (and has to be run from a query) you can add to it things like artist, title and entry position in the basic access query system.

You can then produce such wonderful things as the new Download Analysis.

Tonight I get the chart as read out and input into the computer while he's playing the number 1. Usually the chart is ready to post as the number 1 finishes playing. However, due to the lack of internet at home I don't post it then but on Thursday morning. It takes about two minutes to update the chart from the minute I have the full list to being able to print it to the website. It will gradually take longer as the chart goes on and on but it should start to really slow down around 2034, so that should not be a problem. If I was updating the current UK Singles Chart this way it would take longer (about ten minutes) to get this and a full artist history - though I'd probably just alter the way it compiled the data.

Hope this helps everybody.
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby BluSquirrel » Wed Oct 13, 2004 2:26 pm

thanks flatdeejay- it was really simple, i was confused because i was putting everything in one table :P

and kingofskiffle- do you produce the Download Analysis by creating a report?
BluSquirrel
Groupie
 
Posts: 6
Joined: Wed Jun 16, 2004

Postby kingofskiffle » Wed Oct 13, 2004 4:14 pm

BluSquirrel wrote:thanks flatdeejay- it was really simple, i was confused because i was putting everything in one table :P

and kingofskiffle- do you produce the Download Analysis by creating a report?
Yes, I do. I make the report then export as a rich text file - then save as txt file and copy and paste. Just so everybody can see how it all works I'll put something together tonoght to post here.

Probably the codeing as to how it all works and maybe the layouts and things - if I can remember how it all worked!
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Postby Dreamweaver » Wed Oct 13, 2004 5:25 pm

This is one Link I just Found
http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html
But if you Type into google the next line
"MS Access" ("=Include These) or MSAccess
you will get loads of related development sites I see if I can dig out my old sites access books page as that will be usefull When I find it I'll upload it for you all the links and info on the books is there (i think)

later

mick
Dreamweaver
Groupie
 
Posts: 151
Joined: Thu Jan 01, 2004

Postby kingofskiffle » Thu Oct 14, 2004 10:00 am

As promised I'm posting below some of the coding used to make the Download Posting.

However, this is how the thing is made up in Access

Step 1

A query is compiled pulling the complete download charts since they began out of the database and pasting into a new table - one already built.

Step 2

This is then used to generate a chart run sequence for the entire chart since they began.

Step 3

Once completed seperate queries find the positions of the records on the current UK and USA singles charts and add this data into a main query which includes the artist history and the coding needed to make a previous number 1 bold and blue.

Step 4

This is then compiled together and the chart output is generated.

The report looks like this

Report Header - Date (taken from the chart date)
DateUKHeader - A header for the current artist position - different queries combine the data so that elements are different sizes.
ArtistEnteredHeader - The dates the artist / record entered the chart - generated, again, by a seperate query.
ArtistPeakHeader - Artist peak
ArtistWeeksHeader - Artist Weeks On Chart So Far
ChartRun1 - chart run, with added data from the toital weeks on chart so far
Current UK - what it says
Current USA - what it says
Entered Date - blank - for sorting purposes
Detail - The history of the artist on the chart

All of these can 'shrink' - vanish if blank - so that if the record has no UK position this week that row is blanked and so removed.

Code: Select all

[color=red] [b] [size=large] UK DOWNLOAD CHART ANALYSIS   16 October 2004
[/color] [/b] [/size]
[b] [/b]
[color=blue][size=large][b]1[/b][/size] [i]   (1) [/i] Vertigo - U2 - 2 wks[/color]
Artist Entered: 09/10/04    Record Entered: 09/10/04
Artist Peak: 1
Artist Weeks On Chart: 2
Chart Run: 1-1->2
Current Billboard Hot 100 Chart Position: 45
09-Oct-2004    -    1    -    2    -    [color=blue] [b] Vertigo[/color] [/b]
[b] [/b]
[color=blue][size=large][b]2[/b][/size] [i]   (2) [/i] American Idiot - Green Day - 7 wks[/color]
Artist Entered: 21/08/04    Record Entered: 04/09/04
Artist Peak: 2
Artist Weeks On Chart: 10
Chart Run: 5-3-4-2-2-2-2->7
Current UK Single Sales Chart Position: 19
Current Billboard Hot 100 Chart Position: 66
21-Aug-2004    -    10    -    2    -    Time Of Your Life (Good Riddance)
04-Sep-2004    -    2    -    7    -    American Idiot
11-Sep-2004    -    18    -    1    -    Time Of Your Life (Good Riddance) (Re-entry 1)
[b] [/b]



The two

Code: Select all
[b] [/b]


between each record are generated to give a line break between the two positions - there are different ways to do this but this one works so I use it.

Hopefully this should help anybody wanting to generate the same type of query.

Modifications can be made to the previous queries to make the data form the current chart only so that the entire database is not made into a chart run before being outputted.
http://thechartbook.co.uk - for the latest are best chart book - By Decade!
User avatar
kingofskiffle
Superstar
 
Posts: 6029
Joined: Thu Jan 08, 2004
Location: On The Internet

Return to Chart Analysis