In relational databases a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A table has a fixed number of columns, but can have any number of rows. Each row is identified by the values appearing in a particular column subset which has been identified as a candidate key.
Databases store relational tables/indexes in memory or on hard disk in one of several forms:
* ordered/unordered flat files
* hash buckets
* B+ trees
Each of these have various advantages and disadvantages - discussed further in the articles on each topic. The most commonly used are B+ trees and ISAM.
Oracle recognizes seven types of tables:
* Heap organized tables
* Index organized tables
* Index clustered tables
* Hash clustered tables
* Nested tables
* Global temporary tables
mySQL recognizes six types of tables:
* BerkeleyDB (BDB)
Object databases use a range of storage mechanisms. Some use virtual memory-mapped files to make the native language (C++, Java etc.) objects persistent. This can be highly efficient but it can make multi-language access more difficult. Others break the objects down into fixed- and varying-length components that are then clustered tightly together in fixed sized blocks on disk and reassembled into the appropriate format either for the client or in the client address space. Another popular technique involves storing the objects in tuples (much like a relational database) which the database server then reassembles for the client.
Other important design choices relate to the clustering of data by category (such as grouping data by month, or location), creating pre-computed views known as materialized views, partitioning data by range or hash. Memory management and storage topology can be important design choices for database designers as well. Just as normalization is used to reduce storage requirements and improve the extensibility of the database, conversely denormalization is often used to reduce join complexity and reduce execution time for queries.
Related Code Snippets: