Hi I am working in a project where I have to save data with parent child relationship.
The data structure is like this:
flight_id child_id Flight_name
1 1 E213
2 2 E333
3 2 E444
Flight 1 has no child_id so it's parent id replaces it, flight 2 has two child_id so in each of child_id I have to put the parent id.
Any idea how can I do that?
Parent-child relationships are usually modeled the other way around: the child belongs to a parent. Think about it, a child can only have one parent, that's easy to model; but a parent with many child ids is pretty hard to model.
Additionally you can use Nested Sets a.k.a. MPTT to make querying for children/parents easier.
See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ and http://en.wikipedia.org/wiki/MPTT.
Normally you won't store all the child ID's, but the parent ID's:
CREATE TABLE `flight` (
`id` int(11) NOT NULL,
`parent` int(11) DEFAULT NULL,
`flight_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`parent`) REFERENCES `flight` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Your data would look like:
id parent flight_name
1 NULL E213
2 NULL E333
3 2 E444
4 2 E555
5 2 E666