> The only difference is that you have moved parent outside main table.
> No benefits at all. You have to create two records I two tables instead of
> one. You have to make joins to see what is the parent of particular child.
> I am strongly against this.
1) relational theory clearly states you should store what is TRUE
(this means: no NULLs). Obviously, people are used to NULLs,
but this doesn't make them right.
2) there's nothing wrong with joins
3) there's nothing wrong with multiple inserts
4) you avoid self-joins, which can be tedious to write.
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> > It's really clear what you want. Please specify.
> > If what you basicaly want is just a tree structure, then it's done like
> > CREATE TABLE something (
> > id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> > parent INT NOT NULL,
> > data1 CHAR(255),
> > data2 CHAR(255),
> > ...
> > );
> > Then you can specify the parent node id for each record in database.
> > This is how tree-like structures are stored in SQL. Hope that helps.
> That is ONE way to store a tree structure :-)
> Another would be:
> (ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> other stuff)
> (ItemID int,
> ParentID int
> primary key (ItemID, ParentID)
> I prefer the latter.