e_i_pi's Profile User Rating: *****

Reputation: 801 Master
Group:
Mentors
Active Posts:
1,701 (0.79 per day)
Joined:
30-January 09
Profile Views:
49,305
Last Active:
User is offline Dec 17 2014 10:35 PM
Currently:
Offline

Previous Fields

Country:
AU
OS Preference:
Windows
Favorite Browser:
FireFox
Favorite Processor:
AMD
Favorite Gaming Platform:
Playstation
Your Car:
Mitsubishi
Dream Kudos:
75
Expert In:
Databases

Latest Visitors

Icon   e_i_pi has set their status

Posts I've Made

  1. In Topic: Trying to get the max values in descending order mySQL

    Posted 9 Dec 2014

    You're wide open to SQLI here:
    + " SUM(CASE WHEN EMAIL LIKE '%" + email + "%' THEN 1 ELSE 0 END) as rated "
    
    

    I would suggest running your email variable through a whitelist of alphanumeric plus "SQL-safe" symbols (e.g. @-_., there may be more)
  2. In Topic: I need help understanding how to normalise a database

    Posted 3 Dec 2014

    I would think that the Start_Date_Time and End_Date_Time would go in a Schedule table. The reason I think this is that you will have recurrences of programs, e.g. Saturday Sports Hour, Sunday Night Talkback. There won't necessarily be one date-time for this. The program's name and topic might not change, but the times and presenter may (what if Joe Football is off sick?). Hence the Schedule junction table - to match a Program to a Presenter, and then allocate a timeslot for that instance of the program.

    In the case of having a Schedule table, and that table having a primary key of ScheduleID, this then changes your proposed Music table above. Instead of having [Prog_ID, Presenter_ID] as the primary, you would simply have [Schedule_ID], though you may want to record the time it was played as well. You might also want to change the name of the table from Music to MusicPlayed, or something similar, because...

    You can now create a Tracks table. The Tracks table will have the columns Track_ID, Title, Artist, Label. You can then put a Track_ID foreign key constrained column in the MusicPlayed table.

    In terms of normalisation, it doesn't stop there though. We might have multiple tracks by the same Artist, so instead of having the column Artist in the Tracks table, you might have the column Artist_ID, and foreign key constrain it against the Artists table. You may also want to have an Albums table. If there are multiple Artists per Album, you may include an AlbumArtist function table. You may also have a Labels table to constrain against, and in this case you would remove Label from the Tracks table, and include Label_ID to the Albums table (as Labels release Albums, not Tracks).

    At this stage, it would be worth your while to look at the Wikipedia articles on normalisation. They all have the same example given, but explain how to reorganise your tables to meet the various normal forms. Here's the links:
    1NF
    2NF
    3NF
    3.5NF (aka Boyce Codd normal form)

    You can further read on 4NF, 5NF, 6NF, though 4NF is about as high as you need to go with most applications, and with many data structures going beyond 4NF incurs performance penalties during queries. Higher normal forms tend to better for data storage, at the expense of performance. In my experience though, 3-4NF performs better than 1-2NF, as your information is stored in smaller tables with narrower row width.
  3. In Topic: Javascript files in the cache

    Posted 2 Dec 2014

    I'm not a server config expert by any means, but I believe it is the "must-revalidate" cache control directive that tells the client to check if the server-side file is different from the client cache. In .htaccess I use the following:
    # Set up caching on static files for modification plus 12 months
    <FilesMatch "\.(xml|txt|html|js|css)$">
    	#ExpiresDefault "modification plus 12 months"
    	Header set Cache-Control "no-cache, max-age=31536000, public, must-revalidate"
    </FilesMatch>
    
    

    ...and I believe that does the trick. Of course, you could use different settings for "modification plus 12 months" and "max-age=31536000" if you liked.

    Someone with a bit more know how on cache control might be able to confirm what I'm saying or correct me.
  4. In Topic: Header Size Issues

    Posted 2 Dec 2014

    Try background-size: 100%;, though it will probably look ugly when it is stretched. You could centre the image instead, using margin-left: auto; margin-right: auto;
  5. In Topic: I need help understanding how to normalise a database

    Posted 2 Dec 2014

    It looks like that diagram and the associated tutorial can be found here. I've never had the pain or displeasure of working with dependency diagrams. I'm sure whoever came up with them was a sadist.

    Think of your data this way:

    You have unique Presenters, so you need a Presenters table with PresenterID as the primary key. Anything to do with the presenter and only the presenter resides in this table. Stuff like the presenter's name, their date of birth, eye colour etc.

    You have unique Programs, so you need a Programs table with ProgramID as the primary key. Anything to do with the program and only the program resides in this table. This is where it starts to get tricky. A Program is kind of like a class in object-oriented programming. The program should only have things like the name and the genre of music played. The time, presenter, specific tracks played, etc does not reside here.

    Next we have a bridging table, which we will call ProgramSchedule. This is what ties together the presenters and the programmes. This table needs a primary key and a unique surrogate key. The primary key will simply be ProgramScheduleID, and will auto-increment. This is how we identify particular "shows". Let's also make the following unique surrogate key (PresenterID, ProgramID, TimeslotStartDatetime). PresenterID will be foreign key constrained against Presenters.PresenterID. Similarly, ProgramID will be foreign key constrained against Programs.ProgramID. Other columns will be TimeslotStartDatetime, and TimeslotEndDatetime. I don't think we need anything else beyond that.

    Finally, for the Irish music board, we need to know what tracks are played. For this we need two tables - one to hold the tracks, and the other one a bridging table between ProgramSchedule and the Tracks table. Do you want to have a guess at what columns might be in those tables, and how they foreign key constrain (i.e. relate) to other tables in the schema?

My Information

Member Title:
= -1
Age:
40 years old
Birthday:
June 24, 1974
Gender:
Location:
Australia
Years Programming:
4
Programming Languages:
HTML, XML, jQuery, PHP, SQL, MySQL, VBA, VBScript, PostgreSQL

Contact Information

E-mail:
Private
Website URL:
Website URL  http://www.artofwar.cc/

Comments

Page 1 of 1
  1. Photo

    cupidvogel Icon

    09 Jun 2012 - 11:30
    Ha ha, I really liked your "if(navigator.appName == 'Microsoft Internet Explorer')" theme!
  2. Photo

    Duckington Icon

    04 Apr 2012 - 00:57
    It's not live yet, still developing and then going into beta test. I haven't actually decided on a name yet, so no domain for it.
  3. Photo

    modi123_1 Icon

    13 Mar 2012 - 06:56
    Ha.. well good luck with it.. it's always a good butt of a joke.
  4. Photo

    RudiVisser Icon

    08 Mar 2012 - 15:20
    You know, I only just worked out what your avatar is. Love it!
  5. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  6. Photo

    RudiVisser Icon

    06 Sep 2011 - 00:37
    Thank you very much :-D
  7. Photo

    e_i_pi Icon

    05 Sep 2011 - 22:15
    Ah thank you :) I'm a fan of minimalism, so I wwanted something nice and simple, plus you just can't go past red and black!
  8. Photo

    no2pencil Icon

    05 Sep 2011 - 19:52
    Love your avatar!
  9. Photo

    Sayid Ahmed Icon

    13 Jul 2011 - 14:02
    thanks for the add.
  10. Photo

    Dormilich Icon

    17 Feb 2011 - 13:25
    just wanted to congratulate for the funny thread title "OOP, PDOs, pain, tears".
Page 1 of 1