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 <japruim@xxxxxxxxxx> To: php-db@xxxxxxxxxxxxx Date: Mon, 20 Oct 2008 07:59:36 -0400 Subject: 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@xxxxxxxxxx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
