From: Peter Brawley
Date: May 6 2005 3:09pm
Subject: Re: Multi-user bookmark system
List-Archive: http://lists.mysql.com/mysql/183701
Message-Id: <427B8898.7020705@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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