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.

[landonmkelsey]

wouldn't there be a foreign key reference (to the second table) in the first table!

[redhead]

There is issue_id in the details table is a reference to the appropriate entrance in the issues table.
You dont need to explecitly tell *SQL its a foreign key, when your access to it is build uppon the knowledge of it beeing combined in this way.

It will open up for a solution where theres a details entrance which hasn't got any issues it derives from, perhaps a future fictional table entrance...

[landonmkelsey]

The question is "where is issue_id"...it could be in several tables

Here is sql to create 5 tables that are heavily connected!

The constraints are added at the bottom! Good reason for adding at the bottom!

reason: the inserts would not work!

Note how the constraints tell which table the target column is in!

I have downloaded a free tool to make UML/ERD diagrams out of sql, C++ etc.

I have diagrammed these 5 tables before and have a screen snapshot!

create table salesreps
 (empl_num int not null,
  name varchar(15) not null,
  age int,
  rep_office int,
  title varchar(10),
  hire_datetime datetime not null,
  manager int,
  quota float,
  sales float not null,
  primary key (empl_num ));

create table offices
 (office int not null,
  city varchar(15) not null,
  region varchar(10) not null,
  mgr int,
  target int,
  sales int not null,
  primary key (office));

create table customers
(cust_num   int not null,
 company varchar(20) not null,
 cust_rep  int,
 credit_limit  float,
 primary key (cust_num));

create table products
 (mfr_id char(3) not null,
  product_id char(5) not null,
  description varchar(20) not null,
  price float not null,
  quantity_on_hand int not null,
  primary key (mfr_id, product_id));

create table orders
 (order_num int not null,
  order_datetime datetime not null,
  cust int not null,
  rep int,
  mfr char(3) not null,
  product char(5) not null,
  qty int not null,
  amount float not null,
  primary key (order_num));
insert into salesreps values (109,'Mary Jones',31,11,'Sales Rep','12-oct-89',106,300000.00,392725.00);
insert into salesreps values (102,'Sue Smith',48,21,'Sales Rep','20-dec-86',108,350000.00,474050.00);
insert into salesreps values (106,'Sam Clark',52,11,'VP Sales','14-jun-88',NULL,275000.00,299912.00);
insert into salesreps values (104,'Bob Smith',33,12,'Sales Mgr','19-may-87',106,200000.00,142594.00);
insert into salesreps values (101,'Dan Roberts',45,12,'Sales Rep','20-oct-86',104,300000.00,305673.00);
insert into salesreps values (110,'Tom Snyder',41,NULL,'Sales Rep','13-jan-90',101,NULL,75985.00);
insert into salesreps values (108,'Larry Fitch',62,21,'Sales Mgr','12-oct-89',106,350000.00,361865.00);
insert into salesreps values (103,'Paul Cruz',29,12,'Sales Rep','01-mar-87',104,275000.00,286775.00);
insert into salesreps values (107,'Nancy Angelli',49,22,'Sales Rep','14-nov-88',108,300000.00,186042.00);
insert into salesreps values (105,'Bill Adams',37,13,'Sales Rep','12-feb-88',104,350000.00,367911.00);

insert into products values ('REI','2A45C','Ratchet Link',79.00,210);
insert into products values ('ACI','4100Y','Widget Remover',2750.00,25);
insert into products values ('QSA','XK47','Reducer',355.00,38);
insert into products values ('BIC','41672','Plate',180.00,0);
insert into products values ('IMM','779C','900-lb Brace',1875.00,9);
insert into products values ('ACI','41003','Size 3 Widget',107.00,207);
insert into products values ('ACI','41004','Size 4 Widget',117.00,139);
insert into products values ('BIC','41003','Handle',652.00,3);
insert into products values ('IMM','887P','Brace Pin',250.00,24);
insert into products values ('QSA','XK48','Reducer',134.00,203);
insert into products values ('REI','2A44L','Left Hinge',4500.00,12);
insert into products values ('FEA','112','Housing',148.00,115);
insert into products values ('IMM','887H','Brace Holder',54.00,223);
insert into products values ('BIC','41089','Retainer',225.00,78);
insert into products values ('ACI','41001','Size 1 Widget',55.00,277);
insert into products values ('IMM','775C','500-lb Brace',1425.00,5);
insert into products values ('ACI','4100Z','Widget Installer',2500.00,28);
insert into products values ('QSA','XK48A','Reducer',177.00,37);
insert into products values ('ACI','41002','Size 2 Widget',76.00,167);
insert into products values ('REI','2A44R','Right Hinge',4500.00,12);
insert into products values ('IMM','773C','300-lb Brace',975.00,28);
insert into products values ('ACI','4100X','Widget Adjuster',25.00,37);
insert into products values ('FEA','114','Motor Mount',243.00,15);
insert into products values ('IMM','887X','Brace Retainer',475.00,32);
insert into products values ('REI','2A44G','Hinge Pin',350.00,14);

insert into orders values (112961,'17-dec-89',2117,106,'REI','2A44L',7,31500.00);
insert into orders values (113012,'11-dec-90',2111,105,'ACI','41003',35,3745.00);
insert into orders values (112989,'03-mar-90',2101,106,'FEA','114',6,1458.00);
insert into orders values (113051,'10-feb-90',2118,108,'QSA','XK47',4,1420.00);
insert into orders values (112968,'12-oct-89',2102,101,'ACI','41004',34,3978.00);
insert into orders values (113036,'20-jan-90',2107,110,'ACI','4100Z',9,22500.00);
insert into orders values (113045,'02-feb-90',2112,108,'REI','2A44R',10,45000.00);
insert into orders values (112963,'17-dec-89',2103,105,'ACI','41004',28,3276.00);
insert into orders values (113013,'14-jan-90',2118,108,'BIC','41003',1,652.00);
insert into orders values (113058,'23-feb-90',2108,109,'FEA','112',10,1480.00);
insert into orders values (112997,'08-jan-90',2124,107,'BIC','41003',1,652.00);
insert into orders values (112983,'27-dec-89',2103,105,'ACI','41004',6,702.00);
insert into orders values (113024,'20-jan-90',2114,108,'QSA','XK47',20,7100.00);
insert into orders values (113062,'24-feb-90',2124,107,'FEA','114',10,2430.00);
insert into orders values (112979,'12-oct-89',2114,102,'ACI','4100Z',6,15000.00);
insert into orders values (113027,'02-jan-90',2103,105,'ACI','41002',54,4104.00);
insert into orders values (113007,'08-jan-90',2112,108,'IMM','773C',3,2925.00);
insert into orders values (113069,'02-mar-90',2109,107,'IMM','775C',22,31350.00);
insert into orders values (113034,'29-jan-90',2107,110,'REI','2A45C',8,632.00);
insert into orders values (112992,'04-nov-89',2118,108,'ACI','41002',10,760.00);
insert into orders values (112975,'12-oct-89',2111,103,'REI','2A44G',6,2100.00);
insert into orders values (113055,'15-feb-90',2108,101,'ACI','4100X',6,150.00);
insert into orders values (113048,'10-feb-90',2120,102,'IMM','779C',2,3750.00);
insert into orders values (112993,'04-jan-89',2106,102,'REI','2A45C',24,1896.00);
insert into orders values (113065,'27-feb-90',2106,102,'QSA','XK47',6,2130.00);
insert into orders values (113003,'25-jan-90',2108,109,'IMM','779C',3,5625.00);
insert into orders values (113049,'10-oct-90',2118,108,'QSA','XK47',2,776.00);
insert into orders values (112987,'31-dec-89',2103,105,'ACI','4100Y',11,27500.00);
insert into orders values (113057,'18-feb-90',2111,103,'ACI','4100X',24,600.00);
insert into orders values (113042,'02-feb-90',2113,101,'REI','2A44R',5,22500.00);

insert into offices values (22,'Denver','Western',108,300000.00,186042.00);
insert into offices values (11,'New York','Eastern',106,575000.00,692637.00);
insert into offices values (12,'Chicago','Eastern',104,800000.00,735042.00);
insert into offices values (13,'Atlanta','Eastern',105,350000.00,367911.00);
insert into offices values (21,'Los Angeles','Western',108,725000.00,835915.00);
insert into offices values (45,'Dallas','Western',108,725000.00,835915.00);
insert into offices values (41,'Houston','Western',108,725000.00,835915.00);
insert into offices values (62,'Galveston','Western',108,725000.00,835915.00);
insert into offices values (29,'San Antonio','Western',108,725000.00,835915.00);
insert into offices values (49,'Austin','Western',108,725000.00,835915.00);


insert into customers values (2111,'JCP Inc.',103,50000.00);
insert into customers values (2102,'First Corp.',101,65000.00);
insert into customers values (2103,'Acme Mfg.',105,50000.00);
insert into customers values (2123,'Carter and Sons',102,40000.00);
insert into customers values (2107,'Ace International',110,35000.00);
insert into customers values (2115,'Smithson Corp.',101,20000.00);
insert into customers values (2101,'Jones Mfg.',106,65000.00);
insert into customers values (2112,'Zetacorp',108,50000.00);
insert into customers values (2121,'QMA Assoc.',103,45000.00);
insert into customers values (2114,'Orion Corp.',102,20000.00);
insert into customers values (2124,'Peter Brothers',107,40000.00);
insert into customers values (2108,'Holm Landis',109,55000.00);
insert into customers values (2117,'J.P. Sinclair',106,35000.00);
insert into customers values (2122,'Three-Way Lines',105,30000.00);
insert into customers values (2120,'Rico Enterprises',102,50000.00);
insert into customers values (2106,'Fred Lewis Corp.',102,65000.00);
insert into customers values (2119,'Solomon Inc.',109,25000.00);
insert into customers values (2118,'Midwest Systems',108,60000.00);
insert into customers values (2113,'Ian and Schmidt',104,20000.00);
insert into customers values (2109,'Chen Associates',103,25000.00);
insert into customers values (2105,'AAA Investments',101,45000.00);

alter table customers
  add constraint hasrep
  foreign key (cust_rep)
  references salesreps
  on delete no action;

alter table offices
  add constraint hasmgr
  foreign key (mgr)
  references salesreps
  on delete no action;

alter table orders
  add constraint placedby
  foreign key (cust)
  references customers
   on delete no action;

alter table orders
  add constraint takenby
  foreign key (rep)
  references salesreps
   on delete no action;

alter table orders
  add constraint isfor
  foreign key (mfr,product)
  references products;

alter table salesreps
  add constraint dummy
  foreign key (manager)
  references salesreps
  on delete no action;

alter table salesreps
  add  constraint worksin
  foreign key (rep_office)
  references offices
  on delete no action;

[DJMaze]

Originally Posted by landonmkelsey

The constraints are added at the bottom! Good reason for adding at the bottom!

reason: the inserts would not work!

Bad reason!
What if you need to insert a new record later on?
You delete the constraints and put them back afterwards?

Good luck with getting back to the drawing table!

My solution to the 5 year old topic: 2 tables but with nested/threading support.
This allows people to reopen a ticket and split it in parts

[landonmkelsey]

Originally Posted by DJMaze

Bad reason!

Good luck with getting back to the drawing table!

let us stay scientific and concentrating mainly on the original problem.

After adding the constraints, one may insert/delete/update at will!

Adding the constraints with the create makes for a mess

and disallows the inserts..try it!

What database(s) do you have at your fingertips?

The script above works! Try the inserts with the constraints added!

Having 2 unconnected tables means that one could have used an STL map

and avoided a relational database. My brother worked at FX Mortgage in Dallas

where they had just one table! This still allows advantages such as 3rd party software!

[landonmkelsey]

The reason the inserts don't work if constraints are added with the create statements is:

Obvious logic ! The constraints would refer to tables not in existence yet!

In this example, table a would refer to table b not yet created!

Have a nice day!

[DJMaze]

Originally Posted by landonmkelsey

Obvious logic ! The constraints would refer to tables not in existence yet!

In this example, table a would refer to table b not yet created!

Right!

1. create tables
2. commit
3. create constraints
4. commit
5. insert data

As i said "going back to the drawing table" means to redesign anything. In this case it's your sql script