A table is a collection of data about a specific topic organized into columns called fields, and rows called records.
Guidelines for creating tables
*note: My examples are taken from Access, but the guidelines are basically the same for other RDBMS.
...but first of all WHAT IS THE DEFINITION OF TABLE?
|
A table is a collection of data about a specific topic organized into columns called fields, and rows called records. |
|
Now the guidelines...
1. Use numeric data types (currency or number) ONLY if calculations are to be performed.
|
Ok, but what is a data type?? A data type is a characteristic of a field that determines what kind of data it can store. There are different different data types, such as:
*note: data types in SQL may vary a little, but in essence they work in the same way. For example VARCHAR, CHAR, LONG, NUMBER, DATE etc |
|
Ok, but how do I know what type of data to use?
It is important that you know the differences between data types and how they are used for different purposes... Here is a description of different data types in SQL.
char(size) |
Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. |
varchar(size) |
Variable-length character string. Max size is specified in parenthesis. |
number(size) |
Number value with a max number of column digits specified in parenthesis. |
date |
Date value |
number(size,d) |
Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. |
2. Make the lengths of the character columns long enough to accommodate future values
|
But also remember that... Minimizing storage space is also a good thing to do because smaller data sizes can be processed faster and require less memory.
|
|
3. Use Primary Keys!
|
|
Ok, but what is a primary key? A primary key is a field or set of fields that uniquely identifies each record stored in the table. Once you have designated a primary key no null values can be entered in the database. For instance, your SSN is a good examnple of a primary key. And why is it necessary to have a primary key in our tables? It is not necessary, but it really helps a relational database to retrieve information stored in separate tables by using queries, reports and forms. |
4.Foreign keys must have the same data type as the primary key to which they are related.
|
If you try to create a relationship in a table where the primary key is an auto number and the foreign key is a character, then the relationship won't be created. It is necessary that the two fields have the same data type, in this case auto number for the primary key, and number the foreign key. |
|
This is also important for fields that will be compared often or used in calculations together.
|
Remember that
you want to compare
|
to
|
5. Try to use self explanatory names for every table and also for every field.
| Remember that it is very possible that you will not be the only person using the database , and you don't want other people to get confused with unclear names. For instance, instead of using add for a field name use address. "The more detail you can provide to other users of the database , the better your database will be." |
|
6. Don't leave spaces between the words of the name of your table.
| It is recommended that you separate every word in the name of your table and field names with an underscore . For instance, instead of employee name you can use employee_name. This will facilitate SQL queries. |
|
7. Descriptions
|
The field description is optional, but is very helpful to list the properties of the field so that every person that works with the database knows exactly what kind of information is stored inside that field. |
|
8. Use constraints to determine how the data must be entered in each field.
| Ok, I'll use
them, but can you explain first what is a constraint... A constraint is a a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column, they must all be unique. Another popular constraint is "not null" which specifies that a column can't be left blank |
|
Questions your should be able to answer:
1. What is a constraint?
A rule associated with a column that the data entered into that column must follow.
2. Mention a popular constraint
Not null, which specifies that a column can't be left blank
3. Is it possible to perform calculations between 2 fields that have different data types?
No, you can only compare and perform calculations between fields that have the same data type.
4. What is a primary key and why it is important in a table?
A primary key is field or set of fields that uniquely identifies each record stored in the table, and it is important because it helps a relational database to retrieve information stored in separate tables by using queries, reports and forms.
5. What is the ideal size for a column?
You have to find a size not so small so that you can accommodate future values. But also not so big because smaller data sizes can be processed faster and require less memory.
6.What is a data type?
A data type is a characteristic of a field that determines what kind of data it can store. There are different different data types, such as: auto number, number, text (in Access), and char, varchar, and date in SQL.
7. What is a table?
A table is a collection of data about a specific topic organized into columns called fields, and rows called records.
References:
*Microsoft Access Help
*http://www.sqlcourse.com/create.html
This website has a very easy to follow interactive tutorial in Access.
*Modern Database Management (6th Edition by Jeffrey A. Hoffer (Author), et al.
*Dr. Kirs slides (specially "An introduction to Structured Query Language").
