Designing Tables
When you design a database, you decide what tables you need, what type of data goes in each table, who can access each table, and so on. As you create and work with tables, you continue to make more detailed decisions about them.
The most efficient way to create a table is to define everything you need in the table at one time, including its data restrictions and additional components. However, you can also create a basic table, add some data to it, and then work with it for a while. This approach gives you a chance to see what types of transactions are most common and what types of data are frequently entered before you commit to a firm design by adding constraints, indexes, defaults, rules, and other objects.
It is a good idea to outline your plans on paper before creating a table and its objects. Decisions that must be made include:
- Types of data the table will contain.
- Columns in the table and the data type (and length, if required) for each column.
- Which columns accept null values.
- Whether and where to use constraints or defaults and rules.
- Types of indexes needed, where required, and which columns are primary keys and which are foreign keys.