Database tables

Computer Science · Databases

tap a part of the table

Peeled on: the data type of each field sits above its field name.

Tap a part of the table to see what it is. Peel the data types on and off too.

What you need to know

• A database stores its data in tables. Each table holds data about one type of thing, such as pupils. That type of thing is the entity.
• Each row is one record: everything the table stores about one pupil.
• Each column is one field: one kind of detail, like surname, that every record has.
• Have a goA school wants to record every pupil's form group in the Pupils table. Do they add a new row or a new column?

  A new column, which is a new field.

  A row is a whole pupil, so a row would add another pupil; a new kind of detail for everyone is a new field.

• Each field has a field name, written at the top of its column, such as Surname.
• Each field also has a data type, such as integer, text or date. Every record uses that type for that field.
• Have a goDev is sure it is fine to type 'ask me later' into one pupil's DateOfBirth, which is a date field. Is he right?

  No. That record would hold text in a date field.

  Data type belongs to the field, so every record has to use it; one record cannot choose its own.

• Every record has the same fields, with the same names and data types. No record gets fields of its own.
• The primary key is a field whose value is different in every record, so it picks out exactly one record.
• Choose a primary key that can never repeat. A key that repeats stops identifying one record.
• Have a goA shop gives every customer a customer number. Why might that make a better primary key than the customer's full name?

  A number can be different for every customer, but two customers could share a name.

  A primary key has to be different in every record so that it picks out exactly one record.

• One database can hold several tables. They can be linked through a field they have in common.

The big picture

A database table holds data about one type of thing. Each row is a record and each column is a field. Every record has the same fields, with the same names and data types. The primary key is different in every record, so it identifies exactly one. A database can hold several tables, linked through common fields.

⚠ Watch out

Thinking each record can have fields of its own, or its own data type for a field. They can't. A table is a grid with rules, so every record has the same fields with the same names and data types.

Memory hook

A column is like a pillar: it holds up one kind of detail all the way down. A row runs across like a line of people: one whole pupil. Pillar = field. Line = record.

Check yourself

A Teachers table has the fields TeacherID, Name, Subject and Room. Two teachers can both teach Maths. Which fields could repeat across records, and which field would make the best primary key?