I have been playing around with databases for around ten years and though I often think that I have just about grasped something I then find I have got confused. Again!
It is not that I am a complete idiot though. Over the years I have picked up an acceptable (in my eyes!) working level of Excel, Dreamweaver, Front Page, Photoshop, Fireworks, InDesign, Serif Page Plus, CuteNews, Powerpoint , Flash and others simply by clicking on buttons to see what happens or searching the web for solutions to similar problems.
I have also tried this for things I have been trying to do in MS Access but for some reason just cannot pick it up. The only time I seem to be able to grasp it is if the problem that I am trying to solve has a solution somewhere, in exactly the context I need it. That way I can do a sort of 'reverse engineer' on it to understand how it works.
Having said that, one program that I use actually runs off of Access databases so have opened it in Access to try to understand it. The trouble was that the data in all the tables were ID numbers and although I do understand about ID's, relationships, primary and foreign keys to an extent, this is one of the things causing me most problems to understand.
I fully understand that linking tables creates smaller databases and faster indexing and that taking the ID number primary key from one table and linking it to the same ID number as a foreign key in the second table will mean that all data in the same row as the id number in either table will be linked.
However, what I cannot grasp (and I am sure I am just missing something simple) is how, when you are using ID numbers as a lookup in the second table, do you know you are selecting the right data. For example if you wanted to add a player to a game via a lookup you know what name to put in but you won't know the ID number.
As I said, this is probably very simple and bread and butter to you guys but I just don't get it!
I have tried getting some simple starting help on other database forums but never got very far, sometimes because I was not already au fait with all of the terminology that they were throwing at me and they would not continue with the thread because I asked what they meant or because the general answer to any question is 'Look at this link......' which, presumably, was telling me what I wanted but as it was not in the context I need it, once again it went over my head. What I think I really need is from the very start of the project (as it will be a large one - but I will get to that eventually) to take each little bit one step at a time and seek advice on how to do each bit, hopefully in a way relating to what I am after.
And I can promise that this is not a school project as I am 37! Although, as you can see, I have already searched the forums to see if the questions have already been answered. And I do agree, sometimes even I can smell a request for help for a school project a mile off!
The main reason that I really want to get a better knowledge of databases right now is two fold. I write the matchday magazine for my local football (soccer) side and in that there are are quite a lot of game and player statistics, including historical ones, that I currently create using two different football statistics programs, about a dozen Excel spreadsheets (using data connections to link data between them), although because it took so long to set up sheets for everything I wanted to use I ran out of time and none of them ever got finished so I still do not get everything I want out of them.
The reason I do it this way is that although both programs enable me to generate most of the information I want, both do something that the other doesn't, of the things that both do they do them in very different ways and for various things I prefer one or other of the programs and one can generate entire web sites (something else I will be doing soon) although not very well whilst the other has much better querying and exporting to csv functions to make it easier to work with Excel.
I have also agreed to take on the job of the long overdue redevelopment of the clubs web site and although the CuteNews system it runs on is fine for news, a large part of a football clubs website is statistics and they should be fully interactive. By this I mean if you click on a game you get taken, for example, to a list of the players who took part. If you then click on a players name you get taken to his profile page or a list of the games he has played in. And so on.
Although I am aware that Access and MySql driven web sites are both different beasts and although I was thinking of initially trying to get this built side by side in both, I think it would be best to just work with Access to get it right to start with as I should (hopefully) be able to work out how to get it across to MySql. Unfortunately the clubs web host does not allow remote connections to the database (strangely Dreamweaver can connect to the database but no other program can) so will, presumably, have to export all tables from Access and import them, individually, into the web database when the time comes.
Anyway, after that not so brief introduction, I will try to explain what I am looking to do.
I currently have a spreadsheet of every game the team has ever played, which is numbering just under 5,000 against over 1,000 different teams. The information includes the date, the score, the crowd attendance, what competition the game was played in, the goalscorers, the stadium name and the name of the manager at the time.
I want to move this into a database with a slightly different format so that other teams matches can also be put on to generate league tables/standings and competition-wide statistics. The main other thing I want to add is players and statistics for each game they play in but that will be a long way down the line.
As there will be a lot of games to input, I want to try to make things a bit quicker by setting up some referencing tables first. In a briefish example, when you are entering the details of a game, once you put a date in, I would like the season field (a season in soccer is generally played from around August until May and takes its name from the two different years it spans, for example, we are currently in the 2009/2010 season) to fill automatically. There have been around 20 different competitions that the club has played in over the years but not all have been every year so, once again, when the date is entered, the competitions field will only allow you to enter the name of a competition the club played in in that season. And so on.
I hope that I have not overcomplicated this in my intro but if any of you good people out there would be able to, basically, explain to me how to do various things, almost in the purest laymans style, from the very first table and explain why it would be done that way I really do feel that I would finally grasp it.
I will in the next day or so put a post in the correct forum with my idea for the first couple of tables and hopefully 10 years of frustration could come to an end!!
Finally, for those of you who have read down to this point, thank you for having the patience and I hope that I have not hurt your head to much.
Thanks in advance