List:General Discussion« Previous MessageNext Message »
From:Lieven De Keyzer Date:May 6 2005 4:10pm
Subject:Re: Multi-user bookmark system
View as plain text  
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
>


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