Database Schema

DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Database Schema

  1. #1
    Join Date
    Jul 2007

    Database Schema

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

    Data looks like following:

    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

    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?

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    What are the different pieces of information regarding the crew that you want to store?

    List them.

  3. #3
    Join Date
    Feb 2008
    Those look like the TMS ProgramIDs. They do give you the cast and credits (or crew) crosstabbed on each program record (ie - 20 cast_role_desc columns, 20 cast_first_name columns, 20 cast_last_name columns and the same for credits). Makes it a little difficult to write queries when you have to include 120 columns to make sure that you get all of the info. So, you want to normalize those columns out, yes?

    If I'm totally off-base, then just ignore the rest of this.

    What I did is similar to what you propose -

    ProgramCredits (like your ProductionCrew table) -
    ProgramID, RoleDesc, FirstName, LastName, CreditOrder (you might not care about the order, but they are in order).

    Then I have a similar table for the ProgramCast -
    ProgramID, RoleDesc, FirstName, LastName, CastOrder
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  4. #4
    Join Date
    Jul 2007
    I would have a Program table and a Person table.

    Program: ProductionID, Name, ...
    Person: PersonID, GivenName, ...

    I would then add a third table (a cross reference table: ProgramRole?) that would contain the ProgramID and PersonID as the primary key. That way a single person entry can be associated with multiple Programs and a single Program can be associated with multiple persons.

    You could also include credit order and role information on the cross reference table.
    Last edited by mdb002; 03-24-2008 at 03:02 PM.

  5. #5
    Join Date
    Jul 2007
    I think i only need 2 table is enough

    CREATE TABLE programs
    programid int NOT NULL PRIMARY KEY,
    programname nvarchar(200) NOT NULL,
    ... )

    CREATE TABLE productioncrew
    programid int NOT NULL REFERENCES programs,
    firstname nvarchar(200) NOT NULL,
    lastname nvarchar(200) NOT NULL,
    role nvarchar(200) NOT NULL,
    PRIMARY KEY (programid, firstname, lastname, role)

    since my DB is only 6 tables like Schedules, TVStations,
    ChannelLineup, Programs, ProductionCrews and Genres

    it's a very small DB and I dont' need all those ID things it's easy to
    do query with above

  6. #6
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    So, is this question resolved for you?

Similar Threads

    By RaeK in forum Database
    Replies: 5
    Last Post: 02-22-2012, 07:40 AM
  2. Replies: 0
    Last Post: 08-23-2006, 12:19 AM
  3. unable to read data from database
    By dppalepu in forum Java
    Replies: 2
    Last Post: 07-27-2006, 03:15 AM
  4. Replies: 0
    Last Post: 11-20-2005, 07:43 AM
  5. "Suspected" DataBase - problem with Log file
    By Nirit Touboul in forum Database
    Replies: 0
    Last Post: 04-01-2001, 07:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center