Re^2: Categories. Table of Contents. Trees?

For my immediate purposes I need to take a mysql structure set up like so:

Book Name <-> Chapter Name <-> Sentence text
[download]

where the <-> is a lookup table and Chapter name exists only once in the database, but has a 1:N relationship with books (i.e., Chapter 1 exists in many books). This is where I'm having a problem converting from. I'm not sure how I handle the unique id per node, or how to recalculate or anything else along those lines. This is new territory for me. I think I'm going to have to get the book suggested below.

I ran across Tree.pm awhile ago, thanks for refreshing my memory on it.

Harley J Pig

Comment on Re^2: Categories. Table of Contents. Trees? Download Code

Replies are listed 'Best First'.
Re^3: Categories. Table of Contents. Trees? by tilly (Archbishop) on Nov 04, 2005 at 03:56 UTC
I smell over-abstraction. Unless space is truly at a premium, I would strongly suggest a design where you have a book table (with id, name, publisher, etc) and a chapter table (with a book_id, position_id, name, etc). And then not worry about the fact that some chapter names are replaced. Then just use the database in the obvious way for the specific query. As an example of why, consider how much work it takes with your schema to change the name of a chapter in a book if it was misentered. Think of how little work it takes with my schema. Another example of why, think of how much work you're putting out now to try to figure out how to make hierarchies completely generic. I guarantee that when you're done, your code will be a lot harder to understand and be a lot slower than the obvious, straightforward approach. My general rule of thumb says, Do not try to come up with an abstraction until I am solving a problem for the third time. Why? Well the first time I have no idea what is important or what kinds of changes I'll have. So I'll over-design for things that I won't need. (Plus I may never face this problem again.) The second time I'll design a great solution for my second problem, and will be shocked at things that are different. (This time I'm more likely to face it again.) But the third time I have enough of a sense of the problem that I have a chance of coming up with something useful. (And if I've faced it three times, I'll almost definitely see it again.)	[reply]
Re^3: Categories. Table of Contents. Trees? by ickyb0d (Monk) on Nov 04, 2005 at 16:39 UTC
This is where I'm having a problem converting from. I'm not sure how I handle the unique id per node, or how to recalculate or anything else along those lines The way you might want to handle it in the DB is to have each table have a parent_id column. This parent_id would refer to the unique id of a row entry. So a 'Chapter 1' row entry in the chapter table might have a parent id of '32'. Where '32' is the book_id of the book 'Moby Dick'. Or something along those lines. I assume you would want this for your chapter and sentance tables. As previously stated, to do this you would have to have multiple entries of 'Chapter X' dependant on each book.	[reply]