12 Replies - 776 Views - Last Post: 08 September 2008 - 12:15 AM Rate Topic: -----

#1 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Thinking about writing some Database Tutorials

Posted 05 September 2008 - 10:18 AM

Hey everyone,

I was thinking about writing some tutorials on designing databases for projects that you're working on. I've been seeing a lot of issues lately that are based on poor database planning, and I was thinking about discussing the importance of defining relationships and entities. Is this something that the community could benefit from? I'd like to hear your thoughts before I put the time into it.
Is This A Good Question/Topic? 0
  • +

Replies To: Thinking about writing some Database Tutorials

#2 grimpirate  Icon User is offline

  • Pirate King
  • member icon

Reputation: 149
  • View blog
  • Posts: 714
  • Joined: 03-August 06

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 11:49 AM

I think there are plenty of database tutorials out there already. People are just too lazy to look them up or prefer to screw up and ask a question here. If you're going to write a tutorial for a database, write it for a database that is less well known (something like GladiusDB).
Was This Post Helpful? 0
  • +
  • -

#3 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 11:51 AM

Well I've noticed there's a lot of information on SQL and stuff like that. I'm looking at more of the design process. I've yet to find a great resource for how to properly plan out your databases.
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2983
  • Posts: 10,313
  • Joined: 08-August 08

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 12:13 PM

View Postgrimpirate, on 5 Sep, 2008 - 11:49 AM, said:

I think there are plenty of database tutorials out there already.

Yes there are, but most of them are like the man pages in Unix. They're great if you already know what you're doing, but they'll make your head spin if you need help getting started.

Anyone writing a tutorial should use tons of simple examples before showing anything complex. I'd also say that a simple example should always appear FIRST, with an explanation following.
Was This Post Helpful? 0
  • +
  • -

#5 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 01:19 PM

Should this not be in the "Database" forum? :rolleyes:
Was This Post Helpful? 0
  • +
  • -

#6 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 01:24 PM

Nah, this was targeted toward people writing in PHP but who are having secondary database issues. It wouldn't reach it's proper audience if it were sitting alone in the database forum.
Was This Post Helpful? 0
  • +
  • -

#7 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 01:36 PM

All to often people get a hold of MySQL and PHP and then say "Hey I have this cool stuff here, I should make something with it" so I definitely think the community could benefit from it. I would suggest that even before database design you should consider writing a tutorial for product planning and then move into the various aspects of developing an actual product. I think that would help with many of the problems you see when people do not properly plan and develop a product. But if you only want to write a tutorial for database design I say follow CTphpnwb's advice and KISS.

Bear in mind when I say (although technically I am writing)"product" I do not mean the latest greatest MMORPG it can be a simple two table one script product.
Was This Post Helpful? 0
  • +
  • -

#8 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 01:39 PM

Yeah I've written a bunch of tutorials on here and a good chunk of them have more than 1,000 views. I definitely understand the whole KISS approach. I actually wrote a blog post on the importance of planning, and had considered doing something of that sort. I e-mailed skyhawk about it to find out where to submit it to, so I'm just awaiting a reply. Thanks for the ideas.
Was This Post Helpful? 0
  • +
  • -

#9 capty99  Icon User is offline

  • i am colt mccoy
  • member icon

Reputation: 98
  • View blog
  • Posts: 10,081
  • Joined: 26-April 01

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 01:54 PM

Hey I would read that , just don't dumb it down too much.

PHP / SQL skills are up to par, but my planning goes down to jotting table names on a piece of paper with row types.
Was This Post Helpful? 0
  • +
  • -

#10 Nykc  Icon User is offline

  • Gentleman of Leisure
  • member icon

Reputation: 728
  • View blog
  • Posts: 8,642
  • Joined: 14-September 07

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 02:03 PM

I would be interested in reading those. All too often I try to plan some database projects and end up fumbling up the process.

Two perfect examples are a sports card collection database and a recipe database.

The tutorials I did find work to an extent but don't answer specific questions and problems unique to my situation. Poor planning mostly.

I worked with SQL and MySQL databases before, I don't need a beginners guide to querying a database - I need a how to on how to properly structure a database design.
Was This Post Helpful? 0
  • +
  • -

#11 Sonastylol  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 151
  • Joined: 15-December 07

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 03:22 PM

Written by DFOX of newgrounds.com in the PHP:Main topic, which is a compiled list of PHP/MySQL tutorials.

PHP: Database Structuring 101

Introduction

As of late, I've been helping a number of people with their PHP inquiries. A lot of those people had MySQL questions.

As I talked with these people, it came very clear to me that most people don't know how to properly structure a database. They don't know about relational data, unique ids, things like that.

So, if you're new to MySQL, you should read this to learn how to property structure your database.

Unique IDs, what are the used for, why should I use them?

Unique IDs, know as the primary key in your database table, are one of the most important concepts you need to understand when using databases.

What a unique ID is, is a number that is unique for each row in the table of a database. So each row has a different number, and usually these numbers go in sequential order. MySQL has a feature called "auto_increment". What auto_increment does is cause the primary key, your unique IDs to increment one value every time a row is inserted into that table.

Why is it so important to use unique IDs? Well, let's use this example. I'm making a movie database. Two movies can easily have the same name. Let's say I'm not using a unique ID for my primary field, by table would look like this:

------------------------------
|movie_name|movie_description|
------------------------------
|Titanic|Titanic movie from 1953 staring Barbara Stanwyck.|
------------------------------------------
---------------
|Titanic|The gripping tale of the Titanic from 1997 staring Leonardo DiCaprio|
------------------------------------------
---------------

So, we have a problem. What if I want to select the Titanic movie from 1997 and show it's description? There's two movies named Titanic, and I have no way of telling them apart.

Now, how would this have looked if we had a primary, unique ID?

------------------------------
movie_id (auto_increment)(primary)(int)|movie_name|
movie_description|
------------------------------
1|Titanic|Titanic movie from 1953 staring Barbara Stanwyck.|
------------------------------------------
---------------
2|Titanic|The gripping tale of the Titanic from 1997 staring Leonardo DiCaprio|
------------------------------------------
---------------

Now each movie has it's own unique number assigned to it. If I wanted to pull the data on the awesome 1997 Titanic, all I have to do is select movie_name and movie_description WHERE movie_id = 2. It's as simple as that.

So, you may be asking, how do you make an auto increment unique ID primary field? It's simple using PHPMyAdmin.

Simply add a new field with these settings:
Field = any name you want for your unique ID
Type = INT
Extra = auto_increment
Primary = selected

And you're all set!

Relational data, what is it, and how do I use it effeciently?

The way you store data is EXTREMELY important. Storing data in an impractical way can cause major problems with large systems.

First, I want to go over relational data. Let's say you have a users table in your database. That table has fields like username, password, location, email address, stuff like that. It should also have a unique auto increment ID like: user_id.

OK, so let's say we have our users table set up like this:

------------------------------------------
----------
user_id|username|password|location|email|
------------------------------------------
----------
3|DFox2|loldonthackme|Syosset|david@phpmai
n.com|
------------------------------------------
----------

OK, so that's my user record in the database. Let's say I'm making a portal, and users are going to be submitted tons of Flash content.

When a user submits a piece of content, I need to save that info in another table, called flashes. Here's the important part: IN flashes, I don't want to store the username of the user who submitted it. I want to store their USER ID as the creator, NOT username. Why? What if a user has submitted 100 flashes, and wants to change their username? I'm I going to change it in 100 rows? No, all I have to do is change the username in the USERS table, and it will take effect everywhere else as I have flash submitions tied to the user_id and NOT the username. User id will NEVER change.

So, my flashes table would look like this after I submit a flash:
------------------------------------------
----------
flash_id|user_id|flash_name|views|num_vote
s|
------------------------------------------
----------
1|3|Hop To The Top|324553|2100|
------------------------------------------
----------

It's as simple as that. That is relational data. The user_id within the flashes table relates to the users table as you can do a lookup of a user in the users table just by using the user_id in the flashes table. This is a very important concept, so please make sure you understand it.

Structuring your database properly

A few days ago, someone comes to me with a PHP question. They were making a feature on their site where people could make a favorites list in their account of their favorite flashes.

This is how they had their database structured:

------------------------------------------
----------
user_id|user_name|email|favorite_1|favorit
e_2|favorite_3|favorite_4|favorite_5|
------------------------------------------
----------

IT MADE ME WANT TO PICK MY DOG OFF THE FLOOR AND THROW HIM OUT THE window. Augh, it pissed me off. What if you want to have the user be able to select unlimited favorites???

I'm going to say this once. YOU DO NOT PUT REPEATING DATA IN COLUMS, YOU PUT IT IN ROWS. Soooo many people make this error. The proper way to do this is:
- Have a table called favorites
- Have it uses a relational field called user_id to tie it to a specific user
- Each favorite gets one row.

It would look like this with some data:
------------------------------------------
----------
favorite_id (auto_increment)|user_id|favorite_movie_id
------------------------------------------
----------
1|3|6
2|3|300
3|3|246
4|3|69
5|6|27
6|6|32

Then, if I want to select all of users 3 favorites, all I have to do is select from favorites WHERE user_id = 3

So, that's basically it. I hoped this helped you learn how to structure your databases.
Was This Post Helpful? 0
  • +
  • -

#12 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Thinking about writing some Database Tutorials

Posted 05 September 2008 - 07:46 PM

Thanks for the quick tutorial, but I'm thinking about writing something something that comes even before structuring your tables. I'm talking about defining entities and relationships, including one-to-one and many-to-many. I guess I'll be writing it over the weekend. Hopefully it'll be up by Monday. I'll keep everyone updated.
Was This Post Helpful? 0
  • +
  • -

#13 -fedexer-  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 44
  • Joined: 03-June 08

Re: Thinking about writing some Database Tutorials

Posted 08 September 2008 - 12:15 AM

Sounds like a plan :P I will definetly be looking at it when it is up, your reckoning you'll have it up today? (monday)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1