First I have to give the caveat... there will be different thoughts on the
best way to handle this. Some will be more right than others. I'm not
going to pretend I'm the DB expert, but I've seen quite a few different
schema styles in my day. I've seen ones that made sense to me and others
that seemed overly complex but may have been the right way for someone
(or for some era of DB programming.. but may not be applicable anymore).
With all that said, here are some thoughts:
It seems to me that you're on the right track. A customer table with data
that's specific to that customer. A table for keeping a log of contact
with that customer and then another table to keep track of changes.
You might also have another table with codes to use in your contact table
indicating the purpose of the contact.
Some people may want to go crazy with the lookup tables and have a table with
states/provinces and link that to an ID in your customer table for the
state/province. I think that's overzealous, myself, and I haven't seen too
much of that kind of insanity.. but every now and then you see it.
That may be a best practices situation because you're saving spaces in the
database (in theory) because MI takes up more space than 39, but the
cost savings is probably offset by having another table and doing the
proper joins. Plus it makes it more confusing to programmers, I think.
Here's how I approach DB schema design:
1. Create lookup tables for commonly used codes. Typically there are a few
things that aren't going to change much once they're entered and can
potentially be used in multiple tables.
2. Create flat tables that will grow at a slow to medium pace (maybe fast,
but not as fast as #3). These will be things like your customer list.
3. Create tables for things like data change (or other) logs.
4. Lastly, you may need cross reference tables that link one or more tables.
Between #2 and #3, you may just use a foreign ID in the log type table
linking it to a record in #2, but if you have a list of customers and a
list of categories they afll into, Customer #1 may be in category A, B, C
and Customer #2 may be in category B, C, D. A cross ref table would allow
you to have two customers in categories B and C without getting too messy.
Trying to do this in either the Customer or Category table would be
cumbersome, trying to stuff multiple codees into a single field. Some
people may like to do that, but doing substring searches on data.. even
set searches probably.. seems to be more load on the server than you need
to put on it.
Anyway, just some thoughts. See if anyone else responds, do some reading,
etc. Sounds like you're in a good position since your company is small.
Probably can't do too much that's going to cause a mess in the future. hah
Good luck!
-TG
- Original Message -
From: Jason Pruim [EMAIL PROTECTED]
To: php-db@lists.php.net
Date: Mon, 20 Oct 2008 07:59:36 -0400
Subject: [PHP-DB] 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
