Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Need help with normalisation.

  1. #11
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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?
    To choose the lesser of two evils is still to choose evil. My personal site

  2. #12
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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 Code:
    <?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
    PHP Code:
        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 
    PHP Code:
        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:

    PHP Code:
    $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.

  3. #13
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  4. #14
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

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

    PHP Code:
    <?php define('_EXEC'1);

    require 
    'config.php';

    $Bootstrap->render();
    My config.php is like this:
    PHP Code:
    <?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 Code:
    <?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.

  5. #15
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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.


  6. #16
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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:

    Code:
    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:

    Code:
    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:

    Code:
    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.
    Last edited by james438; 07-05-2012 at 09:15 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  7. #17
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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.

  8. #18
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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!

  9. #19
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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"));

  10. #20
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •