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.

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.

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.

this structure does not require join tables

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?

Yes it was, thank you very much.