Page 1 of 1

Database design

Posted: Fri Jan 30, 2004 5:32 pm
by Unipus
I'm trying to code up a problem management system. In a lot of ways, it will work very similar to a forum. But there's some key differences, and it's giving me mind-drain trying to work out the schematic in my head.

First of all, all posts are related to an Item #. There can be a small but variable number of problems flagged with any given Item #, for instance it might need a price change, a description corrected, or it might need to be deleted altogether. Obviously, being deleted is a solo condition, but most of the other conditions can be combined with each other.

However, each individual problem needs to be handled individually within the scope of that item. Once a problem has been posted, other people need to be able to respond to it. What this initially says to me is that each type of problem needs its own table, and that each of these tables simply needs to reference the same item #.

Okay, great, but then I have to query anywhere between half a dozen and twenty tables for each item # that has a problem, and that doesn't sound so good to me. It also introduces a new problem with how I want the user-interface to work.

So... reactions? Ideas?

Posted: Fri Jan 30, 2004 6:37 pm
by microthick
I remember you posted something about this earlier.

I really don't think you'd need to have a new table for each type of problem.

On a short stint for the gov't, I developed a call center support system for them that had a variety of types of built-in problems. Although each problem had to be handled differently by the software, we didn't have different tables for each type of problem.

You should probably try and have one Problem table that tracks all problems. Depending on a switch of some sort, if you need to track extra criteria not stored in that table, then store that data in another table specifically for that type of problem, referencing the id from the main problem table.

It sounds like an interesting app you're working on. Best of luck.

Posted: Fri Jan 30, 2004 6:45 pm
by Unipus
This is actually not for the same project as the other one. The other one is through now. It's obvious that I'm gonna have to take some time this weekend and sketch this out a little better. The Answer keeps eluding me. But, if you ever want a handful of flawed database designs, each with different problems, I could make that happen!

Any additional help still appreciated.

Posted: Fri Jan 30, 2004 7:08 pm
by microthick
If you end up sketching any system flow charts or uml-type diagrams, I'd be interested in seeing them. Tough problems are always fun to look at.

Posted: Fri Jan 30, 2004 9:33 pm
by jaxn
I am not sure what RDBMS you want to use but...

Here is how I would do it in Postgres.
First off, the ID is a sequence.
Second create a table called "problems"
In the problems table have the following fields:
- ID
- type
- description
- date
- status

etc, etc. Pick any fields that are going to be present for all types of issues. Make the primary key the ID and the type (since you can't change the price twice).

then for each issue create a table like this:
CREATE TABLE price_changes () INHERITS problems;

It will have all of the fields in the problems table (but none of the constraints so you will want to recreate those). You can then add any special fields to the price_changes table that are unique to price change problems.

So why is this a good idea?

the ID is for the problem or group of problems. Even though you insert price change problems into the price_changes table, you can select all problems for a given ID like this:
SELECT * FROM problems WHERE ID='$id'

Because of price_changes INHERITS problems, selecting from problems will select from price_changes.



Hope that spurs some ideas for you.

-Jackson

Posted: Fri Jan 30, 2004 10:59 pm
by McGruff
This tutorial might be what you are looking for.

Possibly you might try (issues = items):

posts table
pid | issue_id | etc

issues table
issue_id | etc

type table
type_id | name | description (verbose) | etc

.. and then any join tables you might need.

All the first cols are auto-increment integer (one of..) cols.

A type table would be more useful the more there ARE a "normal" list of issue types. If you find yourself adding new types for each new issue, it wouldn't have much of a purpose.

I wouldn't worry about multi-table JOIN queries. Set it up, test it, and THEN see if there is any need to optimise. Use EXPLAIN to see how well the JOINs are operating, and make sure that cols used to link tables are EXACTLY the same type. The mysql manual has the details (if that's what you're using).

Postgres could have advantages (re mysql): it's usually better to do as much as possible with the query logic - not a hard and fast rule though.