Re: When does using multiple tables make sense?

Liam Friel wrote:

2008/10/20 Lester Caine <lester@lsces.co.uk <mailto:lester@lsces.co.uk>>

    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