• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 12. SQL Syntax > Guidelines for Building a Table

Guidelines for Building a Table

The following suggestions make table creation easier.

  1. Always declare a primary key. The basis of relational databases is that each table has a way to refer to exactly one row. SQL can take care of this so that we don't have to. All we have to do is choose a column (or set of columns) to be our primary key. It makes deleting an entry easier and selecting a specific record faster. And it takes almost no effort on our part.

    There are a few exceptions, of course. You do not need a primary key, for example, if you plan to use the database as a very simple mailing list and will never refer to one particular column. Or you might not use a key if you add to a table orders of magnitude more often than you retrieve from it and retrieval time is less of an issue than update time (each primary key is an index and adds time to the update time). An example of an update-intensive application is a real time data collection application. It's better to create a key and not use it than need it and be unable to get to it.

  2. Blast information into “atoms.” It is easy enough to concatenate a couple of pieces of information to form larger, more complex expressions, but breaking up text into smaller pieces involves parsing. We all know a few (very few) people who enjoy text parsing, but most of us don't. Let's assume that we want to avoid the necessity. Why would we want to store someone's entire name as one string when we can store a first name and a last name (and a middle initial too). It's easy enough to concatenate “Jesse” and “M” and “Jacobson” to make “Jesse M Jacobson,” but we can also quickly make “Mr. Jacobson” or “Jesse” out of those fields. The smaller the data chunks, the more flexibility we have in how we use the data.

    Consider an algorithm that would correctly parse the names in Table 12.2.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint