Building a database driven site

Tips needed

[rubenski]

Hi everyone,

I want to build a completely database driven website. I have quite a lot of PHP and MYSQL experience, but I never dared to start a project like this. Now I do.

I designed a simple message board, some product comparison apps, a template system and a some smaller stuff. I even tried designing my own CMS which worked to a certain extent, bit I think I was on the wrong track with my database layout.

What would a good database design look like for a content rich website? What are important things to consider when starting a project like this? Could you provide me with some technical tips on this matter?

[Salsa]

Planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, planning, doing.

It looks like you're off to a good start, and I'm sure you'll get even better tips from others.

[moltar]

Draw the database structure out. Either use some software or do it on paper. I find paper works best for me for small to medium size projects.

Make sure your database is normalized - no data repeats itself. For example, you have a database of cars. You should make one table with car makes, and one table with car properties. Do not store actual car make in the car properties table, but store the ID of the row of the car make table.

Make as few queries per request as possible. Make use of joins.

Index tables. Play around with it. See which index length suits you best.

Use appropriate table column types. Think ahead about them. Do you really need an INT? Maybe SMALLINT will do just fine. Same applies to TEXT fields.

Avoid huge tables. Maybe you can split a table in two different ones based or properties?

Test your table design/structure on big data chunks. Test it on 100,000 rows, and 1,000,000 (depends on kind of data). Benchmark them all. Write them to a spreadsheet. Compare and see what works best. You can auto generate data randomly to fill up a table.