Multi-user bookmark system
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-user bookmark system
Lieven, If a folder belongs to an account, why not use the account PK as a FK in folders? See http://www.intelligententerprise.com/001020/celko1_1.jhtml for ideas about SQL representation of trees. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-user bookmark system
Peter, From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Lieven De Keyzer [EMAIL PROTECTED] CC: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-user bookmark system
From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Lieven De Keyzer [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Lieven De Keyzer [EMAIL PROTECTED] CC: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Bookmark Table Data
I'd like to move some bookmarks in one database to another. The format
of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly trucated):
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| bookmark_id | int(11) unsigned | | PRI | NULL| auto_incr |
| group_id | int(10) unsigned | | MUL | 0 | |
| bookmark_title | varchar(255) | | | | |
| bookmark_url | varchar(255) | | | | |
| bookmark_descrip | varchar(255) | YES | | NULL| |
| bookmark_creatio | datetime | | | - | |
| bookmark_private | char(1) | | | 0 | |
| bookmark_last_hi | datetime | YES | | NULL| |
| user_id | int(10) unsigned | | MUL | 0 | |
| bookmark_deleted | char(1) | | | 0 | |
+--+--+--+-+-+---+
I want to move this into the online-bookmarks 'bookmark' table which
has the format:
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| user| char(20) | | | ||
| title | char(70) | | MUL | ||
| url | char(200) | | | ||
| description | char(200) | YES | | NULL||
| private | enum('0','1') | | | 0 ||
| date| timestamp(14) | YES | | NULL||
| childof | int(11) | | | 0 ||
| id | int(11) | | PRI | NULL| auto_increment |
| deleted | enum('0','1') | | | 0 ||
+-+---+--+-+-++
The only field mappings I care about are:
bookmark_title - title
bookmark_url - url
bookmark_description - description
Now I know I can generate insert statements and then run that script on
the target but is there a better way? The databases do not have direct
access as each is running on localhost only.
Thanks,
Mike
--
A program should be written to model the concepts of the task it
performs rather than the physical world or a process because this
maximizes the potential for it to be applied to tasks that are
conceptually similar and, more important, to tasks that have not
yet been conceived.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving Bookmark Table Data
From: Michael B Allen [mailto:[EMAIL PROTECTED]
I'd like to move some bookmarks in one database to another. The format
of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly
trucated):
+--+--+--+-+-+
---+
| Field| Type | Null | Key | Default
| Extra |
+--+--+--+-+-+
---+
| bookmark_id | int(11) unsigned | | PRI | NULL
| auto_incr |
| group_id | int(10) unsigned | | MUL | 0
| |
| bookmark_title | varchar(255) | | |
| |
| bookmark_url | varchar(255) | | |
| |
| bookmark_descrip | varchar(255) | YES | | NULL
| |
| bookmark_creatio | datetime | | | -
| |
| bookmark_private | char(1) | | | 0
| |
| bookmark_last_hi | datetime | YES | | NULL
| |
| user_id | int(10) unsigned | | MUL | 0
| |
| bookmark_deleted | char(1) | | | 0
| |
+--+--+--+-+-+
---+
I want to move this into the online-bookmarks 'bookmark' table which
has the format:
+-+---+--+-+-+
+
| Field | Type | Null | Key | Default | Extra
|
+-+---+--+-+-+
+
| user| char(20) | | | |
|
| title | char(70) | | MUL | |
|
| url | char(200) | | | |
|
| description | char(200) | YES | | NULL|
|
| private | enum('0','1') | | | 0 |
|
| date| timestamp(14) | YES | | NULL|
|
| childof | int(11) | | | 0 |
|
| id | int(11) | | PRI | NULL|
auto_increment |
| deleted | enum('0','1') | | | 0 |
|
+-+---+--+-+-+
+
The only field mappings I care about are:
bookmark_title - title
bookmark_url - url
bookmark_description - description
Now I know I can generate insert statements and then run that
script on
the target but is there a better way? The databases do not have direct
access as each is running on localhost only.
While it's a little clunky, why not do this on the first server:
CREATE TABLE apb_bookmarks_tmp
SELECT '' AS user,
bookmark_title AS title,
bookmark_url AS url,
bookmark_description AS description,
0 AS private,
NULL AS date,
0 AS childof,
NULL AS id,
0 AS deleted
FROM apb_bookmarks;
I'm assuming that by these are the only field mappings that I care about, you mean
that those are the only fields you want brought over. If not, then replace the NULLs
and 0s with the proper field names.
Once you've done this, all you need to do is mysqldump the table, scp it over the the
new host, and import it. If you had indices on the original table, you'll have to
manually create them on the new table -- CREATE TABLE ... SELECT FROM doesn't carry
those over.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BookMark
Sorry, my mistake in not explaining in details I have problem on 'Multiple-step operation generated errors. Check each status value'. when i use Visual Basic ADODC OCX to addnew. This is the sample of my source code. The problem crop up when the bookmark value 1. againxxx1: Form1.DtaOut.Recordset.AddNew ' MsgBox Form1.DtaOut.Recordset.Bookmark If Form1.DtaOut.Recordset.Bookmark 1 Then Form1.DtaOut.Recordset.CancelUpdate Form1.DtaOut.Recordset.Close Form1.DtaOut.Recordset.Open Form1.DtaOut.RecordSource = Select * from GblOutDB Where id = 1 Form1.DtaOut.Refresh GoTo againxxx1 'Form1.DtaOut.Recordset.Bookmark = 1 End If Form1.DtaOut.Recordset(aDate) = Format(Date, mm/dd/) Format(Time, hh:mm:ss ampm) Form1.DtaOut.Recordset(method) = 2 Form1.DtaOut.Recordset(sp) = Gblsp Form1.DtaOut.Recordset(mobile) = Form1.DtaIn.Recordset(Sender) Form1.DtaOut.Recordset(Sender) = Form1.DtaIn.Recordset(Sender) Form1.DtaOut.Recordset(try) = 0 Form1.DtaOut.Recordset(member) = GblMember Form1.DtaOut.Recordset(Serviceid) = GblServiceID Form1.DtaOut.Recordset(msgid) = Form1.DtaIn.Recordset(msgid) Form1.DtaOut.Recordset(Priority) = GblPriority Form1.DtaOut.Recordset(Status) = Normal Form1.DtaOut.Recordset(smstype) = Gblsmstype Form1.DtaOut.Recordset(aMT) = GblaMT Form1.DtaOut.Recordset(smsfile) = Form1.DtaIn.Recordset(smsfile) Form1.DtaOut.Recordset(msg) = GblError1 Form1.DtaOut.Recordset.Update At 06:03 AM 5/17/2002 -0500, Gerald R. Jensen wrote: Bookmarking?! No, MySQL does not support bookmarking ... it is relational database manager, not a word processor. All you have to do is save the query you run, and it will return the same results. - Original Message - From: SB Lim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 17, 2002 5:42 AM Subject: BookMark I am using VB6 with MYSQL, Is MYSQL support Bookmarking ? How can I implement that on the MYSQL ? Regards SB Lim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BookMark
I am using VB6 with MYSQL, Is MYSQL support Bookmarking ? How can I implement that on the MYSQL ? Regards SB Lim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BookMark
Bookmarking?! No, MySQL does not support bookmarking ... it is relational database manager, not a word processor. All you have to do is save the query you run, and it will return the same results. - Original Message - From: SB Lim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 17, 2002 5:42 AM Subject: BookMark I am using VB6 with MYSQL, Is MYSQL support Bookmarking ? How can I implement that on the MYSQL ? Regards SB Lim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
