Absolute beginner's guide to database design

  tasslehoff burrfoot 23:20 25 Jun 2008


Can anyone recommend an online resource for learning database design?

It's the principles I'm interested in mainly, I've played around with php and mysql and am okay with them at the moment but what I don't understand is how to actually create the database - how many tables will I need, what goes in which table, how do you link the tables etc.


  Forum Editor 19:26 26 Jun 2008
  tasslehoff burrfoot 22:04 26 Jun 2008


Thanks for that, I don't think it's what I was looking for but I can see it will be useful all the same.

I will try to clraify what I'm asking:

I don't have a specific projec t in mind at the moment but as an a example, let's say I wanted to make a football prediction league website.

What I don't understand is how I would structure the database (or even how many databses I would need).

I'm guessing I would need a table for users, one for the football teams, one for the fixtures, one for the results, one for the user's predictions.

Or would I need a separate database for each of those things?

How would I link all the different tables (or databases) so that looking up a user in the user table can return how well their past predictions have done and what future predictions they have made?

And how would I use the results table to inform the user's table to see who has the most points?

Hope this makes sense, thank you

  Taran 08:49 27 Jun 2008

You've asked many questions which, combined, would take pages upon pages of answering.

Here are a couple of links to Open Source Fantasy Football league scripts written in PHP with a MySQL database that you can poke around in: click here and click here

They should give you some ideas of the possibilities surrounding database structures, not to mention having the ready made PHP to play around with.

Database design is a huge topic and not easily covered briefly.

Basically table names and conditions in SELECT queries (not to mention primary keys) allow you to call and display data based on the conditions you apply to the query.

A very simple plain English SQL query could read:

SELECT all from column surnames
FROM table users

It does exactly what it says - it returns all surnames from the 'users' table.

SQL syntax for the above could be:

SELECT surname
FROM users

I've split the query onto two lines but you don't need to.

The biggest problem you will have is learning data normalisation, and I only mention this since you ask particular questions about how best to structure the database.

Basically you would use one database for the vast majority of projects, but the tables in it would be subject to a normalising excerscise to make them as efficient as possible.

There are arguments for not normalising your database and many data-driven web applications can be thrown together in short order with little thought and planning, but the more well considered the program is the better it will perform, the more robust it will be and the more you will be able to do with it as you develop it in the future.

For a small application you might not need to devote much thought to this, but it's worth knowing about because it is good database design practice and if you ever do go into serious development in the future you won't want to learn about it then.

Better to do it right once than rework it several times.

W3Schools has a good intro to SQL: click here

  HighTower 11:22 27 Jun 2008

Around 4 1/2 years ago I found myself in the same position, and Taran was the one who came to my aid (thanks Taran).

click here

The most useful thing I found was actually buying a book. The Kevin Yank book mentioned in the thread above was an excellent introduction. I added to that with a PHP for Dreamweaver 8 book and then just kept on researching, learning and asking questions. I find a book far easier to learn from than reading from the screen or a print out. You tend to find that they are much more organised and well thought out than a series of web tutorials, unless you find some particularly good ones.

I now wouldn't consider writing a static site, and to date have written nearly 50 dynamic ones, all php / mySQL - so that investment in a couple of books has paid for itself considerably!

Once you get your head around the structure and how to organise relational databases you should be able to get started pretty quickly. Whether you decide to get right in the guts of things or use the build in behaviours in an app like Dreamweaver is up to you. Personally I do a bit of both.

Good luck!

  tasslehoff burrfoot 12:34 27 Jun 2008

Thank you Taran and HighTower

I'm at work at the moment and have to be careful what links I follow, but I will certainly look more closely at your advice when I get home.

Thanks again

  AvatarRoku 07:35 10 Jul 2008

You can do it on several way.
?nstall mysql and use command prompt.
Install apachetriad , use phpmyadmin.(u can use seperately too.)
Use premium soft navicat. (I prefer)

  AvatarRoku 07:36 10 Jul 2008

I think you have to learn sql. Structered query lang. click here

  tasslehoff burrfoot 19:54 10 Jul 2008

Wow, didn't realise it had been so long since I posted in this thread.

I've had little time but have been looking at the links Taran posted, particularly with regard to the structure of the databases how they link etc. But I'm still working on that.

AvatarRoku, I'm okay with the actual creation of a database and with mysql, it's more the structure of the database (tables/fields etc) that I'm struggling with.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Huawei MateBook X Pro review: Hands-on

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?