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


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