32dd Oops - I got my initial database design wrong
Training, Open Source Computer Languages

Search for:
Print friendly page
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Oops - I got my initial database design wrong

Just 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.
(written 2005-07-12, updated 2006-06-05)

 
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S154 - MySQL - Designing an SQL Database System
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

Q916 - Object Orientation and General technical topics - Database design principles
  [572] Giving the researcher power over database analysis - (2006-01-22)


52c6
Back to
Instant availability
 Previous and next
or
Horse's mouth home		 Forward to
What brings people to my web site?
Some other Articles
Getting the lighting right
A word of admiration for the London cabbie
A Strengthened City
What brings people to my web site?
Oops - I got my initial database design wrong
Instant availability
Vik, Iceland to Melksham, England
Time calculation in PHP
The training team that's looking out for you
From Iceland
4106 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).
You can Add a comment or ranking to this page
Public Training Courses
Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??

Related short articles
Perl design patterns example
Cacheing class for Python - using a local SQLite database as a key/value store
Looking for Creative Design, Catering and Transport inputs to help with Melksham Campus
SQL databases from Python - an SQLite example
Which database should I use? MySQL v SQLite
Rooms ready for guests - each time, every time, thanks to good system design
Caching Design Patterns
2011 Census results - initial figures for Wiltshire.
Some traps it's so easy to fall into in designing your web site
An easy way to comply with the new cookie law if your site is well designed
Learning C++ - a design pattern for your first class
The goto statement in Lua
Defining database relations in your Django model
Design Patterns - what are they? Why use them?
Designing your application - using UML techniques
Defining, declaring and initialising variables in C
Initial thoughts - response to GW Rail Franchise Consultation for Wiltshire
Sharing the user experience - designing a form with the customer in mind
From flowchart to program - code design for the newcomer
MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP
Exceptions - a fail-safe way of trapping things that may go wrong
Loops - a comparison of goto, while and for
Specification, Design, Implementation, Testing and Documentation - stages of a (Java) programming project
Finding your big files in Perl - design considerations beyond the course environment
Do I need to initialise variables - programming in C, C++, Perl, PHP, Python, Ruby or Java.
Files or Databases? MySQL, SQLite, or Oracle?
Oops - I typed ci not vi, and have lost my file ...
Perl - database access - DBD, DBI and DBIx modules
Databases - why data is split into separate tables, and how to join them
How to display information from a database within a web page
Coincidence, or Conspiracy - a wrong email address
Using an exception to initialise a static variable in a Python function / method
Initial handling of phone calls and walk in visitors
Getting the OO design write - with PHP a example
Upload Image, Store in database, redisplay in browser. PHP and MySQL
PHP - Object Oriented Design in use
Connecting Python to sqlite and MySQL databases
Freedom of Information - consideration for web site designers
Twelve skills / knowledges needed for the design of a web site
A better design of mouth
Using print_r in PHP to explore mysql database requests
Checking the database connection manually
Object Oriented programming - a practical design example
Making variables persistant, pretending a database is a variable and other Perl tricks
Designing your data structures for a robust Perl application
Automatically initialising Lua variables the first time
How do I query a database (MySQL)?
Application design in PHP - multiple step processes
Designing a heirarcy of classes - getting inheritance right
Get it right ... if it goes wrong, it takes so much effort to sort out!
Bean Classes in Java and Java Database Connections
Looking for a career change - Physician to Web Site Designer
Setting up a MySQL database from PHP
Database connection Pooling, SSL, and command line deployment - httpd and Tomcat
Keeping PHP code in database and running it
Getting hold of the wrong end of the stick
MySQL database from Ruby - an example
Hiding a MySQL database behind a web page
Web Bloopers - good form design - avoiding pitfalls
What have iTime, honeytrapagency and domain listing center got in common?
Pointing all the web pages in a directory at a database
Uploading to a MySQL database through PHP - examples and common questions
What have hotels and bananas got in common?
The wrong way to put up prices
Using PHP to upload images / Store on MySQL database - security questions
Using a MySQL database to control mod_rewrite via PHP
GUI design - Sketch it out first! (Java / Swing example)
Good Programming practise - where to initialise variables
A pu that got me into trouble
The Psyche is all wrong.
HTML tables - telling whats wrong from the display
The Fag Packet Design Methodology
Viewing images held in a MySQL database via PHP
Ruby course - oops - it's not happening
Think about your design even if you don't use full UML
Using a MySQL database from Perl
Been on a course, but still not got it?
Design your day with a walk
Combining similar rows from a MySQL database select
Design - one name, one action
4a8
© WELL HOUSE CONSULTANTS LTD., 2013: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/375_Oops ... wrong.html • PAGE BUILT: Mon May 27 06:13:46 2013 • BUILD SYSTEM: wizard
0