Practical Issues In Database Management

Fabian Pascal has written this work on issues that come up in database administration and recommendations for solving them. It's not really a practical guide, despite the title, but probably many administrators would benefit from reading the book and keeping it handy somewhere. Yes, I mean you, the one who's got a copy of Filemaker Pro at home and thinks he knows it all.

Practical Issues in Database Management author Fabian Pascal pages 256 publisher Addison-Wesley rating 7.5 reviewer Michael Sims ISBN 0-201-48555-9 summary Not a practical, hands-on book at all; contains high-level database problems and theoretical solutions

Most of the time, when a computer book has the word "practical" in the title, it means one thing: examples. Lots and lots of real-world, cut-and-paste examples intended to solve the exact problem you're facing. This book departs from that stereotype by containing little in the way of practical examples. I don't think it even mentions any specific database products. Instead, it mainly discusses the platonic ideal of a database from a scholarly standpoint, and never touches actual examples of database products. As such, it is a relatively timeless book, but it is not what I would describe as "practical".

Essentially, it is a scholarly overview of the whole concept of databases, some common pitfalls that database administrators (DBA's) run into, and where actual database systems fall short of the platonic ideal. It would be a good book for an "Intro to Databases" class (and I don't mean a How to Use Excel course, I mean a CompSci course).

Let's skim through the chapters. I'll try to make this review accessible to all readers, even those who don't know much about databases.

Chapter 1 discusses datatypes (how data is stored in the database), and suggests that DBA's should not fall into the trap of using complex, proprietary datatypes over standard character and numeric fields. Chapter 1 also includes the oddest section of the book: 20 pages of Webpage print-outs whose sole unifying theme seems to be "Look what weird stuff people want to put in databases - and here's a ZDNet printout to prove it!". This section almost turned me off the book entirely, but thankfully it wasn't repeated. I don't know what they were thinking...

Chapter 2 discusses integrity rules. Integrity constraints are rules that your data should obey - enforcing the rules is the problem. For instance, no two employees should have the same employee number. Essentially, the author's advice boils down to implementing integrity in the database itself rather than via triggers or external logic.

Chapter 3 discusses keys. A key is a field in a record with data that you plan to use to pull that record from the table - for instance, if you were getting information about employees, you might use that employee number as a key, because one employee number should correspond to one record and one employee. The author discusses the various types of keys and makes obvious recommendations.

Chapter 4 talks about duplicate rows. It's actually an insightful discussion about a serious flaw in many databases designed by amateurs, and the author provides a few possible paths for how to do something that is surprisingly difficult in large tables: getting rid of duplicate rows. A valuable chapter.

Chapter 5 discusses normalization. Good overview, good recommendations.

Chapter 6 discusses entity subtypes and supertypes - essentially, what do you do when you have items to store in a database that have some traits in common but some not in common. The nomenclature was a little confusing. He discusses some oddities in the most recent SQL standard, which mostly went over my head.

Chapter 7 discusses data heirarchies and trees. In a nutshell: there are no trees in SQL. The author is distressed by this.

Chapter 8 covers redundancy, more or less an extension of chapter 4. Good coverage, mostly seems to be common-sense, but then I've seen plenty of databases that lacked this common sense, so perhaps it isn't as common as one would hope.

Chapter 9 is about quota queries, a common task in any database project, and one that usually seems to have exactly one example in any set of documentation. (Not enough!) Some good tips are hidden in here, and it should be helpful to many DBA's.

Chapter 10 covers missing information, the difference in database-land between a field with (say) Yes, No, an empty string, or a null value, which has given everyone who does any sort of database programming problems at one time or another. The author's analysis is sound and useful.

To sum up, it's a decent book covering a wide range of areas pertaining to databases from a scholarly viewpoint. Perhaps it could be compared to Sun Tzu's Art of War - it doesn't really discuss YOUR situation, but it gives a lot of tips, and if you pay attention, you'll probably find something in there that will help you in your present crisis. The author is more of a scholar than a hands-on instructor, but he obviously knows what he's talking about. The book title should probably be "The Zen of Databases" or something like that, though, rather than implying it will be some sort of practical guide to administering SQL Server 7 or anything along those lines. Probably the people who will get the most benefit from it will be DBA's who have learned database administration from the school of hard knocks - learn by doing - but find themselves doing it more often than they would like, and want to get a little book-learning in to help them past the problems they are encountering. Novices won't get a lot out of it because they won't have hit the problems he describes. Experts will already know the solutions he recommends, although they'll probably get something out of it nonetheless.

The author has a website, Database Debunking, which has a similar tone to the book. There is also online errata for the book.

Purchase this book at Fatbrain.

Comments

A First Course in Database Systems

[Latent IT]

Yes, I know it's a textbook, and has maybe entirely useless examples, and is, by definition, too expensive to be entirely practical, but I learned a lot from this book. It's written by not entirely incompetent professors, and has been taught for several years successfully at a few major universities. The chapter layout is nearly exactly the same as the book reviewed above, and it has examples in both SQL and relational algebra for many problems, so you actually get a feel for how you can use one to get better with the other. Just do a google search for 'A First Course in Database Systems' and you'll come up with quite a few class pages that use it, and use it well.

Integrity

[Lozzer]

Chapter 2 discusses integrity rules. Integrity constraints are rules that your data should obey - enforcing the rules is the problem. For instance, no two employees should have the same employee number. Essentially, the author's advice boils down to implementing integrity in the database itself rather than via triggers or external logic.

Err triggers are in the database. The declarative RI you get in most DB products is just a convinient trigger creation mechanism under the covers... Of course using declarative RI ensures you get a consistent well trigger...

Re:Integrity

[phossie]

triggers or external logic

...implies that they are NOT one and the same. nobody said triggers were external. there's nothing wrong with that statement at face value, and unless you've read the book, you're not providing much of a critique of the author's opinion. if you have read it, i'd be curious to know why you think what you do, because you're still not explaining yourself given the author's supposed criticism.

Re:Integrity

[Lozzer]

I've not read the book, I'm only mildly criticizing the review statement. I agree with the sentiment of what he saying - I'd always use declarative constraints when possible, triggers as my next preference and external logic as a final option. I just think the statement is a bit imprecise:

Essentially, the author's advice boils down to implementing integrity in the database itself rather than via triggers or external logic.

Taken as a whole this phrase says to me that triggers don't implement integrity internal to database. As you say it also implies that triggers are not external logic - so if they are not internal and not external what are they?

what's wrong with FileMaker Pro?

[Fat Lenny]

Crissakes, it has the word "pro" right in the name! How can you argue with something like that? I'm still using Ami Pro for the same reason, and it's way better than WordPerfect 5.

--

Maybe not practical, but very very useful

[micromoog]

Database is one area that needs more conceptual information in the marketplace. You can know every practical command for your database system, but if you don't know how to design the system properly to begin with, you will have trouble and headaches from it forever. I've seen this time and again, where someone goes to a "practical" class on Oracle, SQL Server, DB2, etc. and learns all these commands, but doesn't have the background knowledge to be able to design an effective system.

Practical books are good for something like operating system administration, where design mistakes are much more easily corrected. A database design tends to hang around for years, and should be very thoughtfully executed.

Re:Maybe not practical, but very very useful

[Stormie]

Database is one area that needs more conceptual information in the marketplace. You can know every practical command for your database system, but if you don't know how to design the system properly to begin with, you will have trouble and headaches from it forever.

A lot of people look at it like programming - they think, you "know" this database, OK, you're hired, just as they would hire someone who "knows" a programming language. At least Microsoft distinguishes between Database Administration and Database Design in their certification.

I know this because a previous employer sent all the programmers on an Administration course rather than a Design one. Then we had to design our database (luckily I knew a bit about this from prior experience). We couldn't even use our freshly-learned Admin skills because we didn't have enough privilege on the NT servers. :-)

Oh well, I've since moved to a larger company, where I only do front-end stuff. My database skills will now slowly stagnate.

Re:Maybe not practical, but very very useful

[xmedar]

The same problems occur with databases and OOing, you get people who know the commands but cannot generate a clean design in the first place. With every step in the process requirements-spec-design-implementation the cost of a mistake at the previous level is an other of magnitude larger. This generally leads to all sorts of problems, and I have seen many diverse projects fail because of this. You need a Linus, I guess you might call him a "design czar" and then let others learn from him to build up their skills. Also management need to understand that there are these differences, and that needs to be communicated more by us at the sharp end.

Trees

[Lozzer]

Chapter 7 discusses data heirarchies and trees. In a nutshell: there are no trees in SQL. The author is distressed by this.

Given that a foreign key is basically a pointer you can store trees in databases, it might not be pretty and there may not be easy way to read them and it might not be a good thing to do - but if you feel the need then get right in there. Of course I could be totally wrong about this...

Re:Trees

[VRisaMetaphor]

Yes, you can represent a tree in a relational database, but there is no mechanism in SQL to query on or return trees, and there is no tree datatype. I believe that's the author's point.

Re:Trees

[the eric conspiracy]

It's not hard to create a binary tree structure in a database without use of foriegn keys. Just make a parent and two child fields.

Re:Trees

[iseletsk]

It is extremally expensive when you try to traverse/search the tree that is built in such way. As you have to make query, get the results, and based on this results, make another query for the chield node, and iteration after iteration.

Re:Trees

[dewey]

Disclaimer: I have not read the book, so I'm commenting based solely on the review and the comments.

Trees can be implemented in databases, although, as Lozzer points out, it might not necessary be pretty. However, the review says there are no trees in *SQL*, which is the language used to get data out of databases.

Even if you implement a tree in your database, the usual way to access the data is by reading the "root" record, then using some programming language to follow the pointers to other records. You almost never try to follow the pointers using SQL, because that quickly becomes difficult.

SQL is designed to grab sets of records out of tables. It's good at doing set operations such as union, intersection, and so forth. A tree structure isn't really a set operation. It is specialized for operations like that. It is not the right tool for implementing trees.

That's disappointing, because you very often want to use trees and similar data structures. It means you need more than just SQL to implement a useful database system, but as long as you have those other tools at your disposal, things usually work out OK.

Re:Trees

[Bigboote66]

I think you mean CONNECT BY.

Unfortunately, Oracle's CONNECT BY isn't very useful - it only works if you have only one table in a query - it doesn't allow you to join the table containing the parent/child pointers to other tables. This is especially problematic if you tree structure is implemented this way:

CREATE TABLE Node
( NodeId NUMBER
, NodeName VARCHAR2(100)
, NodeValue VARCHAR2(100)
, CreateUser VARCHAR2(100)
, ...);

CREATE TABLE NodeRelationship
( ParentNodeId NUMBER
, ChildNodeId NUMBER
, FromDate DATE
, ToDate DATE
);

Oracle's CONNECT BY will not let you join the NodeRelationship table to the Node table. You can maybe make it work by creating a virtual table in your from clause that looks like this:

SELECT NodeName
, NodeValue
, Level
FROM ( SELECT ChildNodeId
, RowNum
, Level
FROM NodeRelationship
WHERE :interestingDate BETWEEN FromDate and ToDate
START WITH NodeId = :foo
CONNECT BY PRIOR ChildNodeId = ParentNodeId
) tree
, Node
WHERE Node.NodeId = tree.ChildNodeId
ORDER BY tree.RowNum

But it isn't documented anywhere what the behavior of RowNum is when used inside subqueries, so this technique makes me nervous.

In any case, it only works for Oracle. All other systems have to use client-side code or make use of temp tables or cursors to acheive the same result, both of which are not a part of standard SQL, which always gives academician's (like this book's author) fits, but is rarely a problem in the real world (unless you have to migrate to a new RDBMS).

-BbT

Re:Trees

[Beelzebubba]

While it's true that creating a tree structure is not difficult using SQL, it is rather difficult to maintain the integrity of the data, and also to manipulate the data or even query it in useful ways.

Some examples:

A tree should have one root level node (a node with no parents). This means that the column that holds the parent keys must allow nulls, but only in one row. A trigger or constraint must be employed to ensure this.

A tree cannot have any cycles. Let's say that you're using a tree to represent an org chart. Bill reports to Jenny. Jenny reports to Frank. You need to add a constraint to ensure that Frank can never report to Bill (because then he would be reporting indirectly to Jenny, his own employee). Again, this can be done, but requires triggers, and some crafty data design.

Just one more, I promise: A common kind of query for a tree is to return all the rows that have the same ultimate parent. For example, you want to know what the total payroll is for everyone that reports to Jenny or her subordinates. Not easy to do unless you go to special lengths in the data design.

An excellent treatment of working with trees and graphs in SQL is presented in Joe Celko's book: SQL For Smarties. This is an EXCELLENT resource.

Trees are a pervasive structure in the real world, the org chart is the most common example, but once you start looking, you'll find them everywhere. It would be a great blessing to see extensions to the SQL standard to handle trees more gracefully.

- Chris
brd27@hotspammail.com

Re:Trees

[iseletsk]

The SQL was created to query relational databases. And relational databases were created to manage tabular data. Such databases basically are not built to handle hierarchical (tree-like) structures, and as such they handle it purly. Oracles solutions (proprietrary, not addopted by other), is just a shortcut, but it has unessesary high performace hit, comparing to what can be done with the hierarchical (or object) databases.

Re:Trees

[Beelzebubba]

However, the loop/cycle detection is not included for purposes of maintaining integrity, i.e. as a constraint.

Also, the CONNECT BY predicate is an Oracle extension, rather than part of the SQL standard, so for many of us, is not a solution.

Don't get me wrong, it is definitely possible to deal with trees in SQL (I do it all the time), it simply requires a lot of overhead that could be nicely handled behind the scenes, thus improving performance, making code easier to read, standardizing behavior, et al.

- Chris
brd27@hotspammail.com

***************

Re:Trees (Score:0)
by Anonymous Coward on Thursday November 09, @12:52PM EST (#55)

Oracle:
Definition of table MYTREETAB
ID, NAME, PARENTID

SELECT NAME FROM MYTREETAB
START WITH ID = [INSERT ID FOR ROOTNODE HERE]
CONNECT BY PRIOR ID = PARENTID

Very useful. Depth first. Loop detection included. :-)

Re:Trees

[the eric conspiracy]

I didn't say it was good or efficient, merely that it can be done.

I am that idiot!

[AgentGray]

I'm one of the guys who has FileMaker Pro at home. I've even gone to their annual conference.

However, that doesn't mean I know it all. :) The company I work for swears by the application. I've been designing, developing, and maintaining a human resources package for them for the last 1 1/2 years.

Eventually we'll be running the system over a WAN form 8-12 different locations. I know that FileMaker will not cut it for the needs that we have. Maybe as a thin-client. I've been lobbying for a couple of months now for us to use a SQL/XML web based solution that does the same thing. It will lower our costs (one server and no software expenses - we could just use a browser as the interface).

The main location (and possibly one other) are diehard Mac users. So far I've been able to switch our heavy dependednt servers over to linux whether it's on a PPC or Intel machine.

FileMaker is a great program because there is hardly any learning curve at all. However, I don't think it has the power for long range, data intensive applications over a WAN.

Anybody have any ideas as to what might be better?

Re:I am that idiot!

[crazyfrenchmen]

One word ORACLE! The only thing is that you can't learn how to use it overnight. Good luck! P.s. I've been working with that monster for 6 years and i'm still learning.

Re:I am that idiot!

[GoldenBear]

and thats the reason that oracle may not be such a great solution, it's so hard to learn compared to other DB systems. i've been working on a project for over a year now that needs to use both SQL server and ORACLE as a backend. luckily my company decided to dump the oracle version last week, i couldn't be happier, such a pain to configure the clients. I do understand how powerful ORACLE can be when used properly, but for most projects SQL server is sufficent. And WAY cheaper. which was a factor that the original author mentioned.
if you are already skilled with ORACLE, then great, it's a powerful system. but it could be overkill for most projects.

Of course if you are in the *nix world then SQL server isn't for you. but there are still other options than oracle.

(does anyone else feel completely drained from this election?)

Re:I am that idiot!

[larkost]

I to am a FileMakerPro Developer. Actually I am just wrapping up my first commercial solution with it right now (within the next two hours), but for the question at hand, where I usually work in is more importatnt: WebObjects!

For the project I am finnishing up right now, FP5 is the right platform, but I did find myself a little constrained by the close association between tables (called databases in FP5), and reports/views/layouts. I would up having to create a lot of extra calculation fields to get the exact behavior that the cutomer wanted. Not a huge problem as the datatbase is only 5K records big (counting centeral records), but somethign the WO Developer in me shuddered at.

Well, my point here was that with WebObjects now in the $800 range, it is suddenly closer to the range yoiu are looking at, and it does come with the lite version of OpenBase (no seurity, but if you have LAN firewalling, you can deal with this untill you get management to pop for the full version, or another DataBase... no MySQL is not a good idea for WO Development, EOF does not play nice with it yet).It is my sicere hope that the FileMaker and WebObjects teams can be slowly brought together on MacOS X. I have no real evidence for this, but if you look at the directons that the two teams are moving it is not totally out of the question (WebObjects new easy to use "Direct to Web" and "Direct to Java Client" assistants, and FileMaker Pro's XML moves).

My recomendation, start looking into WebObjects!

Re:I am that idiot!

[Stigma]

A simple rule of thumb is that if your company decides to use Oracle, they need to hire a DBA.

Re:I am that idiot!

[KegDude]

I've been lobbying for a couple of months now for us to use a SQL/XML web based solution that does the same thing. It will lower our costs (one server and no software expenses - we could just use a browser as the interface).

Anybody have any ideas as to what might be better?

Yes - Visual FoxPro and Web Connection.

It's only for Windows, but if you use a browser as the interface, only the fileserver and webserver have to be Windows - the people accessing it can use any browser on any platform. This works out really well for our foreign clients, because there is no application to be installed anywhere. Their users just have to have a browser and an internet connection.

They can log in, enter orders, update information, view reports - all the pages are generated on-the-fly from the live database running at the client's office, served up by our Web Connection apps.

However, this is not for everyone - you definitely need to be a database programmer who understands stateless access, HTML, etc.

Re:I am that idiot!

[KickKat]

The web based solution sounds like a great way to go. I would suggest a good database system like SQL Server or Sybase ASA. For the web side of things, you have PHP, Perl, ASP, JSP, or, my favorite, ColdFusion. There are many options at many different prices.

DataBase Ease

[matth]

I've tried to setup several databses, both under Windows and Linux, and have found that many times
it's extremely difficult to setup databases and get them to work correctly. I think some company could benefit from writing some software that would have a very easy point and click, or very simple command line, creat a config file, type of engiene to write a database. And all you would have to do is drag and drop options. Of course, this could also open up another can of worms, in that then anyone and his brother might try to write a databse and succeed, but not know what to do when the databse crashes!

Re:DataBase Ease

[jerdenn]

Actually, both Sybase ASE 12 and SQL Server 2000 have GUI 'wizards' for creating databases (I'm certain that Oracle does, too, but as I'm not an Oracle user, I can't tell ya).

The problem with using the wizard is, of course, that one size doesn't really fit all circumstances. Database design requires skill and thought - for any sizeable project, it's not something you should just 'whip together'.

-jerdenn

Re:DataBase Ease

[Daniel Dvorkin]

Um ... actually, that's Filemaker (Pro, Server, etc.) It really is a tremendously easy-to-use DBMS with a lot of built-in functionality. But it doesn't scale up all that well, as my company recently found out, which is why we're going to SQL. If you're only going to working over a LAN or you want your remote users to access only _very simple_ databases, FMPro is fine. But if you want something that works well on a wide variety of connections, you really have no choice but the heavy-duty stuff. And right now, I think that's an inherent limitation, The Way Things Are. Another few years of steadily increasing data transmission rates and bandwidth, and it may be different.

And so this helps .... who?

[CyberKnet]

And so to boil it down the only people that this book will REALLY help are:

FileMaker Pro Owners

Linux Newbies with MySQL who think that alone makes them a DBA

Microsoft SQL Server Owners. Mandatory Microsoft Dig

Come on.. this book is hardly ground breaking, describes very little which is not basic knowledge. What use could a review of it on a tech website possibly have?

---

It misses an important chapter

[Anonymous Coward]

Ethics. Anyone who runs a database has an important role in making sure that the data is used only for ethical means. This means that it should not be used for spam, spying, or other illicit purposes.

My databases course actually had a lecture on the legal issues of data protection. It was pointed out in this how and why it is wrong to store data on people without their knowledge or permission. This book doesn't seem to discuss this

I'm not saying that the author is amoral in ommitting this, but I feel that people have a duty to consider how their knowledge is being used, and people need to be reminded about their responsibilities. Not mentioning this when you have an opportunity is simply wrong.

Re:It misses an important chapter

[chamo]

You mention legality and ethics.

Legality wise I assume the author is American and as far as I know their data protection laws are very scant, in comparison to the UK certainly. The point is that the book is available to an international audience and so the author can't really talk abou the legal issues.

Ethics is an interesting issue. Sure spam, whatever is a pain in the arse but one still requires a well strucured database to do it. Ethics are not an issue when it comes to writing a good database, whether the database is being used to store personal data for your hosipital patients or people you plan to kill is not an issue at a design level.

It seems to me to be a whole separate issue. Web design books don't discuss the ethics of creating an inflamatory site. I don't dispute the DB creator *should* be aware of these issues. I dispute whether you should criticise the author for omitting this topic.

Re:It misses an important chapter

[porlw]

Oracle programming guru Steven Feuestein uses a lot of ethical & non-ethical examples in his books and classes - check out this article where he explains himself.

Re:It misses an important chapter

[ichimunki]

Great article. One of the things I've liked about the O'Reilly books I've read is the humor and real world attitude (admittedly I've read mostly Perl books, so humor is important in preventing the onset of insanity-- or it may be a symptom of it, I can't tell which). Too bad Feuerstein writes mostly Oracle-specific stuff, since it would be great to read a database book with examples like those! Personally, I think I'd skip right over any chapter about ethics in a book about design principles or practical application. It's appropriate in a college curriculum perhaps to have it as an aside. But the method which includes getting you to think about it WHILE reading the book is excellent.

"Purchased knowledge" is a disease.

[AFCArchvile]

"Yes, I mean you, the one who's got a copy of Filemaker Pro at home and thinks he knows it all."

That's the way I feel. Every time someone buys Filemaker Pro or something similar, like Adobe Photoshop/Premiere/Illustrator/Go Live/etc., Macromedia Director, Visual Cafe, or JBuilder, they automatically assume that they are well educated in the topic just by making the purchase. This arrogance must stop before it spreads like a plague (wait a minute, that bum across the street is holding a copy of JBuilder, too late).

FileMaker

[guinsu]

A few years ago I worked at a company that did all their invoices with Filemaker and I had the task of updating the whole thing, both the look and adding/removing data fields. I never had worked with databases but found Filemaker a breeze to work with. Since then, I've learned Access and worked with SQL Server, but sometimes I miss Filemaker. I've considered getting my mother a copy for some database work she needs to do since she'd probably do better with Filemaker than with Access. Filemaker defintely has its place.

Re:FileMaker

[SCHecklerX]

A few years ago I worked at a company that did all their invoices with Filemaker and I had the task of updating the whole thing, both the look and adding/removing data fields. I never had worked with databases but found Filemaker a breeze to work with.

You obviously have never used older versions that had no concept of relationships before. What a friggin' nightmare pain in the ass. You had to write logic *IN THE FIELDS THEMSELVES* too. YUCK.

Everything we had to do with filemaker we had to pull some trick to do it. I was so happy leaving that job.

Humph...

[gregbaker]

Yes, I mean you, the one who's got a copy of Filemaker Pro at home and thinks he knows it all.

I'll have you know that it's a copy of Access. Thank you very much.

Greg

Re:Humph...

[Kozz]

What about my copy of "PFS File"?
*grins*


Quidquid latine dictum sit, altum viditur.

Not to be elitist, but...

[CaptainZapp]

from writing some software that would have a very easy point and click, or very simple command line, creat a config file, type of engiene to write a database. And all you would have to do is drag and drop options.

This is a bad idea. I'm aware that point & click, drag & drop GUI interfaces are very tempting to manage such complex systems as a relational database. Let me elaborate:

It gives joe end-user the impression that, hey, it's only a little Access after all. Planning an enterprise database that has to support 100s of users with guaranteed response times and throughput is never an easy task and requires quite some insight into the DB engine. Another issue is that desaster is at your finger tips: Are you sure ...? From using desktop applications we all know how easy it is to just click yes. The yes on such a window might be the sure path to major desaster.

An even worse issue is recoverability. It is a pain in the ass to create the scripts for every single database object, to version control them and to treat them like source code. Let me assure you that when the crap touches the ventilator you thank [whomever you believe in] that you went real slow, conservative and have scripts prepared to recover the db from scratch and re-apply the table data you where barely able to bulk copy from your corrupt database. When you dragged and clicked a bit, I can guarantee you, that there's no way to recover the proper database structure (Hey, but Joe, ran the reverse engineering option in March 98...)

The very, very best Sybase DBA I've ever gotten to know did everything with plain straight forward ascii scripts. It's a system that processes $10'000'000'000.00 of real cash every day. It's up and running since 1996 and if it's down for three days the first companies will file for bankruptcy. It handles up to 9'000'000 bookings a day (each consisting of 8 tpc/c transactions).

This guy never had a bad night since the system is in production.

How typical

[Anonymous Coward]

It's never enough to say, here's a review of some cool thing. If it remotely has anything to do with Linux, and appears on Slashdot, one must also issue an arbitrary slam of some related thing that happens not to be GPLed, in this case, FileMaker. FileMaker happens to be very good at what it does. People are moving in droves from Access to FileMaker, which is why FileMaker, Inc.'s financials keep improving. So what if it's not MySQL / Oracle / Postgres / the SQL-flavor-of-the-week? People who need an SQL database usually know they need it, and don't bother to consider low-end solutions.

FileMaker is good for small firms with relatively simple needs (Web storefronts, schools, and the like), because it's cheap, user-maintainable, and allows you to develop your applications rapidly.

Anyone who has ever wondered why some people have to put down Open Source whenever they wax poetic about their favorite commercial solutions, this is why. They're merely reciprocating.

Stamp out, eliminate, and eradicate redundancy!

[CraigoFL]

Chapter 8 covers redundancy, more or less an extension of chapter 4.

Am I the only one who laughed out loud over this one? :-)

2 ways of thinking

[rumba]

"We are designing a business-to-business application with shoppingcarts, orders, ordelines and other stuff with servlets and JSP.
The java part is clear. But the database... Pff, we are programmers not databasedesigners [sic]. Can anybody give us some help?" --from the Database Debunking site

Hackers and db admins seem to have different ways of thinking. The db admin may make a query where the coder would write a script to scan variables. 2 different ways of thinking that should be mastered, yet they are not always in the same head. Seems like a good team would have both heads in on the application design process. Sheesh. Don't write any code till you have those field names!

Re:Attention:

[avandesande]

Thank you sir, may I have another?

Hmph. Purple.

[martinflack]

I guess /. is only reviewing purple books from now on. Oh well.

Overview says it all ....

[opencode]

These are all topics I would consider "practical" (I would wonder how well these topics are covered, and if this book is worth purchasing, according to it's dedication to these areas) ... If by "practical" one means "getting DB2 to integrate with Oracle", that's a vendor-specific issue, and the vendors would be HAPPY to tell you how to do this ... and on this count, the book fails. But such is not the aim of this book.

No trees?

[denshi]

Chap 7 - no trees in SQL?

The hell with that. I use trees everyday with Oracle8. There are also 2 methods for tree queries in PostgreSQL.

It's worth remembering that objects are not stored on disk/ram in tree format - it's a matter of query, or how you view the objects. In C++/Java, a tree is viewed by how a function follows pointers. In SQL, all you need is foreign keys, and connect by (which is sort of a 'ORDER BY :PREVIOUS.PK_ID = :CURRENT.PARENT_ID' - which is obviously not part of SQL92, but simple nonetheless.)

Depends on the context

[CaptainZapp]

I dont damn GUIs to the ground, but prefer to use them for check up and monitoring purposes.

Generating scripts by hand ? Nope. During the years in which you work with RDBs you acquire a tool chest. Mostly shell and perl scripts which are essentially fed some templates and do the job.

The resulting scripts however are regarded as the source, kept up to date and are version controlled. Not that much of a hussle really.

Of course you can usually reverse engineer the databases with most GUI tools. But when was that done last time ? Are all changes applied ? Is it done consequently.

Distributed environments are a good example. There are tools that do the job in the meantime. But, from architecting and designing quite a few distributed systems here's my breakdown on the effort:

Gathering business requirements 20 - 30 %

System Architecture 30 - 40 %

System Design 20%

Setting up object generation infrastructure 10%

Implementing, testing fixing: The rest

The more you invest into the architecture and design the less fixing, debugging, tinkering and praying is applicable. So the part where the gui really helps is minimal. Chances that you seriously fuck up a really complex environment are exorbitant.

The basics are important...

[Da VinMan]

I am continually surprised by the number of developers I have met who routinely design and implement tables/views/indexes who have no knowledge whatsoever of normalization, etc. Now, some knowledgeable people will take you to task for getting religious about normalizing tables, but these folks don't have enough knowledge to break the rules; they don't even know there are rules.

Do you really want people who work for you to be designing databases on a hunch only? Wouldn't you rather there be some sembelence of real engineering? Granted, even strict adherence to normalization isn't REAL engineering (in the hard-core measure it, prove it, test it kind of way), but it's better than nothing.

Anyway, I'm all for any material which attempts to alleviate this sort of (complete preventable) ignorance. It can only help.

Book Name Complaints

[CerebusUS]

A friend of mine wrote the following book Crytography for Visual Basic: A Programmer's Guide to the Microsoft CryptoAPI and got into a fight with his publisher over the name of the book.

While I agree the final name is a bit wordy, it's much more descriptive than the publisher's suggested title: Securing Your Applications -- no reference to VB or Cryptography.

Database Ethics

[wwphx]

canopener.open
canofworms.load
grindingnoise.sound.play


Ethics. Hmmm, gotta get a bigger can. Ethic value can be situationally/occupationally dependant. I have written databases for conducting surveilance, including connecting such databases into other databases in order to produce capability that the original db's creator didn't foresee, mainly because he did not know what other databases I had.

Now, this is ethical for me: I work in law enforcement. If a friend told me he was stalking his ex and wanted me to write a DB for him to log it, that would not be ethical.

In both cases, a person's privacy is being invaded. In the former, law enforcement is trying to catch criminals. In the latter, a sicko is trying to spy on a private individual who has done no wrong. Both are individuals.

Now, you can argue "for the benefit of society", but then you can bring in Libertarian-esque arguments that everyone is entitled to full privacy.

Where do you draw the line?

--

Recursive SQL reference

[K-Man]

For info on how SQL3 handles recursive queries, IBM has a good tutorial here (remove space in url introduced by slashdot to get correct page). Traversing trees is a set-based operation, but it requires a recursive reference in the set definition, eg

X = { the root node } union { y: y is a child of some node in X }

This is perfectly well-defined; it's just a matter of getting SQL to handle it, as is done in SQL3.

Re: another option? 4d

[kuma]

sql rdms might be nice, but design is a pain, and the web interface adds many layers of technology that might go wrong.

that said, you could use 4d for that, but what 4d really gives you are cool rad tools and a network client you can fall back on if your browser dreams don't pan out.

i currently develop/administrate a 4d publishing system that is used to publish some 16000 catalog pages per year... yeah, and i too would love migration to oracle and xml and blah-blah, for a chance to cash-in on a consulting gig someday, but i serve editors/writers/art directors well and lord over buckets of code, routinely passing a dozen or more parameters through a chain of methods indirectly via pointer to a blob, this is not filemaker pro! you can code quick and stupid, or commented and clever, with complete control of scope in a multi-processing/multi-user client-server system with an awesome debugger...

uh, the bottom line: you can be a compsci guru and not puke, and then have some confidence the organization won't fall apart when you leave--it just ain't that tough to work with, otoh 4d is not a popular platform.

postgreSQL

[denshi]

check the bboard on openacs.org

The methods are a new DB API function db_dfs (depth-first search); and an older tcl-layer fxn which I am unfamiliar with, but I know of its existence. Ask at the above.

Re:Is this the Fabian Pascal I remember?

[mercuryvapor]

How many Fabian Pascals' could there be? I too remember him there. He was smart, and sometimes terribly abusive. One of the previous postings in this thread was from someone who heard him speak. Sounds like Mr. Pascal still has an attitude.

This book sucked

[selectspec]

With respect to those concerned, this book was pathetic. Any book that recommends a database written in java running in a JVM isn't worth your time.