Transcription
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.