List:General Discussion« Previous MessageNext Message »
From:Lieven De Keyzer Date:May 6 2005 4:13pm
Subject:Re: Multi-user bookmark system
View as plain text  

>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
>


Thread
Multi-user bookmark systemLieven De Keyzer6 May
  • Re: Multi-user bookmark systemPeter Brawley6 May
    • Re: Multi-user bookmark systemLieven De Keyzer6 May
Re: Multi-user bookmark systemLieven De Keyzer6 May