Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    5

    Multi-Column Primary Key or Join Table?

    I'm fresh out of school, and very new to practical database design. My colleague has been in the IT industry(not specifically database) for some time, and we've had a discussion over the following 2 designs for our database:

    A uni-directional One-To-Many B
    B uni-directional One-To-Many C

    Design 1:
    Tables: A, AB, B, BC, C
    AB and BC are join tables.
    Primary Key A: AId
    Primary Key B: BId
    Primary Key C: CId


    Design 2:
    Tables: A, B, C
    Primary Key A: AId
    Primary Key B: (BId, AId)
    Primary Key C: (CId, BId, AId)

    Argument against 1: Joins are expensive.
    Argument against 2: Complex primary keys might cause expensive lookup operations?


    Any comments? Which would you recommend? Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    sorry I don't really understand the abstraction of the design. it may be me but I find it hard to relate your outline to a real world problem.

    joins are "expensive", but poor physical design is more "expensive"
    selecting the correct sequence of a compound primary can reduce the "expense" put the expected most used element first.

    scheme 1 includes additional tables which have no purpose apart form some theoretical performance benefit, but I suspect that benefit is illusory. looks good in theory, but in practice isn't achieved.

    however you oculd prove the design options yourself by prototyping them and then runnign queeries. however one hting to bear in mind is that most db's will do things in background to improve performance. depending on the loads on the server this optimistation my involve loading tables into memory.

    beqr in mind that another reason to use compound primary keys, and arguably one of the prime reasons to use a relational database is to enforce data integrity. if the design has been normalised then usinng compound primary keys can redcue the risk of data redundancy and duplicates.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2011
    Posts
    5
    Sorry let me try and contextualize this to a real world problem. I have also modified the relationships and actual entities involved(due to confidentiality) somewhat:


    A school has many teachers.
    A teacher can belong to more than one school.
    A teacher teaches many students.
    A student can be taught by more than one teacher.

    These are all bi-directional relationships. Given a student, I should be able to tell what school he/she belongs to and vice versa.

    Solution A:

    Tables: School, School_Teacher, Teacher, Teacher_Student, Student
    School has primary key school_id
    Teacher has primary key teacher_id
    Student has primary key student_id
    School_Teacher is join table with foreign key references to school_id and teacher_id
    Similar for Teacher_Student


    Solution B:
    Tables: School, Teacher, Student
    School has primary key school_id
    Teacher has primary key (teacher_id, school_id)
    Student has primary key (student_id, teacher_id, school_id)


    I personally argue for solution A, because I feel multi-column primary keys convolute the design and cause inefficient lookups? I have almost no experience in this, thats why I can't put up a convincing argument for my superiors and colleagues. Their only argument/mantra is that "joins are expensive", which makes me feel like they're simply blindly quoting a phrase that they picked up somewhere.
    Last edited by oinkl; 04-13-11 at 03:39.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oinkl View Post
    A uni-directional One-To-Many B
    B uni-directional One-To-Many C
    this structure does not require join tables

    Quote Originally Posted by oinkl View Post
    Primary Key B: (BId, AId)
    Primary Key C: (CId, BId, AId)
    these tables do not need composite PKs -- not if "Bid" and "Cid" are unique identifiers


    joins are not "expensive" if you need information from more than one table -- they are inevitable, inescapable, ineluctable

    also, complex primary keys do not cause expensive lookup operations


    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2011
    Posts
    5
    Yes it was, thank you very much.

Tags for this Thread

Posting Permissions

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