+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Create a database for keys only? The Perfect Database? (/showthread.php?tid=11022)
Create a database for keys only? The Perfect Database? - El Forum - 08-22-2008
[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 :-)
Create a database for keys only? The Perfect Database? - El Forum - 08-22-2008
[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?
Create a database for keys only? The Perfect Database? - El Forum - 08-22-2008
[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
Create a database for keys only? The Perfect Database? - El Forum - 08-22-2008
[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
Create a database for keys only? The Perfect Database? - El Forum - 08-22-2008
[eluser]mdowns[/eluser]
Inside the insert function for the model, load the "key" model and insert the row appropriately.
Create a database for keys only? The Perfect Database? - El Forum - 08-23-2008
[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?
Create a database for keys only? The Perfect Database? - El Forum - 08-23-2008
[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).
Create a database for keys only? The Perfect Database? - El Forum - 08-23-2008
[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');Greetings Sebastian