Database Design – Define One-to-Many Relationship Without Primary Keys

database-design

I am new to relational databases, I am not sure if I am asking this question correctly so any feedback is appreciated. I have seen a few questions about storing one-to-many relationships, but none that I have found seem to have data in quite this format.

I am more familiar with a document based approach, so I'll present my example data here as JSON and would appreciated guidance on how this would be implemented in a relational database.

For example if I have data about school classes and want to store where a student sits, in Mongo DB I would do something like the following:

[{'_id': 'asdf', 
  'name': 'English 101',
  'seats': {'john': '1a',
            'bill': '1b',
            'suzie': '1c',
            'julie': '2a'},
  '_id': 'qwer',
  'name': 'Biology 205',
  'seats': {'julie': '1a',
            'bill': '1b',
            'john': '1c',
            'suzie': '2a'}
 }
]

In a relational database I am less certain. I would imagine I would have a table where the primary key is equivalent to the '_id' field a column named 'name' and one named 'seats'? However I don't know what would go in the seats field (I thought about using a JSON string, thought there must be a better way).

Does this mean that I need a new table for each unique class that contains the seat information? Or is there another way to store this data in a relational database?

Best Answer

Here's the design that comes to mind:

4 tables: student, class, seat_class, class_student_seat. Each table has an ID column which is an autoincrementing integer. Typically the ID is the PK. Some tables have compound primary keys (i.e., multiple columns make the PK).

This is approximately a third-normal form (3NF) design; from a relational database perspective, it is modular an efficient (minimizes repeating data):

students
  student_id, (PK)
  student_first_name,
  student_last_name

class
  class_id,
  class_name ('biology 111', etc.)

seat_class
  seat_id,  (PK)
  class_id, (PK)
  seat_name ('1a', '1b', etc.)

class_student_seat
  class_id,  (PK)
  student_id,(PK)
  seat_id    (PK)
Related Question