Database Table: Design & Conventions

Instructor: Katherine Wenger

Kate has a Bachelors, Masters, and is a Ph.D. candidate in the fields of Information Technology and loves teaching students

A database consists of at least one table, the components of which are standardized regardless of the content found within the table. This lesson defines and provides examples of the design and conventions used to create a database table.

The Database Table

The database table is the most basic structure of any database. The database table is comprised of rows and columns. A spreadsheet consisting of rows and columns is the most familiar image of a database table. The table structure provides organization of content, which allows for easy retrieval and storage of data. The design and conventions of a database table are standardized regardless of the content found within or the organization using it.

In this lesson, we will cover the following terms related to the database table:

  • Scheme
  • Attribute
  • Tuple
  • Field
  • Naming conventions
  • Unique identifier
  • Database language

Design and Conventions

A database table is given a name. The name of the database table is referred to as the scheme. The scheme name should clearly identify the reason for and the contents within the table. For example, a scheme name of customers indicates that the table contains data about customers.

Attribute names correlate with column headings. An attribute name in the table should indicate the type of data that will be stored in rows. For example, attribute names found in the customers table could be as follows: last_name, first_name, phone, and id.

last_name first_name phone id

In the above example, proper naming conventions are used. Notice that the column names are in all lowercase letters and separated using an underscore. The column named id refers to an account number. When considering database conventions, using the term id to refer to a unique identifier is considered best practice.

Once the scheme name and attribute names have been established, actual data can be entered into the rows. It is worth noting that a database table need not have any rows, in particular if it is anticipated that data will be added in the future. A row is called a tuple in a database table.

The tuple coordinates to the attribute data, as such:

last_name first_name phone id
Doe John 555-675-7700 40342

Given the image above, the tuple indicates that John Doe has a phone number of 555-675-7700 and an account number of 40342.The order in which tuples are placed in a database table is irrelevant, in other words, customer names need not be organized in alphabetical order.

Field

A database table contains tuples which comprise of data as indicated by the columns. The individual piece of data that falls in the intersecting cell between tuple and attribute is called a field. A field, in the customer table example could be John, which is the cell where the tuple intersects with the attribute first_name. With regard to the data contained within a tuple, not all fields need contain data. In other words, the phone number may be left blank, if allowed.

Unique Identifier

It is essential to that a table be designated a unique identifier. In the most basic sense, a column name is designated as the primary key, or as the unique identifier, in which a field in that tuple contains data that cannot be duplicated within the table. The primary key would be the account number, which in our example is 40342, as this is a unique identifier that is assigned only to the John Doe.The field under the column identified as the primary key may never be left blank as it is the unique identifier.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support