Create a database for keys only? The Perfect Database?

[eluser]Nummero2[/eluser]
Hi,

at the moment I have 3 mysql tables:

project table:

project_id (primary key, auto increment)
project_text (text field)
....
....

task table:

task_id (primary key, auto increment)
project_id (foreign key, if the task is part of a project)
task_text (text field)
....
....

protocoll table:

ptc_id (primary key, auto increment)
ptc_text (text field for protocoll)
....
....

With that I can create projects with added tasks or tasks on their own and protocolls on their own without any connections to tasks or projects. That should change

Now what I want to achieve:
- I want to connect the protocolls with the tasks and the projects, so a task or a project can have a own protocoll which is related to them

BUT

- I want to build a fourth table only for the keys so the seperated parts (project, tasks, protocoll) don´t know anything about the other ones. So no more foreign keys in protocoll table.

Example:

key table:

- project_id
- task_id
- ptc_id

Is that possible and how? Do I have to leave the keys out of the other tables then?

Greetings Sebastian :-)

[eluser]mdowns[/eluser]
If you leave the keys out of the other tables, then you can't join them to get useful information

Why don't you want foreign keys?

[eluser]Nummero2[/eluser]
Because later I may want to add more components to the situation and in that way they shouldnt be related/bound to each other directly.

So the planned key table in detail:

dummy_id = int(11) primary key auto increment
project_id = int(11)
task_id = int(11)
ptc_id = int(11)

Also I created a new model for it key_model.php

Ok, but now how do I manage to have my create form writing in it´s "own" table (for example: project table , create a new project) and in my new key table at the same time?

Greetings Sebastian

[eluser]Jamie Rumbelow[/eluser]
Well, then just have one tablke with all the keys linking to eachother, then use a JOIN method to run the query and get the info

[eluser]mdowns[/eluser]
Inside the insert function for the model, load the "key" model and insert the row appropriately.

[eluser]Nummero2[/eluser]
Ok Im kind of confused, at the moment my project model looks like this:

Code:

function __construct()
    {
        parent::Model( 'project_entries' );
        parent::__construct('project_entries','project_id');

        $this->unique_item='';

        $this->items=array(            

            'project_id'        => '',
            'project_text'      => '',
        );
        $this->itemss = array(
        );
    }

My key model like this:

Code:

function __construct()
    {
        parent::Model( 'key_table' );
        parent::__construct('key_table','dummy_id');

        $this->unique_item='';

        $this->items=array(            

            'dummy_id'        => '', //Primary key,auto increment
            'project_id'      => '',
            'task_id'         => '',
            'ptc_id'          => '',
        );
        $this->itemss = array(
        );
    }

What do you mean now should I do? JOIN?

[eluser]mdowns[/eluser]

Code:

$this->load->model('keyModel');
$this->keyModel->insert($data);

Also, consider when you create a project with 2 tasks

1. Create project: create a row in the key table:
1 | NULL | NULL

2. Create task: create a row in the key table ???
1 | NULL | NULL
NULL | 1 | NULL

no...you'd ad it to the existing project
1 | 1 | NULL

3. Create another task (this time you create a row):
1 | 1 | NULL
1 | 2 | NULL

So you have to have extra logic in each model (and possibly extra queries).

[eluser]Nummero2[/eluser]
so now I am able to send values to my both tables

1. protocoll_table

pid int(11)(primary key) auto incr
protocoll name text

2. key_table

pid int(11)(primary key) auto incr
project_id int(11)

now when I send data via my create form I can chose wheter or not I want to bind the protocoll I´m creating to a project (project_id =1,2,3) or not (project_id =0)

But now how do I display the protocoll by project_id?

simply load up the two corresponding models and do a

Code:

listinfobyid('project_id');

request?

Greetings Sebastian