Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Need help with normalisation.

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

    Default Need help with normalisation.

    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

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

    Default

    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.

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

    Default

    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.

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

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

    Default

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

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

    Default

    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.
    Last edited by ApacheTech; 07-03-2012 at 01:27 AM. Reason: Spelling and grammar

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

    Default

    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.

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

    Default

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

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

    Default

    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?

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

    Default

    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.

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

    Default

    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.

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
  •