Page 180 - DCAP104_EXPOSURE_TO_COMPUTER_DISCPLINES

Unit 8: Database System

8.3.1 Relational Databases Notes
The most common database model in use today is that of the relational database — others include
hierarchical databases (where data is organised in ‘trees’, like an organisation’s management
structure), and flat file databases (where data is stored in ‘records’ in a text document).
In a relational database, data is stored in tables. The columns are called fields and the rows are
called records. So, for example, a table might have two fields — firstname and lastname. If you
then added a record to this table, it could be ‘Bob’ and ‘Smith’. Instead of just having that data, you
have labelled it with what it is, and that lets you refer to it and search through it much more easily.
Where the ‘relational’ part is really significant, though, is when it comes to the way tables in a
database relate to the other tables. Each record of each table has an ID number (technically known
as the ‘primary key’) — for example, the Bob Smith record might be ID number 123. This then
lets you refer to his record in a new table.

Let’s say you were storing records of people’s orders. You could have two columns: customer
number and date. This lets you simply store 123 and the date in the table each time Bob Smith
orders from you - the relational nature of the database will tell you later on that customer number
123 is Bob Smith. When it comes to things like, for example, storing posts made by multiple
authors, this is powerful.

8.3.2 Three Rules for Database Work
Remote development is slow and difficult.
Avoid using a shared database at all costs, as they ultimately waste time and help produce bugs.

Always Have a Single, Authoritative Source For Your Schema
Ideally, this single source will be your source control repository (see rule #3). Consider the
following conversation:
Developer 1: It’s time to push the app into testing. Do we copy the database from Jack’s machine,
or Jill’s machine?
Developer 2: Un’t remember which one is up to date.
Developer 1: We’re screwed.
Everyone should know where the official schema resides, and have a frictionless experience in
getting a fresh database setup. I should be able to walk up to a computer, get the latest from source
control, build, and run a simple tool to setup the database (in many scenarios, the build process
can even setup a database if none exists, so the process is one step shorter).
How you put your database into source control depends on your situation and preferences.
Any decent O/R mapping tool should be able to create a database given the mappings you’ve
defined in a project. You can also script out the database as a set of one or more files full of SQL
DDL commands. I generally prefer to keep database views and programmatic features (including
functions, triggers, and stored procedures) as separate files — but more on this in a later post.
Always Version Your Database

There are many ways to version databases, but the common goal is to propagate changes from
development, to test, and ultimately to production in a controlled and consistent manner. A
second goal is to have the ability to recreate a database at any point in time. This second goal is
particularly important if you are shipping software to clients. If someone finds a bug in build
20070612.1 of your application, you must be able to recreate the application as it appeared in that
build - database and all.

LOVELY PROFESSIONAL UNIVERSITY 173