Peter,
>From: Peter Brawley <peter.brawley@stripped>
>Reply-To: peter.brawley@stripped
>To: Lieven De Keyzer <lieven_dekeyzer@stripped>
>CC: mysql@stripped
>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
>
