Reputation: 801 Master
- Active Posts:
- 1,701 (0.79 per day)
- 30-January 09
- Profile Views:
- Last Active:
- Dec 17 2014 10:35 PM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Favorite Gaming Platform:
- Your Car:
- Dream Kudos:
- Expert In:
Posts I've Made
Posted 9 Dec 2014You'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)
Posted 3 Dec 2014I 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:
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.
Posted 2 Dec 2014I'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.
Posted 2 Dec 2014Try 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;
Posted 2 Dec 2014It 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?
- Member Title:
- = -1
- 40 years old
- June 24, 1974
- Years Programming:
- Programming Languages:
- HTML, XML, jQuery, PHP, SQL, MySQL, VBA, VBScript, PostgreSQL
- Website URL: