The "Freeola Customer Forum" forum, which includes Retro Game Reviews, has been archived and is now read-only. You cannot post here or create a new thread or review on this forum.
Calling all Database novices out there,
Following cjh’s post on an introduction to PHP, and after noticing a few posted questions on how to ‘use a mysql database’, I thought I’d put a couple of pointers down to get database beginners started.
DOMAIN/WEBSPACE
If you don’t have your domain or webspace yet then Tappet’s advice here will point you in the right direction.
MySQL
Once you have your webspace in place then you need to get yourself a MySQL database. There are many MySQL providers out there but I have used Freeola’s MySQL package for years and found it to be convenient and reliable.
DATABASES IN GENERAL
The main advice I would give when using databases of any type is to give serious consideration to the information you need to store before you start lashing tables together. There is nothing worse than spending weeks designing a web site only to find you have forgotten to add a column in your table. This will lead to many hours re-writing queries and redesigning website components. Further development and growth of your database is a natural progression but even after many years of designing huge databases I still start off with a basic paper based diagram outlining all required information and their relationships.
TABLES V’S VIEWS
Very basically - A TABLE is the basic data storeage component of a database and provides a framework for the data structure and type. A Database consists of one or more tables. A Table consists of data fields which can be set to a data type. A DATA TYPE restricts the data in the field to be of a certain format. You may for example wish to set up a ‘DateOfBirth’ field in an employees record. The chances are that you would want the date to be in a specific format and also recognised as a date for future calculations. The Data Type for such a field would best be set to (surprise, surprise) ‘DATE’. This would allow only valid ‘date’ values to be entered, such as ‘01/01/1980’ but not values such as ‘1st Jan 80’ or ‘January 80’. Usually (certainly in MySQL PHP Admin) data types will be chosen from a predefined list.
A VIEW will usually (but not always) be a 'joined up' representation of the data in two or more related tables. This means most of the data you see on web sites or database based programs is usually a ‘view’ of the underlying table data. Records from a ‘employees’ table will be related to records in a ‘employees qualifications’ table via the employee’s unique id (but more about that below).
RELATIONSHIPS
Relationships between tables is a key consideration when designing a database. The main objective when designing a database is to reduce duplicity of data maintenance. We never want to get into a position where we need to change the same data in two different tables.
For example: In a database of employees at a business – if an employee changes name (get’s married, changes gender, is running from the mob, etc..) we need to changes the ‘name’ data for that employee. An EFFICIENT database would store that name in one table only – which, when amended would reflect in all other views of that data. An INEFFICIENT database would have the ‘name’ data stored in the employee records and perhaps the ‘qualifications’ table or even a ‘training’ table too.
In short, the golden rule of database design is: if you need to change the same data in more than one table then you need to review your design.
RELATIONSHIPS resolve the problem of multiple data maintenance by stating which records are ‘related’ to other record in other tables. This is done by the use of ‘keys’. A PRIMARY KEY in one table can be stored in the records of another table (where it becomes a FOREIGN KEY) to make sure that ONLY the records where the keys are the same are related. This means that the data in both tables can be shown in a ‘view’ and the data need only be stored and changed in one place.
For example: A Database to show the employee example above would be:
TABLE: Employee
FIELDS:
EmployeeID (PK) - Integer
Name – VARCHAR(30)
Address VARCHAR(100)
DateOfBirth – DATE
Where EmployeeID in the Employee table is the PRIMARY KEY and must be a unique value (usually supplied by the database management system (DBMS) if you specified an auto-increment parameter).
TABLE: EmployeeQualifications
FIELDS:
EmployeeQualificationRecID (PK) - Integer
EmployeeID (FK) – Integer
QualificationDesc
Where EmployeeID in the EmployeeQualifications table is called a FOREIGN KEY
The SQLcode to create the above tables is here:
The relationship between the above tables is the EmployeeID. Notice it is present in both tables and, quite importantly, it is of the same DATA TYPE - Integer.
We can now create a VIEW called 'EmployeeFullView' showing the full details of the employee and their qualifications as:
Which will create a view showing each employee and their qualifications.
We don't need to add any records to this VIEW because they are provided by the Employee and EmployeeQualifications TABLES.
The sharp eyed amongst you will notice there is an improvement which could be made to the EmployeeQualifications table but I’ll leave you to work that one out.
A NOTE ON DataBase Management System’s
Whether you use MS SQL Server, Oracle, MySQL, MS Access, Paradox, DBase or any other DBMS, the principles above will still hold, however, each DBMS has a slightly different flavour of how to implement it.
PART TWO of this post will explain how to extract information from your tables or views, how to display it in a web page and how to extract single data values to use as variables or store as session variables.
I hope it’s been useful. Please feel free to ask questions or point out the mistakes I’ve probably made.
Regards
JTD
Following cjh’s post on an introduction to PHP, and after noticing a few posted questions on how to ‘use a mysql database’, I thought I’d put a couple of pointers down to get database beginners started.
DOMAIN/WEBSPACE
If you don’t have your domain or webspace yet then Tappet’s advice here will point you in the right direction.
MySQL
Once you have your webspace in place then you need to get yourself a MySQL database. There are many MySQL providers out there but I have used Freeola’s MySQL package for years and found it to be convenient and reliable.
DATABASES IN GENERAL
The main advice I would give when using databases of any type is to give serious consideration to the information you need to store before you start lashing tables together. There is nothing worse than spending weeks designing a web site only to find you have forgotten to add a column in your table. This will lead to many hours re-writing queries and redesigning website components. Further development and growth of your database is a natural progression but even after many years of designing huge databases I still start off with a basic paper based diagram outlining all required information and their relationships.
TABLES V’S VIEWS
Very basically - A TABLE is the basic data storeage component of a database and provides a framework for the data structure and type. A Database consists of one or more tables. A Table consists of data fields which can be set to a data type. A DATA TYPE restricts the data in the field to be of a certain format. You may for example wish to set up a ‘DateOfBirth’ field in an employees record. The chances are that you would want the date to be in a specific format and also recognised as a date for future calculations. The Data Type for such a field would best be set to (surprise, surprise) ‘DATE’. This would allow only valid ‘date’ values to be entered, such as ‘01/01/1980’ but not values such as ‘1st Jan 80’ or ‘January 80’. Usually (certainly in MySQL PHP Admin) data types will be chosen from a predefined list.
A VIEW will usually (but not always) be a 'joined up' representation of the data in two or more related tables. This means most of the data you see on web sites or database based programs is usually a ‘view’ of the underlying table data. Records from a ‘employees’ table will be related to records in a ‘employees qualifications’ table via the employee’s unique id (but more about that below).
RELATIONSHIPS
Relationships between tables is a key consideration when designing a database. The main objective when designing a database is to reduce duplicity of data maintenance. We never want to get into a position where we need to change the same data in two different tables.
For example: In a database of employees at a business – if an employee changes name (get’s married, changes gender, is running from the mob, etc..) we need to changes the ‘name’ data for that employee. An EFFICIENT database would store that name in one table only – which, when amended would reflect in all other views of that data. An INEFFICIENT database would have the ‘name’ data stored in the employee records and perhaps the ‘qualifications’ table or even a ‘training’ table too.
In short, the golden rule of database design is: if you need to change the same data in more than one table then you need to review your design.
RELATIONSHIPS resolve the problem of multiple data maintenance by stating which records are ‘related’ to other record in other tables. This is done by the use of ‘keys’. A PRIMARY KEY in one table can be stored in the records of another table (where it becomes a FOREIGN KEY) to make sure that ONLY the records where the keys are the same are related. This means that the data in both tables can be shown in a ‘view’ and the data need only be stored and changed in one place.
For example: A Database to show the employee example above would be:
TABLE: Employee
FIELDS:
EmployeeID (PK) - Integer
Name – VARCHAR(30)
Address VARCHAR(100)
DateOfBirth – DATE
Where EmployeeID in the Employee table is the PRIMARY KEY and must be a unique value (usually supplied by the database management system (DBMS) if you specified an auto-increment parameter).
TABLE: EmployeeQualifications
FIELDS:
EmployeeQualificationRecID (PK) - Integer
EmployeeID (FK) – Integer
QualificationDesc
Where EmployeeID in the EmployeeQualifications table is called a FOREIGN KEY
The SQLcode to create the above tables is here:
The relationship between the above tables is the EmployeeID. Notice it is present in both tables and, quite importantly, it is of the same DATA TYPE - Integer.
We can now create a VIEW called 'EmployeeFullView' showing the full details of the employee and their qualifications as:
CREATE VIEW `EmployeeQualificationView` AS select `Employee`.`EmployeeID` AS `EmployeeID`, `Employee`.`Address` AS `Address`, `Employee`.`DateOfBirth` AS `DateOfBirth`, `EmployeeQualifications`.`QualificationDesc` AS `QualificationDesc` from (`Employee` join `EmployeeQualifications` on ((`Employee`.`EmployeeID` = `EmployeeQualifications`.`EmployeeID`)));
Which will create a view showing each employee and their qualifications.
We don't need to add any records to this VIEW because they are provided by the Employee and EmployeeQualifications TABLES.
The sharp eyed amongst you will notice there is an improvement which could be made to the EmployeeQualifications table but I’ll leave you to work that one out.
A NOTE ON DataBase Management System’s
Whether you use MS SQL Server, Oracle, MySQL, MS Access, Paradox, DBase or any other DBMS, the principles above will still hold, however, each DBMS has a slightly different flavour of how to implement it.
PART TWO of this post will explain how to extract information from your tables or views, how to display it in a web page and how to extract single data values to use as variables or store as session variables.
I hope it’s been useful. Please feel free to ask questions or point out the mistakes I’ve probably made.
Regards
JTD
Calling all Database novices out there,
Following cjh’s post on an introduction to PHP, and after noticing a few posted questions on how to ‘use a mysql database’, I thought I’d put a couple of pointers down to get database beginners started.
DOMAIN/WEBSPACE
If you don’t have your domain or webspace yet then Tappet’s advice here will point you in the right direction.
MySQL
Once you have your webspace in place then you need to get yourself a MySQL database. There are many MySQL providers out there but I have used Freeola’s MySQL package for years and found it to be convenient and reliable.
DATABASES IN GENERAL
The main advice I would give when using databases of any type is to give serious consideration to the information you need to store before you start lashing tables together. There is nothing worse than spending weeks designing a web site only to find you have forgotten to add a column in your table. This will lead to many hours re-writing queries and redesigning website components. Further development and growth of your database is a natural progression but even after many years of designing huge databases I still start off with a basic paper based diagram outlining all required information and their relationships.
TABLES V’S VIEWS
Very basically - A TABLE is the basic data storeage component of a database and provides a framework for the data structure and type. A Database consists of one or more tables. A Table consists of data fields which can be set to a data type. A DATA TYPE restricts the data in the field to be of a certain format. You may for example wish to set up a ‘DateOfBirth’ field in an employees record. The chances are that you would want the date to be in a specific format and also recognised as a date for future calculations. The Data Type for such a field would best be set to (surprise, surprise) ‘DATE’. This would allow only valid ‘date’ values to be entered, such as ‘01/01/1980’ but not values such as ‘1st Jan 80’ or ‘January 80’. Usually (certainly in MySQL PHP Admin) data types will be chosen from a predefined list.
A VIEW will usually (but not always) be a 'joined up' representation of the data in two or more related tables. This means most of the data you see on web sites or database based programs is usually a ‘view’ of the underlying table data. Records from a ‘employees’ table will be related to records in a ‘employees qualifications’ table via the employee’s unique id (but more about that below).
RELATIONSHIPS
Relationships between tables is a key consideration when designing a database. The main objective when designing a database is to reduce duplicity of data maintenance. We never want to get into a position where we need to change the same data in two different tables.
For example: In a database of employees at a business – if an employee changes name (get’s married, changes gender, is running from the mob, etc..) we need to changes the ‘name’ data for that employee. An EFFICIENT database would store that name in one table only – which, when amended would reflect in all other views of that data. An INEFFICIENT database would have the ‘name’ data stored in the employee records and perhaps the ‘qualifications’ table or even a ‘training’ table too.
In short, the golden rule of database design is: if you need to change the same data in more than one table then you need to review your design.
RELATIONSHIPS resolve the problem of multiple data maintenance by stating which records are ‘related’ to other record in other tables. This is done by the use of ‘keys’. A PRIMARY KEY in one table can be stored in the records of another table (where it becomes a FOREIGN KEY) to make sure that ONLY the records where the keys are the same are related. This means that the data in both tables can be shown in a ‘view’ and the data need only be stored and changed in one place.
For example: A Database to show the employee example above would be:
TABLE: Employee
FIELDS:
EmployeeID (PK) - Integer
Name – VARCHAR(30)
Address VARCHAR(100)
DateOfBirth – DATE
Where EmployeeID in the Employee table is the PRIMARY KEY and must be a unique value (usually supplied by the database management system (DBMS) if you specified an auto-increment parameter).
TABLE: EmployeeQualifications
FIELDS:
EmployeeQualificationRecID (PK) - Integer
EmployeeID (FK) – Integer
QualificationDesc
Where EmployeeID in the EmployeeQualifications table is called a FOREIGN KEY
The SQLcode to create the above tables is here:
The relationship between the above tables is the EmployeeID. Notice it is present in both tables and, quite importantly, it is of the same DATA TYPE - Integer.
We can now create a VIEW called 'EmployeeFullView' showing the full details of the employee and their qualifications as:
Which will create a view showing each employee and their qualifications.
We don't need to add any records to this VIEW because they are provided by the Employee and EmployeeQualifications TABLES.
The sharp eyed amongst you will notice there is an improvement which could be made to the EmployeeQualifications table but I’ll leave you to work that one out.
A NOTE ON DataBase Management System’s
Whether you use MS SQL Server, Oracle, MySQL, MS Access, Paradox, DBase or any other DBMS, the principles above will still hold, however, each DBMS has a slightly different flavour of how to implement it.
PART TWO of this post will explain how to extract information from your tables or views, how to display it in a web page and how to extract single data values to use as variables or store as session variables.
I hope it’s been useful. Please feel free to ask questions or point out the mistakes I’ve probably made.
Regards
JTD
Following cjh’s post on an introduction to PHP, and after noticing a few posted questions on how to ‘use a mysql database’, I thought I’d put a couple of pointers down to get database beginners started.
DOMAIN/WEBSPACE
If you don’t have your domain or webspace yet then Tappet’s advice here will point you in the right direction.
MySQL
Once you have your webspace in place then you need to get yourself a MySQL database. There are many MySQL providers out there but I have used Freeola’s MySQL package for years and found it to be convenient and reliable.
DATABASES IN GENERAL
The main advice I would give when using databases of any type is to give serious consideration to the information you need to store before you start lashing tables together. There is nothing worse than spending weeks designing a web site only to find you have forgotten to add a column in your table. This will lead to many hours re-writing queries and redesigning website components. Further development and growth of your database is a natural progression but even after many years of designing huge databases I still start off with a basic paper based diagram outlining all required information and their relationships.
TABLES V’S VIEWS
Very basically - A TABLE is the basic data storeage component of a database and provides a framework for the data structure and type. A Database consists of one or more tables. A Table consists of data fields which can be set to a data type. A DATA TYPE restricts the data in the field to be of a certain format. You may for example wish to set up a ‘DateOfBirth’ field in an employees record. The chances are that you would want the date to be in a specific format and also recognised as a date for future calculations. The Data Type for such a field would best be set to (surprise, surprise) ‘DATE’. This would allow only valid ‘date’ values to be entered, such as ‘01/01/1980’ but not values such as ‘1st Jan 80’ or ‘January 80’. Usually (certainly in MySQL PHP Admin) data types will be chosen from a predefined list.
A VIEW will usually (but not always) be a 'joined up' representation of the data in two or more related tables. This means most of the data you see on web sites or database based programs is usually a ‘view’ of the underlying table data. Records from a ‘employees’ table will be related to records in a ‘employees qualifications’ table via the employee’s unique id (but more about that below).
RELATIONSHIPS
Relationships between tables is a key consideration when designing a database. The main objective when designing a database is to reduce duplicity of data maintenance. We never want to get into a position where we need to change the same data in two different tables.
For example: In a database of employees at a business – if an employee changes name (get’s married, changes gender, is running from the mob, etc..) we need to changes the ‘name’ data for that employee. An EFFICIENT database would store that name in one table only – which, when amended would reflect in all other views of that data. An INEFFICIENT database would have the ‘name’ data stored in the employee records and perhaps the ‘qualifications’ table or even a ‘training’ table too.
In short, the golden rule of database design is: if you need to change the same data in more than one table then you need to review your design.
RELATIONSHIPS resolve the problem of multiple data maintenance by stating which records are ‘related’ to other record in other tables. This is done by the use of ‘keys’. A PRIMARY KEY in one table can be stored in the records of another table (where it becomes a FOREIGN KEY) to make sure that ONLY the records where the keys are the same are related. This means that the data in both tables can be shown in a ‘view’ and the data need only be stored and changed in one place.
For example: A Database to show the employee example above would be:
TABLE: Employee
FIELDS:
EmployeeID (PK) - Integer
Name – VARCHAR(30)
Address VARCHAR(100)
DateOfBirth – DATE
Where EmployeeID in the Employee table is the PRIMARY KEY and must be a unique value (usually supplied by the database management system (DBMS) if you specified an auto-increment parameter).
TABLE: EmployeeQualifications
FIELDS:
EmployeeQualificationRecID (PK) - Integer
EmployeeID (FK) – Integer
QualificationDesc
Where EmployeeID in the EmployeeQualifications table is called a FOREIGN KEY
The SQLcode to create the above tables is here:
The relationship between the above tables is the EmployeeID. Notice it is present in both tables and, quite importantly, it is of the same DATA TYPE - Integer.
We can now create a VIEW called 'EmployeeFullView' showing the full details of the employee and their qualifications as:
CREATE VIEW `EmployeeQualificationView` AS select `Employee`.`EmployeeID` AS `EmployeeID`, `Employee`.`Address` AS `Address`, `Employee`.`DateOfBirth` AS `DateOfBirth`, `EmployeeQualifications`.`QualificationDesc` AS `QualificationDesc` from (`Employee` join `EmployeeQualifications` on ((`Employee`.`EmployeeID` = `EmployeeQualifications`.`EmployeeID`)));
Which will create a view showing each employee and their qualifications.
We don't need to add any records to this VIEW because they are provided by the Employee and EmployeeQualifications TABLES.
The sharp eyed amongst you will notice there is an improvement which could be made to the EmployeeQualifications table but I’ll leave you to work that one out.
A NOTE ON DataBase Management System’s
Whether you use MS SQL Server, Oracle, MySQL, MS Access, Paradox, DBase or any other DBMS, the principles above will still hold, however, each DBMS has a slightly different flavour of how to implement it.
PART TWO of this post will explain how to extract information from your tables or views, how to display it in a web page and how to extract single data values to use as variables or store as session variables.
I hope it’s been useful. Please feel free to ask questions or point out the mistakes I’ve probably made.
Regards
JTD
A nice article JimTheDog, looking forward to the next in the series.
JimTheDog:
"The sharp eyed amongst you will notice there is an
improvement which could be made to the EmployeeQualifications
table but I’ll leave you to work that one out".
Could it be that a column for the actual qualification grade is what you're referring to?
"The sharp eyed amongst you will notice there is an
improvement which could be made to the EmployeeQualifications
table but I’ll leave you to work that one out".
Could it be that a column for the actual qualification grade is what you're referring to?
Not quite. There is already an opportunity to prevent multiple data maintenance in the EmployeeQualifications table but let's say you did want to add a grade column. What type of column would it be? type INT for '1,2,3' grading or perhaps type Varchar(15) for 'Distinction, Pass, Fail'? If you chose the second of these options wold you want to type each grade into the field every time?
oooo I feel all school teachery :o(
oooo I feel all school teachery :o(
A fresh table to hold the many qualification types (GCSE, HND, Degree, etc), and including a reference (foreign key?) to whichever grade type is being added for an individual?
As for the actual grade storage, I'm not too sure on this one. I think I would either go for a VARCHAR, or create a fresh grades table that would store the type of grade on offer, or perhaps multiple tables for each type of qualification.
It has been so long since I've constructed a proper database, it's as if I am re-learning.
As for the actual grade storage, I'm not too sure on this one. I think I would either go for a VARCHAR, or create a fresh grades table that would store the type of grade on offer, or perhaps multiple tables for each type of qualification.
It has been so long since I've constructed a proper database, it's as if I am re-learning.
cjh wrote
> A fresh table to hold the many qualification types (GCSE,
> HND, Degree, etc), and including a reference (foreign key?) to
> whichever grade type is being added for an individual?
That's right - It would be better to create a separate table called qulificationsTypes - each type having it's own TypeID field as its PRIMARY KEY. The EmployeeQualifications table would then have an INTEGER field which contains the qualification type reference as a FOREIGN KEY.
cjh wrote
> As for the actual grade storage, I'm not too sure on this one. I
> think I would either go for a VARCHAR, or create a fresh
> grades table that would store the type of grade on offer,
> or perhaps multiple tables for each type of qualification.
>
That's exactly the option I would choose. If you create a second table called Grades then your ability to add new grades is a simple one.
As in the example with the qualificationTypes, a Foreign Key in the EmployeeQualifications table would hold the GradeID which relates to the GradeID in the grades table.
You'll notice in my original post I spelled the qualification incorrectly several times. Creating a separate table and adding the foreign key into the EmployeeQualifications table completely eliminates this type of error - (or rather it makes it a single point of correction if it is done).
cjh wrote
> It has been so long since I've constructed a proper database,
> it's as if I am re-learning.
It's good to go through it like this. I hope it's giving other people an insight into database building and the decisions to be made.
Just for the record, this process of breaking tables down into more and more tables to reduce multiple data maintenance is called normalisation. The official process of normalisation can be a lengthy one but an experienced database designer can usually weed out these problems at the pencil and paper stage of design.
JTD
> A fresh table to hold the many qualification types (GCSE,
> HND, Degree, etc), and including a reference (foreign key?) to
> whichever grade type is being added for an individual?
That's right - It would be better to create a separate table called qulificationsTypes - each type having it's own TypeID field as its PRIMARY KEY. The EmployeeQualifications table would then have an INTEGER field which contains the qualification type reference as a FOREIGN KEY.
cjh wrote
> As for the actual grade storage, I'm not too sure on this one. I
> think I would either go for a VARCHAR, or create a fresh
> grades table that would store the type of grade on offer,
> or perhaps multiple tables for each type of qualification.
>
That's exactly the option I would choose. If you create a second table called Grades then your ability to add new grades is a simple one.
As in the example with the qualificationTypes, a Foreign Key in the EmployeeQualifications table would hold the GradeID which relates to the GradeID in the grades table.
You'll notice in my original post I spelled the qualification incorrectly several times. Creating a separate table and adding the foreign key into the EmployeeQualifications table completely eliminates this type of error - (or rather it makes it a single point of correction if it is done).
cjh wrote
> It has been so long since I've constructed a proper database,
> it's as if I am re-learning.
It's good to go through it like this. I hope it's giving other people an insight into database building and the decisions to be made.
Just for the record, this process of breaking tables down into more and more tables to reduce multiple data maintenance is called normalisation. The official process of normalisation can be a lengthy one but an experienced database designer can usually weed out these problems at the pencil and paper stage of design.
JTD
Well done to me then :)
I remember the concept of normalisation from my college and SQL book days, and it can be quite a process, though very rewarding later on. It's all coming back to me now.
I remember the concept of normalisation from my college and SQL book days, and it can be quite a process, though very rewarding later on. It's all coming back to me now.