When does using multiple tables make sense?

Good morning everyone!

Hope you all have your coffee in hand when reading this :)

In case it makes a difference, It will be written in PHP 5.2.6 and
using mysql 5.0.51a-community with apache 2.2.9.

I am in the planning stages of a project that will turn into a
customer relation management system. I know there are quiet a few out
there, but I may need to be able to tie it into some accounting
software that we use here so I need to write my own... Besides, I get
paid to do it :)

Now... the meat of the question... When does it make sense to use
multiple tables? I am going to have the name/contact info for the
business, codes for when we contacted them last and how (Phone, e-
mail, postal mail, etc. etc), I also want to track the history of
changes made to each record.

Right now, I am thinking 3 tables, 1 with the name, address, phone,
etc. on it. a second with the contact codes, and a date. And the third
for keeping track of the changes.

I started thinking about it though... and I could have at least 6
tables, Address/contact info. Contact codes. Changes. Customer
History. Sales Rep Info. Access Control...

Right now the organization is small, only 9 full time employees
including production, so it's not a huge deal, but I'm hoping this
will be something that will help grow the company and we will have
sales people all over the world :)

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).

I am also considering writing it as a module system so that I can just
plug things in as needed, I could also then get it up and running
faster I think..

Was anyone able to determine what I am really asking in this long mess
of words? :)

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
11287 James St
Holland, MI 49424
www.raoset.com
japruim@raoset.com





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: When does using multiple tables make sense?

------=_Part_104766_8700008.1224516266121
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Mon, Oct 20, 2008 at 7:59 AM, Jason Pruim wrote:

> Good morning everyone!
> [snip][/snip]
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
I would suggest breaking the client and contacts into two tables. Its likely
that the client info will not change often, but people will changes roles
much more frequently. Or you may have multiple people for multiple areas for
larger clients

--

Bastien

Cat, the other other white meat

------=_Part_104766_8700008.1224516266121--

Re: When does using multiple tables make sense?

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.

--
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

Re: When does using multiple tables make sense?

Liam Friel wrote:
> 2008/10/20 Lester Caine >
>
> 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