Results 1 to 4 of 4

Thread: Database design

  1. #1
    Join Date
    Oct 2002
    Posts
    2

    Database design

    Hi all, I am a novice at database designs and wondering if any one of you can help me.
    I am going to create a database with 3 core tables.
    Professors, Courses, software.
    The relationship between each of them will be many to many.
    Their size will grow to a certain point. (If graphed, it would show a decreasing rate in size increase)
    My dilemna is whether
    1. Create one huge table that maps the primary keys of the 3 core tables
    2. Creating 3 additional tables that maps 2 primary keys of the tables (ProfID&CourseID, ProfID&SoftID, SoftID&CourseID)

    There will certainly be a lot of updates and lookups on this database.
    It will also be attached to a web interface.

    Please fill me in on some advantages and disadvantages of using either option.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Database design

    To answer your question one would need to know more about the relationships. I don't think it's a matter of advantages and disadvantages, it's a matter of getting the CORRECT data model. Perhaps if you supply some more details someone can help.

  3. #3
    Join Date
    Oct 2002
    Posts
    2

    relationships

    Well..to describe the relationships....a professor can teach many courses. A course can be taught by more than one professor.
    One software can be used by many professors and courses. A professor and a course can use more than one software. Well...you get the picture...so it's a many to many relationship between all of them.

    Hope this description is clear

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: relationships

    Originally posted by david_setiawan
    Well..to describe the relationships....a professor can teach many courses. A course can be taught by more than one professor.
    One software can be used by many professors and courses. A professor and a course can use more than one software. Well...you get the picture...so it's a many to many relationship between all of them.

    Hope this description is clear
    OK, well consider these scenarios:
    1) a new professor Smith joins the college, who is an expert in Oracle and Java (assuming they are examples of software). As yet the college has not decided what courses he should teach. If you want to record his skills you will need a Professor/Software table, otherwise (with a Professor/Software/Course table only) you will not be able to record his skills until he has been allocated at least one course.
    2) Similarly, the new Oracle Java programming course requires the Oracle and Java software. If you want to record those facts BEFORE you have decided which professor will teach the course, you will need a Course/Software table.
    3) Perhaps Professor Jones teaches Greek Mythology, and doesn't know any software. To record that fact you will need a Professor/Course table.
    4) If Professors Black and White both teach "Oracle Java for Beginners" and "Oracle Java for Experts" then with a 3-way intersection table you would have to record:
    Professor Course Software
    Black OJBeg Oracle
    Black OJBeg Java
    Black OJExp Oracle
    Black OJExp Java
    White OJBeg Oracle
    White OJBeg Java
    White OJExp Oracle
    White OJExp Java

    So it seems likely you need 3 intersection tables. A single 3-way intersection table is likely to have lots of repeated information in it, and may have update anomalies (e.g. if you delete Professor Smith's only course record, you lose track of his software skills also).

    I hope that gives you an idea of the sort of requirements analysis you need to do.

Posting Permissions

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