A database stores data in tables. When you design a database you need to tell it what tables you want and what fields need to be included in the table. You can think of fields as being the headings for each record in the table. Field names should be as descriptive as possible, for example, in a database table of student records the fields for each record may be Student Id Number, Forename, Surname, Gender and Course. The fields in a table can be text, number, and even pictures.
A table in a database may include records that consist of fields as show below.
|Field Name||Data Type||Description|
|CD Number||Number||Reference number|
|Artist||Text||Name of Artist|
|Position||Number||Place in top 200 Album Chart|
Example records added to the database table for the above table structure is shown in the following extract.
|100||Gold Greatest Hits||ABBA||
|101||Sgt Pepper’s Lonely Hearts Club Band||The Beatles||
|102||(What’s the story?) Morning Glory||Oasis||
If the database stores the data in one large table it is often called a flat file database, as it has only one table.
If the database stores its data across many tables it is called a relational database. Each table will have a field which is a unique identifier like a Customer Id Number, or a Student Reference Number.
These unique identifiers are called primary keys and are set when you design your table.
Relational databases are can very powerful and can store millions of records. Every line in a table is referred to as a record. Each record can have more than one field e.g. Customer ID Number, Customer Name, Customer Address etc.
In a relational database there has to be a common linking field to each table, so you might have in your Orders table a primary field like Stock ID, Stock Description, Stock Quantity and a foreign key called Customer Id, which links the Orders table to the Customer Table.
That way when an order is placed, the person updating the database will be able to tell which customer placed an order, and get their address from the Customer Table.
They will also be able to send the customer an invoice for the order they placed.