Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Angry Need a separate table?

    I have a RDBMS design question and would like to seek help from various DB gurus~

    I am storing some information about Teachers, so I created a table called 'Teacher' PK [TeacherID int].

    For each Teacher, I also need to store their [Subject] they teach, their [Grade] they teach, and among others.

    My dilemma is whether I need to declare additional tables to accomodate the 1-to-many relationship between Teacher-Subject and Teacher-Grade. I thought about declaring a new table called 'Subject' PK [SubjectID int] with column [SubjectName varchar], and in addition declare a junction table 'J_TeacherSubject' PK [TeacherID int, SubjectID int] to denote teachers can teach multiple subjects. Pros: Clean and easy to add new subjects. Cons: Slightly more complex queries.

    Another solution I find is instead of creating new tables, I declare the column [Subject] of 'Teacher' to be varchar, then concatenate multiple subjects into a string, eg. ';math;sci;' I can now use SQL, LIKE '%;math;%' to find all teachers that teach math. Pros: Simple queries. Cons: Perhaps not the idea of a RDBMS.

    I know that the decision may lie under num of rows in 'Teacher' I am talking about. I expect the max num of teachers to be less than 100,000 with MS SQL Server as the backend... Please help, DB gurus, I have a lot of tables that are like such needs to denote a 1-to-many relationship and would love to hear suggestions from you!

    Which method is better?

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Use additional tables

    I suggest that you declare the addtional tables.
    Bradley

  3. #3
    Join Date
    Feb 2003
    Posts
    15
    May I know why so? Have you had bad experiences with the other design, or reasons to favor one over the other? Would love to listen... thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > "I can now use SQL, LIKE '%;math;%' to find
    > all teachers that teach math.
    > Pros: Simple queries.
    > Cons: Perhaps not the idea of a RDBMS."

    your "Pro" is not quite right, the queries are not that simple, and a normalized design is even simpler

    plus, LIKE string will end up not using the index, therefore performance is terrible

    your "Con" is also not quite right, it is a relationship, just, unfortunately, not in first normal form

    do some research on "first normal form" and you will find other reasons why the list in a varchar is a poor design


    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •