Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Planning Ahead

Before you start creating any tables, we suggest that you take some extra time to plan out your intended database objects by deciding the names, types, and purposes of all columns within each table. This can help you to be consistent with table naming structures, which in turn helps you more easily read and construct "legible" queries and statements.

In addition to taking the somewhat semantic considerations just described (names, types, and purposes), it is important to be sure that each table's relationship to each other table is clearly defined. This can be an important point of table design, as you do not wish to redundantly represent large amounts of data, nor do you want to end up omitting important data from one table by misunderstanding the needs that must be satisfied by your implementation.

As an example, consider again the Book Town books table, from Table 3-1. This table holds an internal Book Town identification number for each book, the title, author identification number, and a subject identification number. Notice that rather than storing the name of the author, and rather than storing a text representation of the subject of the book, simple identification integers are stored. These identification numbers are used to create relationships to two other tables: the authors, and subjects tables, whose partial contents are shown in Table 3-26 and Table 3-27.

Table 3-26. The authors table

id

last_name

first_name

1809

Geisel

Theodor Seuss

1111

Denham

Ariel

15990

Bourgeois

Paulette

2031

Brown

Margaret Wise

25041

Margery Williams

Bianco

16

Alcott

Louisa May

115

Poe

Edgar Allen

Table 3-27. The subjects table

id

subject

location

0

Arts

Creativity St

2

Children's Books

Kids Ct

3

Classics

Academic Rd

4

Computers

Productivity Ave

6

Drama

Main St

9

Horror

Black Raven Dr

15

Science Fiction

Main St

By keeping the author and subject-specific data separate from the books table, the data is stored more efficiently. When multiple books need to be correlated with a particular subject, only the subject_id needs to be stored, rather than all of the data associated with that subject. This also makes for simpler maintenance of data associated with book subjects, such as the location in the store. Such data can be updated in a single, small table, rather than having to update all affected book records with such a modification. The same general principle applies to the authors table, and its relationship to the books table via the author_id.

Thoughtful planning can also help to avoid mistakes in choosing appropriate data types. For example, in the editions table, ISBN numbers are associated with Book Town book identification numbers. At first glance, it might seem that the ISBN number could be represented with a column of type integer. The design oversight in this case would be that not only can ISBNs sometimes contain character data, but a value of type integer would lose any leading zeroes in the ISBN (e.g., 0451160916 would become 451160916).

For all of these reasons, good table design is not an issue to be overlooked in database administration.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire