Need help with normalisation.

[james438]

We talked a bit about it in this post. Your new design looks promising.

Will you be adding the tables? When the new content management system is built, if you decide to go that route, updating the site will be very easy. Hopefully easy enough that the average user can use it too if need be.

Just a thought, but do you have a database set up for the redesigned site?

[ApacheTech]

I'm just working on the framework for the site atm.

I have the Bootstrap module working and I'm working on a Database module using the MySqliDb Class from https://github.com/nWidart/PHP-MySQLi-Database-Class

I may need some help converting the above class into an abstract class that I can extend from, it's difficult to get my head around.

<?php

    abstract class DatabaseToObjectModel extends MysqliDB
    {

        public function getFestivalsByYear($year)
        {
            // Do stuff
        }
        
        public function getMusicClubsByCounty($county)
        {
            // Do stuff
        }

        public function renderLinksByCategory($category)
        {
            // Do stuff
        }

    } // END class

    class LiveDatabase extends DatabaseToObjectModel
    {

        public function __construct()
        {
            $this->_mysqli = new mysqli('localhost', 'username', 'password', 'live')
                or die('There was a problem connecting to the database');
            self::$_instance = $this;
        }

    } // END class 

    class ArchiveDatabase extends DatabaseToObjectModel
    {

        public function __construct() {
            $this->_mysqli = new mysqli('localhost', 'username', 'password', 'archive')
                or die('There was a problem connecting to the database');
            self::$_instance = $this;
        }

    } // END class 

So that I can then do stuff like:

$db = array (

    'Live'         => new LiveDatabase();
    'Archive'     => new ArchiveDatabase();
    
);

$MusicClubs = $db['Live']->renderLinksByCategory($category); 

I'm unsure if I've done that right though. I'm trying to think of it in VB.NET terms where one class Implements another, but I'm not sure on the rules for it in PHP.

I've decided to focus mainly on the Links at the moment. It will be a lot simpler to do it a bit at a time, and a lot of the information it is static. I can work on the more complicated bits later on and extend the database from there.

[james438]

Focusing on the Links tables may be a good place to start. Table normalization was a very difficult concept for me as well , but it makes a lot more sense to me now.

It looks like you may have your database set up. Were you able to successfully connect to it?

You use several things that I am less familiar with like MySqliDb and Bootstrap and PHPCSS, which I am not familiar with although I have heard of MySqliDb before, but that's it. I also have little to no experience in working with classes or functions. I could, however, write up a working model of the normalized tables you want created and the forms and the archive pages you are talking about without too much trouble. I just mention that so that you have some idea what skill set is.

Out of necessity I use several normalized tables on my site. It became necessary for me when I wanted to give my articles tags like css, php, or politics. I then had to learn the GROUP BY function when I wanted to update my website's search feature to pull all results that are tagged with css or php and not have duplicate results. I don't see a need to use GROUP BY for what you are trying to do here.

[ApacheTech]

Bootstrapping is just the basic methods for rendering the page. It means that currently, my index.php style is just:

<?php define('_EXEC', 1);

require 'config.php';

$Bootstrap->render();

My config.php is like this:

<?php defined('_EXEC') or die("No direct access!");

/**
* @author:         Apache
* @company:     ApacheTech Consultancy
* @framework:    ATC Asatru Framework 0.6b (BETA)
* @component:    Odin
* @site:        Folk Orbit Magazine
* @file:        config.php
*/

/**
* Debugging.
*/
define('_DEBUG', 1);
(defined('_DEBUG'))    ? error_reporting(E_ALL) : NULL;


/**
* Path Definitions
*/
define('DS', DIRECTORY_SEPARATOR);

define('PATH_BASE',     realpath(dirname(__FILE__)) . DS);
define('PATH_ADMIN',     PATH_BASE . 'administrator'. DS);
define('PATH_MODULES',     PATH_BASE . 'modules'. DS);
define('PATH_CSS',         PATH_BASE . 'css'. DS);
define('PATH_JS',         PATH_BASE . 'js'. DS);
define('PATH_IMAGES',     PATH_BASE . 'img'. DS);    

/**
* Load site modules.
*/
if (!defined('MODULES_LOADED'))
{
    $modules = glob(PATH_MODULES . 'mod_*.php');
    foreach($modules as $module)
    {
      include_once $module;
    }
    define('MODULES_LOADED',1);
    unset($modules);
}

And the mod_bootstrap.php is:

<?php defined('_EXEC') or die("No direct access!");

/**
* @author:         Apache
* @company:     ApacheTech Consultancy
* @framework:    ATC Asatru Framework 0.6b (BETA)
* @component:    Odin
* @module:        Bootstrap
* @site:        Folk Orbit Magazine
* @file:        mod_bootstrap.php
*/

class Bootstrap {

    protected $_page;
    
    function __construct() {
        (isset($_SERVER['PATH_INFO']))
            ? define('__PAGE__', ltrim($_SERVER['PATH_INFO'], '/'))
            : define('__PAGE__', 'home');
    }

    public function render() {
        if (file_exists(PATH_BASE . 'html/' . __PAGE__ .'.html.php')) {
            include_once PATH_BASE . 'html/templates/frontend_header.html.php';
            include_once PATH_BASE . 'html/' . __PAGE__ .'.html.php';
            include_once PATH_BASE . 'html/templates/frontend_footer.html.php';
        } else {
            include_once PATH_BASE . '404.html';
        }
    }
}

$Bootstrap = new Bootstrap();

And the site loads straight from that. It's only a very basic framework at the moment, but I'm hoping to adapt and extend it as time goes on to create my own bespoke PHP Modular Framework. I don't really like the MVC system, it seems far too complicated for it's own good, even when you do understand it. It's pointless for small scale projects like this.

PHPCSS is a nice method I've found to be able to use PHP with your CSS files. In 72 more posts, I'm planning a full write up on it in the Senior Coders forum.

[ApacheTech]

This is my best attempt at normailising the data. I've added an attachment as well, for if/when the link expires on the main image.

I now have the links page working fully with randomly generated data, and I have the static tables built as well. So, all counties in all four countries, weekdays in both full and short and all the categories are in place.

It's getting there, but it's taking a long time with the snagging problems.

[james438]

I might not be fully understanding what the links table is for. When I was looking at your initial table for links it looked like you only needed four simple tables.

Here is an example of a normalized table of the books you own. This covers only the author's names. In your library you have only 4 book entered into your database so far. As you enter names into the database some of the author's names will repeat.

book_title
ID
title

author_name
ID
name

author_name_relationships
ID
author_name_ID
book_title_ID

The records for the tables will look something like this:

book_title
ID    title

1     The Dream Master
2     The Way Of Shadows
3     Necronomicon
4     Madwand


author_name
ID    author_name

1     Roger Zelazny
2     Brent Weeks
3     H. P. Lovecraft


author_name_relationships
ID    author_name_ID   book_title_ID

1     1                       1
2     2                       2
3     3                       3
4     1                       4

The way we tie the author's name to the title of the books is with a third table that lists the relationship between book title and author.

An example query that lists the books by Roger Zelazny will look like this:

SELECT book_title.title, author_name.author_name
FROM
book_title, author_name_relationships, author_name 
WHERE
author_name_relationships.author_name_ID=author_name.ID
AND 
author_name_relationships.book_title_ID=book_title.ID
AND
author_name_relationships.author_name_ID=1

Your submit script will place the title of the book in the book_title table then scan the author_name table to see if the name for your book is in the table. If the author is not in that table then add it to the list of authors in the author_name table. Finally the submit script will scan the author_name table again to locate the ID used for that author. If it is Brent Weeks again then the ID value is 2. That value is stored in the author_name_relationships as:

ID    author_name_ID   book_title_ID

5     2                       5

There are several other ways to write the above MySQL query by using different types of JOIN or using the GROUP BY function, but I prefer to stick with just the basic query format as it makes more sense to me.

Breaking the query down we have the columns we want to display:

SELECT book_title.title, author_name.author_name

From the following tables:

FROM
book_title, author_name_relationships, author_name

This is the complicated part. The tables are tied together with the following relationships:

WHERE
author_name_relationships.author_name_ID=author_name.ID
AND
author_name_relationships.book_title_ID=book_title.ID

Think of it as pulling the results from the author_name_relationships table and replacing the numbers from the columns author_name_ID and book_title_ID and replacing them with their respective text values.

The following says that we only want to pull results where the author is 1, which when the MySQL engine looks at the author_name table it sees that 1=Roger Zelazny.

AND
author_name_relationships.author_name_ID=1

Why use normalized tables?

First, it helps to avoid redundant information in your database, which helps to reduce the size of your database, which helps it to run smoother and faster.

Second, it helps to avoid typos. Lets say I have 50 different titles by Roger Zelazny, but I spelled his name wrong. Now all I need to do is change value for his name in the author_name table.

[ApacheTech]

Each "link" has a title, description, phone number and email. Four basic properties. But it also belongs to a category and can have adverts and websites. hence the foreign key relationships.

For the adverts and websites, I've put them all in a triple-composite key table, which is a lot easier than I thought to work with.

[ApacheTech]

I've built the structure of the database in Access as well, so that I've got a working query builder. One thing Access is very good at is making queries very easy!

[ApacheTech]

And I have my first fully formed SQL SELECT statement!

SELECT adverts.id AS adverts_id, adverts.table AS adverts_table, adverts.record_id AS adverts_record_id, adverts.filename, websites.id AS websites_id, websites.table AS websites_table, websites.record_id AS websites_record_id, websites.url, categories.id AS categories_id, categories.name AS categories_name, categories.alias, links.id AS links_id, links.name AS links_name, links.category_id, links.description, links.phone_number, links.email
FROM ((categories INNER JOIN links ON categories.[id] = links.[category_id]) INNER JOIN websites ON links.[id] = websites.[record_id]) INNER JOIN adverts ON links.[id] = adverts.[record_id]
WHERE (((adverts.table)="links") AND ((websites.table)="links"));

[ApacheTech]

Ok, this is driving my crazy!!!

Trying to use the SQL SELECT statement:

SELECT adverts.id AS adverts_id, adverts.table AS adverts_table, adverts.record_id AS adverts_record_id, adverts.filename, websites.id AS websites_id, websites.table AS websites_table, websites.record_id AS websites_record_id, websites.url, categories.id AS categories_id, categories.name AS categories_name, categories.alias, links.id AS links_id, links.name AS links_name, links.category_id, links.description, links.phone_number, links.email
FROM categories
INNER JOIN links ON categories.id = links.category_id
INNER JOIN websites ON links.id = websites.record_id
INNER JOIN adverts ON links.id = adverts.record_id
WHERE adverts.table = "links"
AND websites.table = "links";

and I keep getting the error:

Fatal error: Problem preparing query ( SELECT adverts.id AS adverts_id, adverts.table AS adverts_table, adverts.record_id AS adverts_record_id, adverts.filename, websites.id AS websites_id, websites.table AS websites_table, websites.record_id AS websites_record_id, websites.url, categories.id AS categories_id, categories.name AS categories_name, categories.alias, links.id AS links_id, links.name AS links_name, links.category_id, links.description, links.phone_number, links.email FROM categories INNER JOIN links ON categories.id = links.category_id INNER JOIN websites ON links.id = websites.record_id INNER JOIN adverts ON links.id = adverts.record_id WHERE adverts.table = "links" AND websites.table = "links";) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"links" AND websites.table = "links"' at line 6 in [CLASSIFIED] on line 403