>From: Peter Brawley <peter.brawley@stripped>
>Reply-To: peter.brawley@stripped
>To: Lieven De Keyzer <lieven_dekeyzer@stripped>
>Subject: Re: Multi-user bookmark system
>Date: Fri, 06 May 2005 11:07:48 -0500
>
>Lieven,
>
>Here it is.
>
>Removing the FK looks to me like an incorrect fix to the transitive
>dependency.
But the FK is not gone, is it? According to Database Systems by Connoly and
Begg, this is the way to resolve a transitive functional dependency.
The foreign key to the account table is now in the owner table and the owner
table has a foreign key to folder. I know there's something wrong with my
scheme, but it should be possible to normalize it, or not?
>
>PB
>
>Lieven De Keyzer wrote:
>
>>
>>
>>>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
>>>
>>
>>
>>
>>
>
>
>--
>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
>
