Database Design question

[Sphexi]

I'm working on a very basic call tracking system at work, so we can specify what kind of calls we're getting throughout the day. I want to make it a branched type of system, so you would have several level 1 options, you select it and go to the level 2 options JUST for the one you chose, then so on to level 3.

If there's 4 level 1, and 4 for each of those, then 4 for each of those, you're looking at 4 + 16 + 64 = 84 total options when you drill down through the tree.

My problem is how do I design the database to hold these? I could just hardcode them into my page, but I'd like to give my manager the ability to create a new one, insert it to the database, and have it automatically show up.

What's the best way to design the database? I was thinking a table for each of the levels, and for each option it would have the text, plus what previous option had to be chosen at the prior level to trigger it. I'm sure I could code a loop for that pretty easily, I'm just new to database structures like this (usually handle standard ecom inventory databases).

 

[lozina]

Forgive me if I am misunderstanding but this sounds like a standard parent/child relationship

Which means it could be accomplished with one table:

ID.........PARENT_ID...........OPTION TEXT
1...........NULL.....................Main Menu 1
2...........NULL.....................Main Menu 2
3...........NULL.....................Main Menu 3
4...........1..........................1's Sub Menu 1
5...........1..........................1's Sub Menu 2
6...........2..........................2's Sub Menu 1
7...........2..........................2's Sub Menu 2

etc... So here you have a main menu with 3 options (Main Menu 1/2/3). Under option 1 you have two options (1's Sub Menu 1/2) and under option 2 you also have 2 options (2's Sub Menu 1/2) and so on...

and of course you can add whatever additional info you want to track for each option in additional columns. Maybe a column that counts how many times that option was hit, for example.

 

[Bulldog13]

Originally posted by: lozina
Forgive me if I am misunderstanding but this sounds like a standard parent/child relationship

Which means it SHOULD NOT BE accomplished with one table:

LevelOne
ID.........OptionText
1...........Main Menu 1
2...........Main Menu 2
3...........Main Menu 3
4...........Main Menu 4

LevelTwo
ID.........LevelOneID.........OptionText
1...........1........................Links to LevelOne ID 1
2...........1........................Links to LevelOne ID 1
3...........2........................Links to LevelOne ID 2
4...........3........................Links to LevelOne ID 3
5...........3........................Links to LevelOne ID 3
6...........4........................Links to LevelOne ID 4

LevelThree
ID.........LevelTwoID.........OptionText
1...........1........................Links to LevelTwo ID 1; Links to LevelOne ID 1
2...........2........................Links to LevelTwo ID 2; Links to LevelOne ID 1
3...........3........................Links to LevelTwo ID 3; Links to LevelOne ID 2
4...........3........................Links to LevelTwo ID 3; Links to LevelOne ID 2
5...........6........................Links to LevelTwo ID 6; Links to LevelOne ID 4
6...........6........................Links to LevelTwo ID 6; Links to LevelOne ID 4

LevelFour
ID.........LevelThreeID.........OptionText
1...........2........................Links to LevelThree ID 2; Links to LevelTwo ID 2; Links to LevelOne ID 1
2...........2........................Links to LevelThree ID 2; Links to LevelTwo ID 2; Links to LevelOne ID 1
3...........4........................Links to LevelThree ID 4; Links to LevelTwo ID 3; Links to LevelOne ID 2
4...........4........................Links to LevelThree ID 4; Links to LevelTwo ID 3; Links to LevelOne ID 2
5...........5........................Links to LevelThree ID 5; Links to LevelTwo ID 6; Links to LevelOne ID 4
6...........6........................Links to LevelThree ID 6; Links to LevelTwo ID 6; Links to LevelOne ID 4

Google Primary keys, foreign keys, and inner joins.

 

[BoberFett]

Wow Bulldog, that's a bad design. Too many tables, requires new tables if you need new levels, additional queries at each level rather than a single query that can be used recursively by the client. You need to read up on hierarchical SQL.

 

[Bulldog13]

As far as I can tell MySQL does not support Recursive CTEs and from his title his requirement is MySQL.

I do not believe it to be bad design, it is perfectly normalized. Neither is 4 tables too many. Or 6, Or 10. Again, he said it only needed 4 levels. Program the requirement.

From a readability and maintainability standpoint, 4 tables is far superior to one giant table of mess, that uses a single query, which the database does not support. Anyone with a passing knowledge of computers can look at that database design and infer where what level "options" are being housed. Anyone with a passing knowledge of SQL can look at the table structure and realize where each "option" is related to. So he has additional queries at each level? If he standardizes on his naming of them then he has created an easily understood, easily maintained database that perfectly suits his needs. Anyone who knows SQL can look at his well named list of SQL statements and know exactly what each one does.

Most importantly, if he goes down this route, he will have figured out primary/foreign keys, table relationships, joins, the Create/Read/Update/Delete statements to manipulate the tables, which accounts for >80% of programmatic database work anyways.

It is juvenile to illustrate your own cleverness by referencing an advanced database programming technique to someone who obviously does not know much about database development.

 

[GodlessAstronomer]

No Bulldog, that design is frankly awful. You can talk about normalization until you're blue in the face, meanwhile the rest of us will get work done. Requiring users to build new tables whenever they want to extend the tree is ridiculous, and your queries are going to be far more complex than necessary.

 

[brianmanahan]

Agreed, Bulldog's design (creating multiple tables that hold basically the same data) is not something you should ever do, unless absolutely necessary. You could do this using inner joins on MySQL, and even if you couldn't, it would be better to use the same prepared statement query in the code several times. The table would have an id column (autogenerated primary key), a parent_id column that references the id columns in the same table (it refers to its parent's id, and is set to null for highest-level items), and then a text field describing the level that each row represents.

 

[Hyperblaze]

check out dotproject.

see if you can manipulate it into something that better fits your needs

many times there is actually open source software that exists already that gives you a head start

 

[presidentender]

Bulldog, what would your queries look like in that case? What would lozina's queries look like? (Let's have him answer before anyone else writes this up. I think this will help alleviate what I believe to be a miscommunication based on incomplete knowledge.)

 

[BoberFett]

Bulldog

You don't NEED CTEs to recursive lookups. To do it in a single elegant query, yes you would. But it can be done with loops in the client just as well.

 

[KLin]

You can always handle recursion logic in the app as well to build the menu's.

 

[BoberFett]

I think that's what I said.

 

[Fallen Kell]

I think the difference here is that Bulldog is looking at the problem from a server side app, not a client side app. It all depends on where the work needs to be done. But I have to agree with everyone else here, for something as trivial as this, with something that will probably never have more than 200 entries in its entire lifetime of use, a single table will probably work just fine. That said, I would make the slight modification in replacing the NULLs for a value, 0 will work fine, for the simple reason that it is much easier, to deal with the query output with a single case statement and not needing to verify that there is or is not data contained within the field.

 

[lozina]

Originally posted by: KLin
You can always handle recursion logic in the app as well to build the menu's.

this.

Typically in the apps I develop which require parent/child relationships such as this it is for some kind of tree structure for the UI. As such the most common way we display said tree structure is via a custom AJAX powered tree control. So we have a simple static query when the page loads to get the root nodes (WHERE parent_id IS NULL) and then for every node we then render to the tree control it has an onclick event to expand/collapse the node where it uses AJAX to do a lookup on what children are present (WHERE parent_id = curent_node_id). (Also usually while pulling children we pull a count of each child's children for a more helpful label on the nodes, i.e. "Dairy (8) -> Milk (4)" So Dairy here is parent and in the parenthesis indicates i has 8 children, Milk being one of them who in turn has 4 children, etc.)

But if we ever needed to recurse the entire structure for perhaps a report like this OP's issue then we always turn to stored procedures, which of course MySQL supports, though I have to admit I've never done it in MySQL as I never was asked to - always been with Oracle. But it ends up being a very simple SP, nothing crazy. Just iterating though the tree with a typical depth-first algorithm. So I assume MySQL's stored procedures offer the same basic functionality to accomplish this.

 

[Sphexi]

Wow, lots of great help, and personally I like Bulldog's design from an organizational point of view, but I went with the first suggestion anyway. It does get annoying when you're creating a child of ID 90-whatever, but not that bad.

What I ended up doing is writing the main page to loop through a few times.

1) You hit the page, it displays level one (which is a loop, each run through creates a table cell with an option whose parent is NULL, 3 wide, then creates a new table row, etc.). When you click one of those (they're all form buttons btw, hate me if you'd like), it posts back to itself with a few string fields, such as "trigger1=triggered&level1ID=1".
2) Level 2 is built the same as level 1, but it pulls the level1ID from the string, and pulls everything whose parent equals that. Also creates new hidden form fields for the first trigger and ID.
3) You click something on level 2, it repeats to level 3, and again to the last step (no level 4 of buttons).
4) Last step after clicking level 3 is an optional comment field, and the submit/save button. Click this and it resubmits all three triggers, IDs, and a new field called "submitnow=yes", back to the page again.
5) Code at the top of the page catches the submitnow field, pulls all the variables, and inserts them into the database, pulling the option text as it does so you have both IDs and the text for each stored in the call log. Then a hidden form with some javascript posts back to itself with a blank string, resetting the page.

I'm sure there's WAY better ways of doing this, but the whole page, including a method of requiring the user to enter their phone extension/password at the beginning (and storing it in a session) is only 450-ish lines of code. I have some other pages for reporting, letting someone see their logs for the day, and an admin to see logs for the day, or top 10/50/100/250/500/month, etc. These are only about 110 lines each, and there's a 15 line file that downloads the report you pulled as a tab-delimited file to load into Excel or something like that. I've been using it for a week now without an issue, and there's only a few more things I want to add (paging on the reports, an interface for adding/removing options without having to open the db management tool, etc.).

Once again thanks for all of the help, my next job is to switch it to SQL 2008 (which we use at work, I didn't have on my home server), should be pretty simple.

Originally posted by: Fallen Kell
I think the difference here is that Bulldog is looking at the problem from a server side app, not a client side app. It all depends on where the work needs to be done. But I have to agree with everyone else here, for something as trivial as this, with something that will probably never have more than 200 entries in its entire lifetime of use, a single table will probably work just fine. That said, I would make the slight modification in replacing the NULLs for a value, 0 will work fine, for the simple reason that it is much easier, to deal with the query output with a single case statement and not needing to verify that there is or is not data contained within the field.

Should have specified, this is a call tracking log, our team handles between 150-200 calls a day, this will probably store up to a year's worth of call logs, then be archived. We'll run it on a dedicated server probably, nothing high end as the calls take place over 12 hours, and reports are run only weekly.