Tables may be the foundation of your database, but you need something to hold
them up.
Columns!
Every table in your database will have at least one column.
Each of these should hold values for one aspect of the objects the table stores.
For example, I've got my table of toys here and each toy would be a row in the table.
Now I might have columns to store things like the color, the weight, and
the cuddliness factor. :)
When you're creating columns there's two things you need to
decide; what to call them and their type.
When naming columns call them something meaningful.
For example I could call these Jeff and Bob but that doesn't tell
me what information they hold.
Better to have boring names like color, toy name, and so on.
Otherwise it just gets confusing.
Far more important is to choose the correct
type for your columns.
Get this wrong and not only will your tables look silly,
you'll never be invited back to architects club.
But most importantly your tables just won't stand over time.
Wrong data types can lead to all sorts of unexpected errors and performance
problems, so let's look at common data types and when you use them.
Numeric columns store things which are, well, numbers.
for example the price of a toy, its size, weight, and so on.
Oracle database has several predefined numeric types.
Each have different precisions.
Choose the appropriate type based on the data you
want to store.
No, not this kind of date.
Calendar dates!
These store when something happens or is planned to happen.
It's really important that you use date data types when you do
this.
Not numbers. Not strings.
Dates!
Dates are stored down to the second.
If you need to be really precise, check out timetamps.
These go right down to nanoseconds.
And, timestamps allow you to do something dates cannot:
store time zone information!
This is great if your colleagues work in different parts of
the world.
It's 1:00 in the morning!
0 1 0 1 1 0 0 1 1 1 0 1 0 0 1 0 1 0 1 0 1.
Blob.
Strings hold general purpose text.
Descriptions names or your innermost thoughts you can also use these to store
unstructured data.
The primary data type for these it's varchar2, but if
you're storing huge amounts text, use a clob.
That was a quick rundown of the main data types available in Oracle Database
There are other, more specialized data types available too.
For more information on those or any of the
other stuff we've discussed so far take a look at the links down in the
description. But remember choosing the correct data type is one of the most
important decisions you can make when building a table.
Get this wrong and you could be regretting it for years.
So now you know how to create columns and
tables, but that still leaves an important decision: How do you decide
what goes in which table and in which column.
We'll take a look at that in the next episode.
Thanks for watching subscribe to my youtube channel to learn
more about database development and see SQL magic.