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.

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.