DB Design Question Part II

[sde]

I'm really trying to do it right the first time ( or second since this is a re-design of a similar system ).

Once again, I'm working on a php/mysql web app for a tech support center which integrates web support, phone support, and an online knowledge base.

This question pertains to 2 tables which will be the heart of the design. Well the question is actually to make these 2 separate tables, or make it just one.

First I'll tell you about the current layout, .. then I'll talk about the newer layout that I thought of last night.

Layout 1

Currently it works a bit like the Thread/Post design of this forum, except i'm using Issues/Details.

Everytime there is a new issue, it adds a row to the Issue table with the topic information and customer id, .. however the details of the issue gets entered in the Details table related to the Issue table by the Issue ID.

The purpose of this is so that there can be multiple details relating to the same issue in the case that the problem was not solved in the first call.

This is what the schema looks like for these 2 tables now:

CREATE TABLE issues (
  issue_id int(10) unsigned NOT NULL auto_increment,
  contact_id int(10) unsigned default NULL,
  group_id smallint(5) unsigned NOT NULL default '0',
  topic varchar(100) default NULL,
  presales varchar(20) default NULL,
  PRIMARY KEY  (issue_id)
)

CREATE TABLE details (
  detail_id int(10) unsigned NOT NULL auto_increment,
  issue_id int(10) unsigned NOT NULL default '0',
  open datetime default NULL,
  closed datetime default NULL,
  agent_id int(10) unsigned NOT NULL default '0',
  c_detail mediumtext,
  a_detail mediumtext,
  status varchar(10) NOT NULL default 'open',
  PRIMARY KEY  (detail_id)
)

Layout 2

Last night I was thinking of another Idea using only the Details table with a 'parent_id' field.

Every detail with a Parent ID of '0' would be the equivalent to the Issue. Then of course everything with a parent_id that is not 0 would just be a child.

Trade offs would be that I will have more fields in the Details table that would just have to be NULL when the Parent ID is not '0' .. fields that would just end up containing redundant data such as contact_id, group_id, topic, and other information that will remain the same througout every detail of 1 issue.

Keep in mind that I need to optimize this for being able to query the most specific information as opposed to performance . .. however performance is nice too.

----------------------------

If you've read this far I really thank you and hope you will share your thoughts =)

Thanks!

[technobard]

I would go with the 2 table design. You have a classic 1 to many relationship (a header table and it's corresponding details table). You can implement it with one table, but unless you actually need a tree structure (i.e. more than one level below a parent), there really is no benefit.

The 2 table design is a lot easier to maintain and expand. Also in a database that supports foreign keys, you can even enforce referential integrity. (i.e. no child can exist without a parent or in this case, no details can exist without an issue)

A lot of developers who find themselves doing the database design as well as code, create a database specifically for the problem at hand. The fewer tables, the reasoning goes, the better. Don't fall into that trap! It is well worth reading an article or two on normalization. A good data model should address 1) current needs, 2) future (expected) needs, and 3) as much as possible, unexpected needs. A normalized model gives you the most flexibility in terms of adding new features. That doesn't mean you can't "denormalize" some things for performance, but it is better to normalize first and then decide where it makes sense to make changes.

Good luck.

[sde]

thanks, .. i do appreciate the insight

[berklee]

Actually, having designed at least 30 of these before from scratch (I do code for call centers), I have to say YES. For two reasons:

1) Companies change their ideas of what they want all the time, particularly once the app gets in their hands. This allows for either the one-to-one, or one-to-many relationship without killing yourself down the road.

2) A tech support company that is doing high volumes of calls is probably going to need to report on their issues, especially in places like the manufacturing industry (i.e. - how many people call because they don't know how to change a drive belt on our vacuum cleaners). By removing the large text fields from your issues table, which contains the data that can be averaged/grouped (unlike the 'notes' field you've got), you're going to speed up any and all reporting on issue for that company.