PHP Scripts

So you want a database for your website, so where do you start?

When I started to write this, I thought just a few lines with all the big bullet points in would do but after hitting 4 pages in ms word , I decided to split it down so here is Part1

Database Design for the Internet 101 Part1

Firstly you need to decide what information you will need? Sounds obvious but you don’t want to go down the spaghetti programming methodology if you can help it as it will cause you problems later on. So the best place to start is a blank A4 piece of paper and create an ERD.

An ERD is a graphical representation of the Database, where you can see the Relationships between the data you want to store. Drawing it out has a lot of advantages as you can chop and change it about very easily at the start before you build the database.

In these diagrams you can use the infinity sign to indicate a 1 to many relationship and lines to link fields. So as you can see from the example diagram above for each Registration you can have multiple payments.

In the Payments table there is a field called RegistrationID which links to the RegistratioinID in the Registration table. And then in the Payments table there is a Payment type which links to the Payments type table.

So why layout the data like this? Its all about data integrity, take the above example, if every time a payment was made the user had to enter a card type there could be all sorts of variations and typos in the table for example “VISA”, “Visa”,” VISA”,”Visa “, “Bisa” so by adding a PaymentsType table, this gives them the ability to select from a know and verified list. Going through this process is database normalization, for a database to work well you want to get to what is called the third nominal form where no data is replicated.

There ends Part 1, Part to will cover more on data relationships and normalization.

This entry was posted on Sunday, July 6th, 2008 at 2:20 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.