Hello,
> 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.
Why?
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.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
> > It's really clear what you want. Please specify.
> >
> > If what you basicaly want is just a tree structure, then it's done like
> that:
> >
> >
> > 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:
>
> ITEMS
> (ItemID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
> other stuff)
>
> ITEM_PARENT
> (ItemID int,
> ParentID int
> primary key (ItemID, ParentID)
> )
>
>
> I prefer the latter.
