IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

IWETHEY Home / IWETHEY Board / Theory and Practice of Programming Forum / Re: Which database engine?
Post #158,346
by bluke
6/4/04 1:46:02 AM
Reply
New Re: Which database engine?
A number, for now both MS-SQL and Oracle 9i. The data is configuration data that we display and that the user can change. There are probably more reads then writes, but writes definately need to be fast.
Post #158,375
by admin
Picture of admin
6/4/04 8:06:33 AM
Reply
New CONNECT BY is out then. :-)
CONNECT BY is out if you have to support Transact-SQL.

You could use nested set trees, which for small hierarchies are probably fast enough on inserts, or simple parentID columns and some code, which can be considerably slower for reading the entire tree. A third method, encoding the path to the node with the node itself, limits the depth you can represent (not an issue if you have shallow trees) and requires string compares for lookups.

With a nested set tree, simple updates will be just as fast as any keyed update would be. Only inserts, deletes, and rare operations like moves are slower, and then only when you are manipulating the left side of the tree. I use a hybrid here, as finding the immediate children/ancestor only of a node can be problematic with a pure nested set.

If you want some example code, I have both Transact-SQL and plpgsql (Postgres, similar to PL/SQL).
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
     How to store a tree structure in a relational db? - (bluke) - (20) - June 3, 2004, 04:21:24 PM EDT
         Ask Bryce! - (folkert) - (1) - June 3, 2004, 04:25:06 PM EDT
             wiki links - (tablizer) - June 3, 2004, 11:57:01 PM EDT
         Which database engine? - (admin) - (2) - June 3, 2004, 05:43:22 PM EDT
             Re: Which database engine? - (bluke) - (1) - June 4, 2004, 01:46:02 AM EDT
                 CONNECT BY is out then. :-) - (admin) - June 4, 2004, 08:06:33 AM EDT
         why not store it in a document - (boxley) - (1) - June 3, 2004, 05:47:47 PM EDT
             We want to move away from LDAP ... - (bluke) - June 4, 2004, 02:06:02 AM EDT
         Just use Windows Server 2003 and Active Directory! - (pwhysall) - (1) - June 3, 2004, 05:54:38 PM EDT
             **thwak!** -NT - (jb4) - June 4, 2004, 02:10:53 PM EDT
         See what Scott said - (ben_tilly) - (2) - June 3, 2004, 08:02:09 PM EDT
             Before 9i - (admin) - (1) - June 3, 2004, 10:27:08 PM EDT
                 I don't use it, so I wouldn't know. Correction appreciated. -NT - (ben_tilly) - June 3, 2004, 10:45:47 PM EDT
         Re: How to store a tree structure in a relational db? - (johnu) - (7) - June 4, 2004, 08:16:45 AM EDT
             AKA Nested Set Trees. :-) -NT - (admin) - (6) - June 4, 2004, 08:54:08 AM EDT
                 After reading about nested sets ... - (bluke) - (5) - June 5, 2004, 02:29:53 PM EDT
                     Updates aren't an issue. - (admin) - (3) - June 5, 2004, 04:48:38 PM EDT
                         If I had to do it over.... - (ChrisR) - (2) - June 5, 2004, 11:27:30 PM EDT
                             That's exactly how I do it. - (admin) - (1) - June 6, 2004, 07:19:32 AM EDT
                                 Guess I'll have to try it for myself - (ChrisR) - June 6, 2004, 02:12:53 PM EDT
                     We have a lot of inserts - (bluke) - June 6, 2004, 05:24:23 AM EDT

Enthusiasm substituting for real talent.
66 ms