Thursday, January 8, 2009
I Am… Annoyed
So, many years ago, what is now my on-line book database started out life as a simple Excel workbook.
When I had to learn MS Access several years ago, I took that Excel file, and tried to convert it into an Access file. It worked… ok. But at least got me comfortable with the basics of Access.
A year or so later I started working with the woman who was helping me re-write our Access classes, and as an exercise she helped me again convert my Excel file into an Access database, and this time I had a better idea of what I was going, and built queries and life was seemingly good.
Well, when I wanted to learn MySQL, I took that Access database and imported it into MySQL, and that’s how my book database began. I’ve used that database to learn to write queries of various types, and all kinds of PHP/MySQL manipulations. And I’ve gone on to write other databases since then.
Well, I’ve been tying to learn more of MySQL, and been playing with my database and been running into a LOT of trouble. It’s been frustrating, but I’ve mostly been able to get things to work. And as a matter of course the more I learn the more I discover that I should have done things differently, and gone back and changed this in light of this.
Well, yesterday I had a problem I couldn’t figure out, so I had Michael look at it. He was playing with the queries, and then asked to look at the database structure. Since he was driving, I was looking at everything from a different angle, and I suddenly realized the whole damned database had been set up wrong.
That’s right. Instead of having foreign keys liking the databases together, I repeated every bit of data in multiple tables.
(deep breath)
So I’ve dumped the entire thing back into Access, and I’m going to see if I can actually repair the damage.
Wish me luck.
Loading ...
January 8th, 2009 at 2:18 pm
I have no idea what that alphabet soup is that you’re talking about, but good luck. :)
January 8th, 2009 at 3:57 pm
Some of that made sense to me (I know that the database for my blog is in MySQL and I know not to mess with it). Mucho, mucho good luck!
January 8th, 2009 at 4:25 pm
Ah, yeah. Foreign keys when used properly are extremely useful things. :)
January 8th, 2009 at 6:26 pm
The problem, MWT, is that the foreign keys were working backwards.
What a mess.
I’m getting it sorted out. I’ve now got everything into a single table, with the authors and the genres having tables with their own keys. I just have to figure out whether to put the foreign keys in an array or create extra fields in my tables.
And how the hell to go about doing that.
January 8th, 2009 at 7:21 pm
Well, the real beauty of a SQL database is to have lots of little tables instead of any big ones. Then you make composite virtual tables (called views). All of my keys are table columns. Some of my tables are nothing but keys.
It helps to draw out a map, with boxes representing each table and arrows connecting all the keys. Yours probably only needs three tables with actual data (title, author, genre?), and then you could make more tables having the keys related to each other in various ways.
Of course, then you run into the problem of actually putting data into the database, when so much of it is keys. This would be my current issue, and the reason why I’m learning so much about HTML forms and PHP. ;)
January 8th, 2009 at 7:24 pm
Hmmmm.
Actually. I’d make one table for primary key | genre, one table for primary key | author, and then the third table would be for primary key | title | author key | genre key.
January 8th, 2009 at 7:39 pm
I’ve got it. I’m going to use lookup tables to link everything together. That will keep me from having too many columns in each table, and keep me from having to deal with arrays or lists or anything else awful.
So I’ve got five tables: author list, genre list, books, author lookup, genre lookup. author list, genre list, and books each of their own primary keys. The two lookup tables link the three primary tables together.
My biggest problem with this database is that it was created in access when I was just learning about databases, and the person who was teaching me did catch the problem. Or rather, the solution was backwards from what I needed. (The books table had unique rows. The author and genre tables then contained that primary key as a foreign key.) Anyway. It was a mess, and was part of the reason I was having problems, because I was repeating data in multiple places.
So I have my fingers crossed that the lookup table will solve those problems.
Of course, this means I have to design my php and HTML from the ground up AGAIN, but I should be able to use bits and pieces.
And if you have any HTML questions, please ask. I’ve been writing code for webpages for about eleven years, so I’ve gotten pretty decent at HTML and CSS. :)