Columns and Datatypes
Tables are made up of columns. A column contains a particular piece of information
within a table.
Column
A single field in a table. All tables are made up of one or more columns.
The best way to understand this is to envision database tables as grids, somewhat like
spreadsheets. Each column in the grid contains a particular piece of information. In a
customer table, for example, one column contains the customer number, another
contains the customer name, and the address, city, state, and ZIP code are all stored in
their own columns.
Tip: Breaking Up Data
It is extremely important to break data into multiple columns correctly.
For example, city, state, and ZIP code should always be separate
columns. By breaking these out, it becomes possible to sort or filter data
by specific columns (for example, to find all customers in a particular
state or in a particular city). If city and state are combined into one
column, it would be extremely difficult to sort or filter by state.
When you break up data, the level of granularity is up to you and your
specific requirements. For example, addresses are typically stored with
the house number and street name together. This is fine, unless you
might one day need to sort data by street name, in which case splitting
house number and street name would be preferable.
Each column in a database has an associated datatype. A datatype defines what type of
data the column can contain. For example, if the column were to contain a number
(perhaps the number of items in an order), the datatype would be a numeric datatype.
If the column were to contain dates, text, notes, currency amounts, and so on, the
appropriate datatype would be used to specify this.
Datatype
A type of allowed data. Every table column has an associated datatype
that restricts (or allows) specific data in that column.
Datatypes restrict the type of data that can be stored in a column (for example,
preventing the entry of alphabetical characters into a numeric field). Datatypes also
help sort data correctly and play an important role in optimizing disk usage. As such,
special attention must be given to picking the right datatype when tables are created.
Caution: Datatype Compatibility
我的内容管理
展开
