Oops - I got my initial database design wrong
Archive - Originally posted on "The Horse's Mouth" - 2005-07-12 13:09:59 - Graham EllisJust once in a while a question comes up on our Opentalk Forum that's so good and of such general interest that a longer answer is worthwhile. Such a question came up yesterday ... and elegantly asked and worded too, so it was a delight to read and answer.
You want a simple database application? Great - so you write the application using MySQL (or some other database) and you hold your data in a single table. Works well at first but then ... the design starts to creak at the seams. Perhaps you have a number of customers listed, and some of them make multiple purchases ... your database starts to contain a whole lot of repeated information which makes it hard to edit when someone changes their address, for example ...
[b]It's so easy to design a database in this way, but how can it be sorted out later?[/b] It's not going to be easy - but I've set down a scheme, with various options, and a piece of PHP to do the task automatically ... and you can find that in the forum archive
Beware - "normalising" your tables - changing from one table to two of more appropriate design - is likely to take you quite a while as you match up entries that aren't quite identical in their common fields but should be, and you're going to have to modify and add an element of complexity to your applications ... they'll have to update multiple tables rather than just one as data is being entered and changed, and they'll need to use MySQL Joins to connect the tables.