In the past I've only ever dealth with creating sites with smallish databases, not a lot of users etc but I'm planning a project which will store large amounts of data in a MySQL database.

 

As such, I'm really looking to learn about designing databases - how best to structure them, relationships etc.

 

I have a very basic knowledge of this but I think it could prove important to me in the future so can anyone suggest any user-friendly ways to learn it? My trouble is that I actually find it pretty boring so I've always struggled to find a good learning resource because it's seems like a difficult thing to teach without a little bore here and there.

 

I'd ideally be after something that relates it to a real-world website project, none of this 'imagine you're creating a student database' type stuff. I want something that will teach me the best practices for a real user-driven website. I may be asking for too much but any suggestions would be welcome.

 

Thanks!

You need to learn the concept of "3rd normal form", this is designing a database so that data needn't be stored more than once.

 

For instance, if you had a website where people signed up and wanted to buy cars they might have to choose 3 car models that they are interested in, this could be shown as such in a users table

 

1 | Adam | Smith | 23 | Male | *email* | Vauxhall

2 | Adam | Smith | 23 | Male | *email* | Ford

3 | Adam | Smith | 23 | Male | *email* | Toyota

 

This is inefficient because your database would soon be 5/6 times the size and it would take longer to search and have much the same data, 3rd normal form would split it up as such and turn that 1 table into 3; User, Cars, UserCars

 

User

1 | Adam | Smith | 23 | Male | *email *

 

Cars

1 | Ford

2 | Renault

3 | Mazda

4 | Toyota

5 | Vauxhall

 

UserCars

UserID | CarID

1 | 1

1 | 4

1 | 5

 

 

This is a basic example, the joining table links the user to the cars, this method can be used no matter how big the database is or how much information you may have in it.

 

So start by searching 3rd normal form.

 

I'm in a similar boat. I'm working on a project with scaling in mind.

 

Depending what your database is for look at Drupal modules / Wordpress plugins that do something similar. The successful ones will have scaling in mind considering the size of some Wordpress/Drupal sites.

 

All you need to do is look at the tables and try to reverse engineer them, it's a great learning exercise.

Thanks for the responses, I did a little reading and believe I may be getting somewhere with this.

 

Never thought of reverse engineering so thanks for the suggestion.

 

Got some serious planning to do once I'm more comfortable with this!

Okay... you're going to laugh... but there's a text book I used when I was studying for a database module at uni last year. I used to have the same problem as you, namely that I found it boring. I was in Waterstones one day browsing the computing section when I came across this:

 

Ta-daa!

 

Yeah, I know what you're thinking. But seriously, it was actually well written and illustrated and actually pretty entertaining to read. I'm a pretty massive nerd fan of this kind of stuff anyway so it kinda resonated. It went into enough depth to cover pretty much 80% of the material on my course, but it obviously wouldn't be suitable as a big serious reference book (or even something you'd want on your shelf at work). But anyway, it worked for me, so I figured I'd sling it out there.

 

(I ended up passing that module with a first as well, so make of that what you will )

Think I'll definittely give that ago, also into that kind of thing so even if it doesn't cover everything it might be a good intro and push me to learn more. Got to be worth a try, thanks.