If at all possible, you should normalise information in your database using Codd's principles of database normalisation. Basically, this means that you don't duplicate information, store information that can be recalculated based on other fields, or store more than one piece of information in a single cell of your table.
This is all well and good until you want to store (for example) information about a person with a list of his children; the solution is to use two tables, with one table being joined to the other when you select your data out of the database.
That's what we call a "one to many" mapping, and the situation becomes slightly more complex when it goes a stage further and we have a "many to many" mapping. In such a situation, the best solution is usually to provide a third table known as a pivot table.
The subject came up during yesterday's
MySQL Course and I wrote
an example that I've posted to the
solutions centre here on this site. The example will appear on appropriate future courses, and the data is available for viewing on the
module description page.
(written 2004-09-22 06:38:55)
Associated topics are indexed under
S154 - MySQL - Designing an SQL Database System [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)
[375] Oops - I got my initial database design wrong - (2005-07-12)
[361] Binary Large Objects or bars - (2005-06-27)
Some other Articles
Almost like old timesTechnical Open HousePython is a fabulous languageBirth NoticeMySQL - Pivot tablesCall Centres, selling, and a pause for thoughtPosting 1000 letters!Dress CodeEvening classes to learn PHPPHP and natural sorting