Lieven,
If a folder belongs to an account, why not use the account PK as a FK in
folders?
See http://www.intelligententerprise.com/001020/celko1_1.jhtml for ideas
about SQL representation of trees.
Peter Brawley
http://www.artfulsoftware.com
-----
Lieven De Keyzer wrote:
> I'm writing a web-application that allows users to store their bookmarks.
> Each user has a tree of folders (and bookmarks belong to these folders).
> The only thing I want to do with tree elements at
> the same level is display them, and let the
> user only go up and down in the tree by one level. No aggregate
> functions or things like that on subtrees. I decided to
> do it like this:
>
> http://wilma.vub.ac.be/~lddekeyz/test/schema.png
>
> Where the arrows represent foreign key constraints.
> But then I realized there is a transitive functional dependency:
> parent_id-> owner. So I normalized it to:
>
> http://wilma.vub.ac.be/~lddekeyz/test/schema2.png
>
> With the arrows still representing foreign key constraints. Now, I
> really feel something is wrong here. And I just know when I try to put
> this in SQL :)
>
>
> CREATE TABLE role (
> role_id INTEGER NOT NULL,
> rolename VARCHAR(25) NOT NULL,
> PRIMARY KEY (role_id)) TYPE = InnoDB;
>
>
> CREATE TABLE account (
> username VARCHAR(25) NOT NULL,
> password VARCHAR(80) NOT NULL,
> email VARCHAR(80) NOT NULL,
> first_name VARCHAR(80) NOT NULL,
> last_name VARCHAR(80) NOT NULL,
> role_id INTEGER NOT NULL,
> PRIMARY KEY (username),
> FOREIGN KEY (role_id) REFERENCES role(role_id)) TYPE = InnoDB;
>
>
> CREATE TABLE folder (
> folder_id INTEGER NOT NULL AUTO_INCREMENT,
> parent_id INTEGER,
> foldername VARCHAR(80),
> PRIMARY KEY (folder_id),
> FOREIGN KEY (parent_id)
> REFERENCES folder(folder_id) ON DELETE CASCADE) TYPE = InnoDB;
>
>
> CREATE TABLE owner (
> parent_id INTEGER NOT NULL,
> owner VARCHAR(25) NOT NULL,
> PRIMARY KEY (parent_id),
> FOREIGN KEY (parent_id) REFERENCES folder(folder_id) ON DELETE CASCADE,
> FOREIGN KEY (owner)
> REFERENCES account(username) ON DELETE CASCADE) TYPE = InnoDB;
>
>
> Now when I delete a user, everything related to him in the owner
> table will be deleted, but in the folder table, his folders will not
> be deleted.
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.5 - Release Date: 5/4/2005
