Database Design Question

[sde]

i designed and maintain a php/mysql app for a tech-support department to track inbound calls.

the system has been up and running great for about a year now.. but i am doing a re-design to optimize the database and add a few more features.

when a caller calls in, usually they are calling about 1 product.. however there are cases where they will call about 2 products.

the product field separates multiple products with commas .. this is fine when i'm using php to extract data, however when using other programs such as crystal reports, i can't get an accurate query because of course they can't explode a field, then reference the product name from the products table.

if you're not following, don't worry, but here is my question.

the solution to the problem is to just make another table that consists of 2 fields. "issue_id" & "product_id".

i suppose it's not that big of a deal, but it just seems like i'm running a mysql table ranch with all the little tables i'm adding to optimize for more precise queries.

is it better to have more tables? i suppose i can answer my own question by saying "if you want that level of precision" .. but i'm interested what others think.

[palin]

sounds like a speed or size tradeoff, So look at your constraints most likely diskspace as the only one that comes to mind at the moment. so pick the one that is more important.

my other thought is would it be more correct to have to incidents reporte seperaterly on the one call? one for each product? This is a procedure change.

[sde]

issues are logged separately.. however, often the issue is using 2 pieces of our products together.

thanks for the input =)

[palin]

would it be better to implement a way to track the relationships between products in the support calls? so that the designers can look at this for future products. Maybe it already does this so I'm not sure.

[sde]

with the new design that would pretty much be implimented.

[technobard]

A little late (I haven't been around much lately), but I'll add my two cents. More tables makes sense when it solves a problem or avoids one. This seems to qualify. The extra space is negligible. I'm a long time database guy, so I have to resist talking about the benefits of normalization and other stuff most people aren't really interested in. But I will say this, the tradeoff is always between flexibility and performance. That doesn't mean you will have bad performance if you make the design flexible, but flexibility usually means you're joining to another table.

[palin]

so I have to resist talking about the benefits of normalization and other stuff most people aren't really interested in.

I'm interested and I'm sure others are too. Who knows it may help someone later on.

[technobard]

Okay. There are books on the subject, but there are basically two main benefits to normalizing a database design: (1) eliminating redundant data and (2) eliminating inconsistency. I would also add flexibility as I mentioned in an earlier post.

(1) Redundant data: This basically means that information is repeated unnecessarily. Let's say you have a list of products. You're smart, so each product has a product_id and a product_description. In your design you have a SALES table which holds among other things each product_id along with it's product_description. Let's also say that you have a total of 35 different products at the moment. If you were to make a separate PRODUCT table, you would only need to store the product_id in the SALES table. Repeating the description over and over again for the 35 unique products is unnecessary.

(2) Eliminating inconsistency: This is closely related to having redundant data. It really refers to how you maintain your system. If we go back to product_description, there is a good chance that it is stored in other tables...say INVENTORY for example. If the description for product_id 23001 changes, you have to update every table that includes the product_id and product_description combination. If you update some, but not others, your data becomes inconsistent. Over time, it may not be clear which product_description is the correct one.

So, having data in a single master table (in this case, PRODUCT) requires less storage over all and makes maintenance a lot easier. Changing the product_description for a product_id 23001 means updating a single row.

An obvious consequence of all this is that to display product_description in the "normalized" version requires joining to the PRODUCT table. All other things being equal, a two table query will always be slower than a comparable one table query. Slower does not have to mean slow, however. Indexing both tables on product_id will minimize the amount of extra work the database has to do to retrieve the product_description. Normalization alone is not enough. You also need to take advantage of the performance enhancing features of the particular database environment.

There are different degrees of normalization. So called third normal form is a very popular stopping point for a lot of database designers. You'll also see BCNF (Boyce-Codd Normal Form) mentioned which falls between third normal and fourth normal forms. Search for any of these terms in google for a more complete description. There are tons of articles, books, etc. on the subject. If the author starts to talk in mathematical terms about relational databases, skip it and find another article. You want plain english. You can always come back to the other articles if you're at all interested after you actually understand what's going on.