how do you know how to design a mysql database when creating an advanced php application?

Question

i've never created a shopping cart, or forum in php. aside from viewing and analyzing another persons project or viewing tutorials that display how to make such a project or how to being such a project. how would a person know how to design the database structure to create such a thing? im guessing its probbably through trial and error...

Answer 1

you should read up and understand the basics of normalization. for most projects, normalizing to 3rd normal form will be just fine. there are always certain scenarios when you want more or less normalization, but understanding the concepts behind it will allow you to think about how your database is structured in a normalized format.

here's a very basic example of normalizing a table:

students
  student_id
  student_name
  student_class
  student_grade

a pretty standard table containing various data, but we can see some issues right away. we can see that a student's name is dependant on his ID, however, a student may be involved in more than one class, and each class would conceivably have a different grade. we can then think about the tables as such:

students
  student_id
  student_name

class
  class_id
  class_name

this is not bad, now we can see we have various students, and various classes, but we haven't captured the student's grades.

grades
  student_id
  class_id
  grade

now we have a 3rd table, which allows us to understand the relation between a particular student, a particular class, and a grade associated with that class. from our first initial table, we now have 3 tables in a normalized database (let's assume we don't need to normalize grades any further for sake of example :) )

a few things we can glean from this very basic example:

• our data is all tied to a key of some sort (student_id, class_id, and student_id + class_id). these are unique identifiers within each table.
• with our keyed relations, we're able to relate information to each other (how many classes is student #4096 enrolled in?)
• we can see our tables will not contain duplicated data now (think about our first table, where student_class could be the same value for many students. if we had to change the class name, we'd have to update all the records. in our normalized format, we can just update class_name of class_id)

Answer 2

The main technique you can learn about database design is called Database Normalization.

Database normalization has it's limits, especially if you have many transactions. At some point you may be forced to Denormalize.

But imho it's always better to start with a normalized database design.

Answer 3

I would also recommend using a visual editor for creating your database schema. I have recently been using: http://dev.mysql.com/workbench/

Once you create a database design, have someone with more experience look it over and give you feedback. But know that as much time as you can spend designing, you will eventually have to do implementation and will find out you are missing something or could do an even better job doing it another way.

So, design, get feedback, but don't be afraid to change.

Answer 4

Great information from Owen there, thanks. I can't Vote Up because my reputaion is low. :) Also thanks rlorenzo, for that link, it'll be a great help for planning future databases.