Table Schema 1

[eric91850]

I have a group of TV listing data need to map into database tables.

Data looks like following:

http://www.oniva.com/upload/1356/crew.jpg

I want to create a table for productionCrew of each TV program

the data is like -
crew -> programID -> member
-> member
-> member ... etc
-> programID -> member
-> member
-> member ... etc
-> programID -> member
-> member
-> member ... etc
... etc

Each member -> role + first name + last name

above are data from productionCrew of all TV program, for each
programID we have a list of members.

Should I merge all member into a big string?
Or should I use 2 tables to store the Crew data?

If i use 2 tables, how the fields / column will look like?

 

[SantaMufasa]

Should I merge all member into a big string?

Never! Stringing together multiple values in a single column is the worst way to disobey "First Normal Form". It is also an avoidable performance nightmare.


Properly normalised tables for your application might include:

PROGRAM:
    ID
    NAME
    <plus all other attributes that relate to a program>

PARTICIPANT:
    ID
    LAST_NAME
    FIRST_NAME
    <plus all other attributes that relate to a participant>

CREW:
    ID
    PROGRAM_ID
    PARTICIPANT_ID
    ROLE_TYPE (e.g., "Actor", "Director", "Producer", etc.)
    CHARACTER_NAME
    <plus all other attributes that relate to the crew entry>

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

BTW, Eric, I noticed you have been a member of Tek-Tips for 8 months now, you have started 11 threads in 4 different forums, yet once you start a thread, you never have come back to respond to any of the suggestions that people offer in your behalf, even to say, "Thank you".

It is important for the success of Tek-Tips that original posters leave feedback once others have taken the time and made the effort to respond. Please do take a minute to respond to not only this thread, but also to the other threads you have started since you joined last July. (Absent that, Tek-Tips Management tends to limit/revoke membership.)

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

Well spotted Dave, one of my pet hates in Tek-Tips.


In order to understand recursion, you must first understand recursion.