how to edit/manage multi tiered conditional data in php

Question

I have 4 tables in a mysql database that i need to add/edit/manage.

The difficulty, is that each one, is dependent on the one before it.

The way i have this setup on the user end, is you select an option from table 1. You are then presented with the options in table 2, that have the first option's ID in their row. Once you select option in table 2, you are taken to table 3, which generates its list where the rows contain the ID of your selection in table 2, and so on and so forth.

The problem is how to edit/manage this.

Obviously if you delete an option in table 1, all its subsequent child options will be invalid.

I just need an idea of how to manage a setup like this.

Answer 1

Multi-tiered data is most-often stored in with a parent:child relation field in a table.

id  | name | parent_id
----------------------
1   | Mom  | NULL
2   | Son  | 1

Write a recursive function/query to traverse the relationships and any updates/inserts/deletes should be relatively simple.

Answer 2

Without many details it's hard to comment on your exact situation, but here's a way to organize your MySQL database structure:

TABLE table1
  INT id
  ... other data ...

TABLE table2
  INT id
  INT table1_id FOREIGN_KEY table1
  ... other data ...

TABLE table3
  INT id
  INT table2_id FOREIGN_KEY table2
  ... other data ...

TABLE table4
  INT id
  INT table3_id FOREIGN_KEY table3
  ... other data ...

And your url structure for your site could be:

/table1/add
/table2/add?table1_id=T1_ID
/table3/add?table2_id=T2_ID
/table4/add?table3_id=T3_ID
/table1/(edit,delete)/T1_ID
/table2/(edit,delete)/T2_ID
/table3/(edit,delete)/T3_ID
/table4/(edit,delete)/T4_ID

For adding to table2,3,4:

INSERT INTO table2 (data, table1_id) VALUES(data, T1_ID);

For deleting from table1:

DELETE FROM table1 WHERE id = T1_ID;
$t2_ids = SELECT id FROM table2 WHERE table1_id = T1_ID;
DELETE FROM table2 WHERE table1_id = T1_ID;
$t3_ids = SELECT id FROM table3 WHERE table2_id IN ($t2_ids);
DELETE FROM table3 WHERE table2_id = $t2_ids;
$t4_ids = SELECT id FROM table4 WHERE table3_id IN ($t3_ids);
DELETE FROM table4 WHERE table3_id = $t3_ids;

And so and so forth if you're deleting from the sub tables.

Additionally, if your data in each table isn't very different, you can use a single table to maintain the parent/child relationships

TABLE table
INT id
INT parent_id
... other data ...

Your URL structure won't change much, but your deleting pseudo-code can be optimized to use subselects on the table itself.