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
    Posts
    4

    Database Schema

    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

    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
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What are the different pieces of information regarding the crew that you want to store?

    List them.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2008
    Posts
    162
    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
    Posts
    161
    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
    Posts
    4
    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
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    So, is this question resolved for you?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. How to: ALTER AUTHORIZATION ON DATABASE
    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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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