Hi persons, Writing here from Cork in Ireland. I'm trying to design a database that will contain a Radio schedule for the college radio and I'm getting this design all wrong!
So ..
My columns are:
1) day INT (1)- this is for the numbers 1- 7 that are associate with each day of the week and relates to the number that is got from a Java Date Object.

2) hour TIME (00:00) This gives the time of the show. Since I've split each column up into 24 rows (the station just operates for about 10 hrs everyday so each row represents each 1/2 hr from 7 in the Morning to 7 at night.

3) Programme Name VARCHAR(30) - This is the programme name.

Now to my problem, I populate the rows with all the details from the schedule, and I can select any info I need (I'm using JDBC in my application) but this Schedule must be updatable by Radio Staff. Now if they insert a new programme how do I ensure it goes into the currect row on the table and replaces the other radio programme?
If the programme is two hours long, How do I ensure the programme goes into the 4 rows that would cover this time period?
Do I need to use a Primary Key,Unique Key, do I Auto-Increment? I just don't know. When it comes to Insert/updating the table, any code I've written ends up causing the database table to become really unwieldy.

Have I made myself clear? probably not.

Please try and help a stuck 'Paddy', Thanks in advance. TK

Suggest you break the database down a little.

Instead of trying to complicate areas like using one table, I suggest you use two in the following formats:

Table 1:

Table Name:- programme

Rows:

programme_name varchar (30),
programme_id integer (11) primary auto

Table 2:

Table Name: programme_schedule

Rows :

day integer 1
hour time
programme_id interger (11) primary

This way you can then insert the programme as a unique record, then insert as many schedules by 1/2 hour intervals as you need. Also when you select them back to edit all you are doing is to select the programme name from the programme table and any records with that ID.

Hope this makes sense. But doing it this way, you can have as many rows of data related to your programme as needed.

Mango