Developers

Tutorial: Understanding Databases
Level: Beginner
Added: 26th March 2004
Author: Steven Wojciechowski (forum name: Steviepunk)


Databases are wonderful things, they are almost essential for doing many of the things that can make a website special; user login, user profiles, forums, etc. However, before jumping into having ASP or PHP access the database it is essential that you know how to use the database in the first place.

There are a number of database type, each of which has different methods of access, the commom database formats that you are likely to be using with a website are Access, MySQL and SQLserver. The specific format is not too important, the important aspects are:
1. You can access it and management it (create tables, add, edit and remove data)
2. Your chosen webserver script languange has a method to access it
3. Your Webhost will support it

These are all things you need to consider, however, the purpose of this tutorial is to go over the basics of database use, these will apply to any database that you would use.

The Basic Elements Return to top

There are five basic elements to a database that you will need to be aware of:

Tables
Tables are the data structures that contain the information that you put into your database.

Fields
These are the individual data elements that are contained with in a Table

Key
The Key field is the field that the database uses as the unique identifier for each record

Relationships
This is where databases start to get a little more complicated, Relationships are the defined information that describes how each table in the database links together. This is why you will hear about database systems such as MySQL and Access being referred to as Relational Databases, you do also get Non-relatoinal Database systems but they are not something you would be likely to come across in basic website developement.

Queries
These are the data requests that you use in order to get data out of the database. Queries make use of the Relationship information in order to allow complex retreival of information.

Table Design Return to top

Each Table will contain a list of fields each of which can be defined to one of the available datatypes. This tutorial is based on Access field types, however each database system will have the same field types available (though they may use a different name in some cases), the data types that will be most relevant are as follows:

Autonumber: This is an automatic field that the database will assign. Each record in the database will be assigned a unique identifying number by the datebase, this number can be either sequential or in random order depending on your needs. This data type is most often used as the KEY for the table given it's unique nature

Text: The basic datatype. This datatype contains a string of text, the size of which can be defined to a specific size limit. If you use the type to store Postcodes/Zipcodes (for example), the maximum size of the field would not need to be more than 10 characters. The largest that the Text field can be set to it 255 characters

Number: Another basic datatype, this can be set to contain any numerical information, however the type of number will have to be defined. Most common number type you will use is Whole Numbers, this would any number that has no fractional value, such as 1 2 3 4 5 and so on. The other type is fractional numbers such as 1.3 3.753 24.3 The need to define this is down to the way that number data is stored on a computer, so long as you select the type that does what you want, then you should not have any problems.

Date/Time: The date/time field will store time based information. This can usually be specified for how much information you want to store, such time only, date only or both.

Yes/No: This field will allow very basic data storage where the data will be set to Yes or No. Different databases refer to this in different ways, SQLserver will only accept the data as 1 or 0, while Access will access 1 or 0 as well as True or False. I have found that besst practise is to use 1 or 0 to enter data (1 = True, 0 = False) as this is most compatable accross different database systems.

Memo: This is the bigger version of the Text field. You do not specify the size you want, the database will accept however much data your send to it. While the Text field might hold the headline title of a news article, the Memo field would be used to store the actual article itself.

With the above data types, you should be able to build a database for almost anything you want.

Relationships Return to top

Each table can be designed to contain what ever information that you need, however the real power of databases comes from the ability to link tables using relationships.

If we take as an example a website News system. When defining the News table most people would include the following as their first attempt at a table:
newsid (autonumber)
headline (text)
posteddate (date/time)
imagelink (text)
newsarticle (Memo)
author (text)

This includes all the information that they would want to include, however an alternative to this would as follows:

Newstable
newsid (autonumber)
headline (text)
posteddate (date/time)
imagelink (text)
newsarticle (Memo)
authorid (number)


Usertable
userid (autonumber)
username (text)
realname (text)
emailaddress (text)

Using Relationships it is possible to link these two tables, linking the fields "authorid" and "userid" together. The reason behind this is to save space and also to minimise data management. If the user changes their username, would you want to go through 100 news articles to update the author? Using this method, you only need to change the Usertable and the news page will automatically get the new username (this will be shown further in the Queries section).

Another advantage of this is that when other tables are added (forums, blogs, etc) then each table just needs to have an authorid field that you link to the userid, then you will have access to that persons account information with any items in the database that they have posted.

This is Relationships at there most simple, they can get alot more complicated than that, this might be something I cover in a future tutorial.

Queries Return to top

This is the part that lets you get your information out of the database. Access and most other database systems will allow you to create Queries with in the program (these will sometimes be called Views). These can be regarded almost like a 'virtual table'. You would start by selecting the Tables that you want to get information from, if you have set your relationships already, then these relationships should already be active when creating your query. Using the previous News example, the query would be generated by first selecting the tables that you want to take data from, Newstable and Usertable. Once the tables are selected, you can then select the data fields from each table that you want to use, this may be something like:

Newstable.newsid
Newstable.headline
Newstable.posteddate
Newstable.imagelink
Newstable.newsarticle
Usertable.username
Usertable.realname
Usertable.emailaddress

With this query defined, it would give you a list of all news articles with the name and email of the user that created them; note that you do not have to include the userid and authorid fields, of course you could include them if you want.

While the usertable might only have one row in it, the username, realname and email address will appear with every news article, even if there are hundreds of articles (and so long as the authorid in the newstable has a value that matches the userid in the usertable.

Conclusion Return to top

The above information should give you enough starting information to give you a basic understanding of how a database works and how it can be used. From here I would suggest that you use the database program you have (be that Access, MS Works or any other suitable program) and experiment with the different features, build tables, build queries and generally get a feel for how it all fits together. Once you get the grasp of this, you can the move onto using SQL statements, this is where you start getting ready to start using your database in your web scripts. Look out for future tutorials on this.

Search for more information Return to top