Liam Friel wrote:
2008/10/20 Lester Caine <lester@xxxxxxxxxxx <mailto:lester@xxxxxxxxxxx>>
Jason Pruim wrote:
So my question is... When is it best to use more tables? All the
info will be related to each other, so I think I would be
looking at either a many-to-many relationship, or a many-to-one
relationship (still figuring that out).
One thing that I've realised make sense is to have a 'sub-table' for
things like phone number, email, fax and the like. All too often we
have two phone numbers or different email addresses, so a four field
table with
ID number, type of info, info, note
This way one can add as many info fields of any different type to a
client/contact record. The type of info field flags things like
primary phone.
Address details often need the same treatment as well, but I use UK
post code as a key for the bulk of that information so it just goes
into a another info field.
I usually like to think of multiple tables in terms of - how many of
this type of data will the users need? If it is a set number i.e. users
should only have name then I would put it in a customer table. If this
type of data may have many entries i.e. user uploaded images (they can
have any number), then I would use a different table to store the images
or information along with a reference to which user they belong.
This practice stops redundant data and using uneccessary space in your
database being used. For example: if you wanted to have 10 fields for
user images and you put them in the contact table, users that do not use
the 10 image fields will be wasting space. whereas if they are in a
related seperate table, only space is used for images that have been
uploaded. Using PHP you would do the necessary validation to check the
number of images etc a user was allowed.
so in short - if a type of data you are inputting has an unknown number
of results - it is best to put it in another table: it is also known as
normalisation.
I think that is more or less what I said ;)
One of my areas of interest is genealogical data, and there can be several
areas where some 'individual' records have no data and others can have a large
number. Even 'date of birth' may be something that is not a simple date ;)
Almost as soon as you put a field in the main index table, there will be an
exception to the rule :)
--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
