Need help with normalisation.

[ApacheTech]

Hey, I have a database I need to try and create. I have a few different categories and lots of fields in each. If I write down what needs to be in each table, can someone help me fit it into at least 3rd Normal Form? I've tried once but I ended up with about 20 tables in total. I need to try and keep it as simple as possible.


Concert Venues:
Venue Name
Address, including post code
Phone Number
Website URL
Country
County
Advert Images (Can be multiple, or none)

Dance Clubs:
Club Name
Country
County
Address, including post code
Day
Time
Contact Name
Phone Number
Website URL
Advert Images (Can be multiple, or none)

Links:
Category
Title
Description
Telephone Number
Email
Website (can be multiple)
Advert Images (Can be multiple, or none)

Music Clubs:
Club Name
Country
County
Address, including post code
Day
Time
Frequency
Contact Name
Phone Number
Website URL
Last Checked Date
Advert Images (Can be multiple, or none)

A large number of the fields are optional and all the information will be displayed in tables on a website. I need to be able to order by County, Country, Day or Category where available.

I also need to be able to archive the records if they are edited or deleted. This is a different matter, but I'm not sure if it's best to clone the structure of the database and add entries in, or add archive table to this database; so it is something that will need to be addressed at an early stage.

My client will also need a back-end to the website, in order to update and maintain the database easily. I'm thinking of writing a bespoke DBM but if there are any DBM's out there that can be used to update databases as easily as you can with Access, I'd be wiling to try out any suggestions.

I have resigned myself to writing the site in PHP and MySQL (or SQLite) as I still haven't been able to find a stable host for ASP.NET 4.0 websites.

Any help anyone can give would be greatly appreciated and repaid with thanks in the site's humans.txt

[ApacheTech]

My apologies, there is another table to add in as well:



Fesitvals:
Year
Month
Start Date
End Date
Festival Name
Location
Description
Contact Name
Telephone Number
Website
Advert images (can be multiple or none)


I still have no idea how to use GROUP BY in SQL. I'm completely lost after using Access for so long.

I anyone knows a stable, free, ASP.NET hosting site, then all my prayers will be answered and I can go back to using my native language. By stable, I mean will host ASP.NET 4.0, Access Databases and will not plaster the site with obnoxious adverts (Somee). Somee is exactly what I'm looking for, but it's atrocious advertising scheme renders the whole site useless. I don't mind advertising the host, nor crediting them or backlinking to them, but I want to be able to host a publicly accessable site at the same time. Somee is horrendous in that regard and makes it impossible to host anything with them.

[james438]

If it were me I would use 12 tables:


Gathering
ID
Name
Year
Month
Address
Phone
Website
Day
Time
Frequency
Contact
Description
Start Date
End Date
Last Checked Date

Links
Title
Description
Telephone Number
Email
Website (can be multiple)


Link Advert Image Urls
ID
Name
Link ID

Gathering Advert Image Urls
ID
Name
Gathering ID


Link Category Names
ID
Name

Link Category Relationships
ID
Category Names ID
Gathering ID

Type
ID
Name

Type Relationships
ID
Category Names ID
Gathering ID

Country Names
ID
Name

Country Relationships
ID
Category Names ID
Gathering ID

County Names
ID
Name

County Relationships
ID
Category Names ID
Gathering ID

The first two tables are your main categories. I merged Concert Venue, Dance Club, Music Club, and Festivals as they have many of the same columns. I gave Links its own table.

For images I kept it relatively simple by giving it an ID, a URL, and a Gathering ID that links back to the Gathering Table ID. I did the same for the Links images, but I could easily have merged these two tables instead of using two separate tables for the images. It was just easier for me to understand.

For the other columns that look to need normalizing (Country, County, and Type) I created 2 tables for each. First there is the name like France or Germany. The second table is the relationship between the name ID and the Gathering ID.


SELECT Country Names.Names
FROM Country Relationships, Country Names
WHERE Country Names.ID=Country Relationships.Gathering ID
AND Country Relationships.Gathering ID=4

This example query lists the text names of all of the countries where Gathering.ID is the same as Country Relationships.Gathering ID.

[james438]

What do you mean by archive the data if it is edited or deleted? Could you mark the entry as hidden instead so that it is only viewable in the archives? Or were you thinking more of a wikipedia editing structure (djr33 is more familiar with that than I am).

On a separate note when submitting the data into the database where editing an entry I just delete those related relationship table entries and reinsert them.

I code less frequently than I used to so I am probably a bit out of practice.

[ApacheTech]

For the archive, the basic idea is that when a club or a venue changes it's details. i.e. a club changing venues or nights, a festival being postponed to a later date, etc; we have a running archive of the old details.

The website as it stands now just changes the details in the source code, there's no database. So, when details are changed, if they aren't copied into Excel or similar then they are lost forever. Basically, if people phone up asking for information, we currently have no easy way of recording what details came before.

It's a feature that my client has asked for within the site project. If it isn't as simple as just cloning the information from one database to another, I will talk to them about dropping the idea, or modifying it.

The ideal scenario is that in the back end, whenever a record is updated, a clone of the old record is sent to the archive db. When a record is deleted, it is moved to the archive db. It seems simple enough in theory and would effectively automate the process. I can also add a flag to turn archiving on or off, so that minor changes can be made without clogging up the archive.

[ApacheTech]

For a better understanding of what I need to achieve and the scale of the project, the website as it stands is at: http://www.folkorbit.co.uk

If you look at the source, especially on the content pages rather than the index, you'll see why it needs to be rebuilt from scratch. It was originally written in Microsoft Word, then ported across to Macromedia Dreamweaver (Yes, Macromedia, you read right!) and has been edited for the last decade or so in Design Mode rather than Source Mode. It's an SEO's worst nightmare!

I'm new to PHP and MySQL though. I have the template of the new site set up, it's just the database and the page content I need to sort out now. Later on, I'll also need help with the SQL Queries, but we'll cross that bridge when we get to it.

[james438]

I remember now, thanks for the link. It has been a while since I last visited your dad's site :). Table normalization was not my favorite topic of study as the concepts were difficult and painful for me to grasp.

I hope someone corrects me if I start giving inefficient coding suggestions.

You already have the template setup, which will act as a guide for setting up the rest of the code we need. It looks like we have the database tables designed. We can edit them later as needed. I would recommend adding them to your database renaming them to whatever makes the most sense to you. For me I do not like to use capital letters or spaces in my table names or column names as it helps to avoid bugs later. If you need help with that let me know. For columns that may have empty values I would set them up as NULL as opposed to NOT NULL.

After that create two forms. The first one is for the music gathering and the other will be for the links.

After that we can create the two submit pages that process the form data. This will be the more complicated part.

The queries we will save for last.

As far as archived data goes it sounds like we will just be creating a new record anytime we make any changes unless the archive option is turned off for that particular update. Your website will then display only that entry with the most recent timestamp. The older entries will then be viewable only in the archives.

When you have the database tables set up please post them so we're on the same page as far as table structure goes.

[ApacheTech]

I remember now, thanks for the link. It has been a while since I last visited your dad's site .

You a folkie? Or have I just linked it before on here?

[ApacheTech]

My current Sandbox site is at: http://phpsandbox.x10.mx/

I'm using PHP 5.3, HTML5 Boilerplate (PHPified), JQuery 1.7.2 and PHPCSS, I may change over to LESS if I get up to scratch with it. I like PHPCSS for now though. :D

I'm in the process of porting over the static info from the old site like the index page and the about and contact pages, then I'll work on the design for the other dynamic pages.

I have a separate site, in the /administrator/ folder that is .htpasswd protected that will act as the Administation Panel.

[ApacheTech]

I've written a bespoke templating engine based on mod_rewrite for .htaccess. An MVC is far too complicated for what this site needs, but I think what I've written is fairly bullet proof for now. I can add a proper bootsrapping class later on.

[james438]

We talked a bit about it in this post (http://www.dynamicdrive.com/forums/showthread.php?t=68576). 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.


http://phpsandbox.x10.mx/img/site/database_relationship_framework.png

[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! :D


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

[ApacheTech]

I hate MySQL!

I've now got as far as:



<?php

$links = $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

foreach($links as $link) {
array_push($link['websites'], $db->query("SELECT * FROM websites WHERE `table` IS 'links' AND `record_id` = " . $link['id']));
array_push($link['adverts' ], $db->query("SELECT * FROM adverts WHERE `table` IS 'links' AND `record_id` = " . $link['id']));
}

?>


I have no idea how to do the WHERE clause.

It gives the error:

Fatal error: Problem preparing query (SELECT * FROM websites WHERE `table` IS 'links' AND `record_id` = 1) 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 `record_id` = 1' at line 1

[james438]

I've been looking at your syntax and I am having trouble seeing any errors. Could it be your use of double quotes? Normally this is not an issue, but it may be if used with some php statements that also use quotes.

[james438]

I posted too late.

[ApacheTech]

I've finally got it working, but Valhalla knows why it wasn't.

I've used the code:



<?php

$links = $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

foreach($links as $id=>$link) {
$websites = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('websites');
$adverts = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('adverts');
$links[$id]['websites'] = array();
$links[$id]['adverts'] = array();

foreach($websites as $key=>$value) {
array_push($links[$id]['websites'], $websites[$key]['url']);
}

foreach($adverts as $key=>$name) {
array_push($links[$id]['adverts'], $websites[$key]['name']);
}
}

?>


It's not throwing up any errors and the var_dump seems ok. It's annoying though that I couldn't use plain SQL for it, I've had to use the MySQLiDB. I was hoping to learn SQL by doing this.