================================================ SEARCHDATABASE.COM'S DBA Tips and Tricks May 30, 2001 ================================================ Welcome to the searchDatabase.com DBA Tips and Tricks newsletter! Today's tip, "Alternatives to the relational database model" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci556020,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "Oracle PL/SQL Best Practices" By Steven Feuerstein In this book, Steven Feuerstein, widely recognized as one of the world's experts on the Oracle PL/SQL language, distills his many years of programming, writing, and teaching about PL/SQL into a set of PL/SQL language "best practices"--rules for writing code that is readable, maintainable, and efficient. Too often, developers focus on simply writing programs that run without errors--and ignore the impact of poorly written code upon both system performance and their ability (and their colleagues' ability) to maintain that code over time. Oracle PL/SQL Best Practices is a concise, easy-to-use reference to Feuerstein's recommendations for excellent PL/SQL coding. http://www.digitalguru.com/dgstore/product.asp?sku=0596001215&dept%5Fid=233&ac%5Fid=58&accountnumber=&couponnumber= ************************************************ "Alternatives to the relational database model" By Ryan Stephens & Ronald Plew Last week, we described the most commonly implemented database model: the relational database. However, all DBA's should know about the five alternatives that exist, as reviewed in this excerpt from Ryan Stephens & Ronald Plew's new book "Teach Yourself Database Design" (Sams, 2000): Flat-File Database Model ------------------------ Before vendors such as Oracle and Microsoft started developing database management systems that run on a computer, many companies that were using computers stored their data in flat files on a host computer. The use of flat files to store data was predominant in the mainframe era. A flat-file database consists of one or more readable files, normally stored in a text format. Information in these files is stored as fields, the fields having either a constant length or a variable length separated by some character (delimiter). In this example (shown on the searchDatabase.com site), there are obviously three fields: an identification number, the author name, and the title of the book. Each field has a constant length because the identification number always starts in column #1 and ends in column #4, the author name starts in column #6 and ends in column #25, and so on. The following is an example of a flat file, with variable length fields separated by a given delimiter: 1234:Ernest Hemingway:For Whom the Bell Tolls 5678:Charles Dickens:Great Expectations 4321:Ernest Hemingway:A Farewell to Arms 8765:Jack London:White Fang 4523:Jack London:Call of the Wild 3456:Mark Twain:Adventures of Huckleberry Finn There are also three fields in this example. Each field is separated by a colon. The fields are not a constant length in the example. When using field separators, you should make sure that the field separator is not a character that can be found in the data. [Note - Sometimes flat files are created and used to migrate data from one database implementation to another, particularly with relational databases.] Every flat-file database system is different because companies store different data and companies have different needs. After a flat-file system has been created and the data has been stored in these files, a method must be devised in order to retrieve the data, create new records, update records, or delete records. For instance, if you wanted to get a list of each one of the titles authored by Jack London, you would have to find every record that had an occurrence of "Jack London". In addition to finding each record with the string "Jack London" in the second field, you would want to filter the data so that only the title of the book, which is the third field, is retrieved. The problem of accessing the data requires a collection of programs to be written that access the information stored in the flat files—unless you expect the user or customer to search the flat files themselves in order to access the data, which would be quite unacceptable. One of the main problems with a flat-file database system is that not only do you have to understand the structure of the files, but also you must know exactly where data is physically stored. Additionally, your database will probably require numerous flat files, which might have data related to other data stored in some other file. Managing data relationships is a difficult task in the flat-file database environment. The following is an overview of the drawbacks of a flat-file database: * Flat files do not promote a structure in which data can easily be related. * It is difficult to manage data effectively and to ensure accuracy. * It is usually necessary to store redundant data, which causes more work to accurately maintain the data. * The physical location of the data field within the file must be known. * A program must be developed to manage the data. Hierarchical Database Model --------------------------- A hierarchical database is a step above that of a flat-file database, mainly because of the ability to establish and maintain relationships between groups of data. The architecture of a hierarchical database is based on the concept of parent/child relationships. In a hierarchical database, a root table, or parent table, resides at the top of the structure, which points to child tables containing related data. The structure of a hierarchical database model appears as an inverted tree. In this example (shown on the searchDatabase.com site), Publishers is the root table. Publishers has two child tables: Authors and Bookstores. A publisher has many authors to whom it contracts, as well as many bookstores to which it supplies books. Authors is a parent table to the Titles table, as Bookstores is to Inventory. Titles is a child table of Authors, as Inventory and Orders are child tables of Bookstores. One of the problems with this hierarchical layout is that redundant book title information would have to be stored in the Inventory table because there is no direct association between Authors and Bookstores. [Note - As previously mentioned, it is a good practice to store as little redundant data in a database as possible. One of the implications of having redundant data is that data will have to be modified multiple times, depending on the level of redundancy. Processes must be in place to ensure that all appropriate data is updated when necessary to avoid inconsistent data in the database.] A parent table can have many child tables, but a child table can have only one parent table. To get to a child table, the parent table must first be accessed. Related tables within the hierarchical structure are linked by a pointer, which points to the physical location of a child record. Benefits of the hierarchical model over the flat-file model: * Data can be quickly retrieved. * Data integrity is easier to manage. Drawbacks of the hierarchical model: * Users must be very familiar with the database structure. * Redundant data is stored. For the rest of this article, including a discussion of the Network, Object-Oriented, and Object-Relational database models, click here: http://www.searchDatabase.com/tip/1,289483,sid13_gci556020,00.html ------------------------------------------------ FOR MORE INFORMATION ------------------------------------------------ The Best Database Basics Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281561,00.html The Best Database Design Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax283234,00.html Ask your toughest design questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Check out our new "Ask the Experts" feature! We have Database Design, SQL, Oracle, DB2, and SQL Server gurus waiting to answer your technical questions: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any techtarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
