Peter,
From: Peter Brawley <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Lieven De Keyzer <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: Multi-user bookmark system Date: Fri, 06 May 2005 10:09:12 -0500
Lieven,
If a folder belongs to an account, why not use the account PK as a FK in folders?
Well, that's what's in the first picture. The owner is a FK in the folders table. But problem is there is a transitive dependency there. So I normalized to 3NF. But perhaps it's just easier to not normalize and do it as in picture 1.
See http://www.intelligententerprise.com/001020/celko1_1.jhtml for ideas about SQL representation of trees.
I'll take a look.
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
