On Wed, 2005-11-16 at 09:43 +1100, Peter Harvey wrote: > Just remember, every 'node' of data is given a unique id. All these > tables do is record different types of information on that node. There > is *no* global list of all nodes. This makes the database horrible un-relational. It is standard database practice for the same type of data to get sequential IDs. This leads to two cases: a. "bookmark" and "history item" are the same data type, in which case they appear in the same table and have the same properties. b. "bookmark" and "history item" are different data types, in which case they each get their own set of IDs and their own tables. Other cases lead to confusion, so they shouldn't be used unless there is a very good reason to do so. > # parent-child relationships between nodes > table 'children' > int id > int child I was about to write a big, long description of how this table could be fixed to work work for case "b." above, when I realized that it could simply be removed entirely. Its only use is for topics, so it can be replaced by: table 'topic_bookmarks': int topic_id int bookmark_id > # data of high-level nodes that are used to organise the other nodes > table 'globals' > int id > string name This table will never get used, and it's not needed. > # data related to page visits, refers to a url node > table 'visits' > int id > int url > date visited > > # data for visited urls > table 'urls' > int id > string url > string title > string icon I imagine the first table could be removed and the second could be renamed to "history"? Also, you could remove the "id" column. Like so: table 'history' string url string title string icon date last_visit > # data for bookmarks > table 'bookmarks' > int id > string url > string title > string icon > # data related to user-specified topics > table 'topics' > int id > string name I'd rename those "id" columns to "bookmark_id" and "topic_id" to make their relations with "table_bookmark" absolutely clear. SQLite then lets you use "NATURAL JOIN", a cute bit of syntactic sugar. > Q. Can I store searches here? > A. Yes. Just store it as a url with a special marker (I like newline) > in the middle, and make it a child of the node which is called > 'searches' according to the globals table. You wrote a "searches" table in another post; I'll copy/paste it here, without the superfluous "id" column: table 'searches' string url_prefix string url_suffix string title string icon It could also be changed to just have one line of data, "url", and just use something smarter than "%s". "`" or "^" would make it easy: they're single characters that are guaranteed not to appear in a url. > Q. How do I store a bookmark? > A. Create a node (unique number) and add an entry to the bookmarks > table. Then take the node called 'bookmarks' in the globals table > and make your new node node a child of that node. With my design: INSERT INTO bookmarks (bookmark_id, url, title, icon) VALUES (NULL, 'http://www.gnome.org', 'GNOME', '\89PNG...'); The caller can then use "sqlite3_last_insert_rowid()" to return the bookmark ID (so that topics can be assigned to it). > Q. Why not just have one table per attribute? Name, icon, url, etc? > A. I'm not a database expert, but I would *assume* that would make > queries horribly slow as you'd need to correlate data from multiple > tables simultaneously. I've tried to ensure we correlate no more > than three tables at once for most things. Correct. That would be impractical design to make tons of tables. To sum up: in my experience, the above database design is slightly easier to understand and to code for. It also removes pretty much every unnecessary concept translated from "EphyNode" (database IDs being the main one) -- and in my opinion, that is a good thing! -- Adam Hooper <adamh densi com>
Attachment:
signature.asc
Description: This is a digitally signed message part