Help with database table design - it's been a while!!!!

[PaulStat]

Ok theory is this should be quite a simple design, but I'd like to check with those in the know. I have some XML data I'm wanting to insert into a MySQL database.

There are two types of xml file, channels.xml and xx.xml (where xx is the channel id)

channels.xml has entries like this (it's basically a listing of all the tv channels available and their id)

<CHANNEL>
   <CHANNELNAME>Five USA</CHANNELNAME>
   <CHANNELID>2008</CHANNELID>
</CHANNEL>

So the corresponding xml file for this channel would be 2008.xml, which has entries like so

<PROGRAMME>
   <CERTIFICATE></CERTIFICATE>
   <DURATION>360</DURATION>
   <STARTTIME>06:00</STARTTIME>
   <DATE>27/09/2010</DATE>
   <SUBTITLE></SUBTITLE>
   <FILM>false</FILM>
   <PERFORMERS></PERFORMERS>
   <WIDESCREEN>false</WIDESCREEN>
   <ENDTIME>12:00</ENDTIME>
   <SUBTITLES>false</SUBTITLES>
   <REPEAT>false</REPEAT>
   <EPISODE></EPISODE>
   <PREMIERE>false</PREMIERE>
   <BNW>false</BNW>
   <STARRATING></STARRATING>
   <PROGTITLE>Teleshopping</PROGTITLE>
   <DIRECTOR></DIRECTOR>
   <YEAR></YEAR>
   <DEAFSIGNED>false</DEAFSIGNED>
   <CHOICE>false</CHOICE>
   <NEWSERIES>false</NEWSERIES>
   <DESCRIPTION>A chance to buy goods from the comfort of home.</DESCRIPTION>
   <GENRE>Consumer</GENRE>
</PROGRAMME>

So based on this information I plan to have the following tables

CHANNELS

CHANNELID | CHANNELNAME

GENRE

GENREID | GENRENAME


PROGRAMMES

PROGRAMMEID | CHANNELID | GENREID | PROGTITLE | SUBTITLE | EPISODE | YEAR | DIRECTOR | PERFORMERS | PREMIERE | FILM | REPEAT | SUBTITLES | WIDESCREEN | NEWSERIES | DEAFSIGNED | BNW | STARRATING | CERTIFICATE | DESCRIPTION | CHOICE | DATE | STARTTIME | ENDTIME | DURATION

Should I normalise out into further tables or does that sound ok?

 

[PaulStat]

ok thanks, I spotted the genre and amended. So so far we have

CHANNELS

CHANNELID | CHANNELNAME

GENRE

GENREID | GENRENAME

PROGRAMME

PROGRAMMEID | GENREID | PROGTITLE | SUBTITLE | EPISODE | YEAR | DIRECTOR | PERFORMERS | PREMIERE | FILM | REPEAT | SUBTITLES | WIDESCREEN | NEWSERIES | DEAFSIGNED | BNW | STARRATING | CERTIFICATE | DESCRIPTION | CHOICE

CHANNELPROGRAMMES

CHANNELPROGRAMMEID | CHANNELID | PROGRAMMEID | DATE | STARTTIME | ENDTIME | DURATION

That way every programme has it's own entry and in theory shouldn't be duplicated in the table, whereas the channel programmes table can have multiple entries for the same programme at different start times? Make sense?

 

[PaulStat]

hmmm could do couldn't I, although I'm not sure what I would do when you have actors of the same name who are actually different people, there wouldn't be any way to know.

 

[PaulStat]

radiotimes data, there's a publicly available csv feed of data that gets updated every morning. Think I'll leave this level of detail out, it's not really necessary.

 

[PaulStat]

well the channel programmes table will get emptied out every day as there's no sense in keeping previous days programmes.

The programme table however will continue to grow