PHP Scripts

We have done a lot of Database work using PHP applications and nine times out of  ten we end up using an MS SQL server backend instead of the standard mySQL backend. MS SQL has so many plus points compared with mySQL, the biggest being speed, recovery, backup and database management.

We have, however, been testing with Oracle in the past month or so. As most of us here come from a DBA background and are used to using  Oracle in large Banking and Financial systems, we would choose Oracle at the drop a  hat if the  TCO  was in the right ball park. With the release of Oracle on windows, it has now come into the same ball park as MS SQL, and having  access to Oracles RAC would be a great advantage as it seems to be even better than MS SQL 2005 mirroring.

We will let you know how our testing gets on and we will hopefully have some benchmarks between MS SQL and Oracle 11g to show how they get on with PHP.

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.