Database structure with Datamapper and nestedsets - need help

[eluser]Cgull[/eluser]
Hello,

I need to develop a website and getting lost with structuring the database.

Hope someone can help me here?

I am attaching a file that should explain the database structure, I'm just not sure how to design the database.

Do I use nestedsets or should I not?

Not sure where to go from here.

To explain a bit:

I will have many provinces.
I will have many books.

Each province can be linked to many books.
Each book can be linked to many provinces.

Each province->book will be published once a week, in the database terms it will be called an issue.

So as I see it, I will have:
provinces table
books table
books_provinces table
issues table
books_issues_provinces table

Now how do I link the tables with datamapper, is it many to many or is it nestedsets?

Hmmmmmmmmm..... hope I explain my confusion and where my problem is.

Can someone please help?
And here I start to lose it.....

[eluser]Cgull[/eluser]
Ok, trying my hardest to understand what I need to do.

Am I correct by saying I probably need to add left and right fields to the books_provinces table?

So the table will look like:
id
book_id
province_id
left_id
right_id

And another table called issues:
id
date

Then, do I create a model called: Books_provinces like that:?

Code:

class Books_provinces extends DataMapper
{
    $nestedsets = array(
           'follow' => FALSE
    );
}

Not sure what do these lines mean:

name - Name of the column in the nested sets table that contains a name used in the path methods.

Is the nested sets table the books_provinces table?
What are the path methods? Where do I use them?

root - Name of the column in the nested sets table that is used to for the nodes tree root pointer.

What is a root pointer?

I read a lot about nested sets but couldn't find anywhere an explanation of the acutal tables structure in the database.

[eluser]Cgull[/eluser]
Still trying to understand how do I use what...

The issues table:
id
book_id
issue_date

The books_provinces table:
id
book_id
province_id
left_id
right_id

My model is now called:
Book_province

Code:

class Book_province extends DataMapper
{
$nestedsets = array(
    'name' => 'issue_date',
        'follow' => FALSE
  );
}

Then in my provinces controller I have this function:

Code:

function tree()
{
$tree = new Book_province();
$tree->new_root();
}

Going to mysite/provinces/tree
I get this error:
Parse error: syntax error, unexpected '$nestedsets' (T_VARIABLE), expecting function (T_FUNCTION) in C:\wamp\www\thinklocal\application\models\book_province.php on line 4

So I changed to model code to:

Code:

class Book_province extends DataMapper
{
var $nestedsets = array(
    'name' => 'issue_date',
        'follow' => FALSE
  );
}

Now I get this error:

A Database Error Occurred

Error Number: 1146

Table 'thinklocal.book_provinces' doesn't exist

DESCRIBE `book_provinces`

Filename: C:\wamp\www\thinklocal\system\database\DB_driver.php

Line Number: 330

I am trying really hard to understand what I need to do and how to use Datamapper and nestedsets, is there a tutorial somewhere that show how to use this extension from A to Z?

[eluser]WanWizard[/eluser]
Nested sets are for storing tree structures in a single table. It has nothing to do with relations.

[eluser]Cgull[/eluser]
Thanks.

So can you help me with that?

How do I use it?

In a controller? In a model?

Did you understand what am I trying to do from my posts?

[eluser]WanWizard[/eluser]
Your attachment is a bit confusing. You start with a simple tree structure that says:

- Province has_many Books
- Books has_one Province
- Books has_many Issues
- Issue has_one Book

But then you draw the relation the other way around, with a book linked to multiple Provinces, which suggest there is a has_many relation between the two.

I suggest you work out your database schema first using the rules for database normalisation. It will make it clear where there repetition is, and therefore what relations you have between objects.

Once you've done that, create a Model for each table and define the relations.

[eluser]Cgull[/eluser]
Thank you.

I'm on the way of quitting Datamapper and nestedsets alltogether but if you don't mind helping me step by step that will be great.

So now I'm stuck on this new problem:

Province A can be linked to many books. (One to Many)
Province A linked to book A can have many issues. (One to many?)
Province A linked to book A can have one rate. (pdf file) (One to One?)

So I created tables:

provinces: id, name
books: id, name
books_provinces: id, book_id, province_id

That all worked fine.

Now I need to add the rates to the story.

So I created table called rates:
id
book_id
province_id
rate_pdf_file

How do I get the rate_pdf_file with Datamapper?
Do I need to create another join table here?
Can I not use book_id and province_id as parents without a joined table?

How?

Thank you very much.

[eluser]WanWizard[/eluser]
When defining relations, you'll have to view it from both sides.

A Province can have many books, but does a specific Book only belong to one Province, or can it be linked to multiple Provinces at once?

If only one, it's Province has_many Book, and Book has_one Province. Normalisation rules say that the foreign key (province_id) should be in the Book record, and no junction table is needed. If multiple, its Province has_many Book, and Book has_many Province, and you need a junction table with both the foreign keys (province_id and book_id) to link the two models.

If you have values that are a property of the relation itself, there are three options:

1) add the property (rate_pdf_file) to the junction table, and use the join_fields() methods of Datamapper to access or set them.
2) create a model for the junction table, and create a one to many relation between it and Provinces and Books so you can access it as any other model
3) create a separate model (like you propose)

Number 1 is the preferred solution, and it works out of the box and transparently.

For number 3, you need to query it manually, as Datamapper doesn't support compound keys, so you can't make a relation on two foreign keys.

What you can do is create a relation on both individually (Province -> Rates and Book -> Rates), and query with an extra where clause:

Code:

$book = new Book(1);
$rate = $book->rate->where('province_id', $provid);

But I would not recommend that.

[eluser]Cgull[/eluser]
Hurray, that helped.

I used option one.

THANK YOU.

Now my next step: (*blush*)

province has many books and many issues
book has many provinces and many issues
issue has one book and one province

New table issues:
id
issue_date
published

So what I think of doing:
As the rates_pdf field does not belong to an issue, I think I need to create another join table?

books_issues_provinces:
id
book_id
issue_id
province_id

Then add to province model:

Code:

$has_many = array('book', 'issue');

The book model:

Code:

$has_many = array('issue', 'province');

And create a new model:

Code:

class Issue extends DataMapper
{
var $has_one = array('book', 'province');
      ...
}

Then if I want to get the issue that is published for Province with id 1 and Book with id 1 do I do this?

Code:

$p = new Province(1);
$b = new Book(1);

$issue = new Issue();
$issue->where_related('book/province', 'published', TRUE)->get();

Where do I tell the query to look for province 1 and book 1?

Next when I create an issue, how will I save it to the issues table and to the join_table?

Thank you very much.

[eluser]WanWizard[/eluser]
You can't create a relation table with more than 2 foreign keys. Well, you can, but Datamapper doesn't support it.

If Issue has a one-to-one relation with the relation between Province and Book (i.e. any combination of Province and Book may have 1 issue, but only one), I would not make it to complex, and add the columns to the relation table, like rate_pdf. Allow a NULL value to indicate whether or not there is an issue.

If you want to make a separate table that needs to be related to the relation between Province and Book, the only option is to make a model for that table, but that will make it a lot more complex.