0

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?

  • Why was this downvoted? Comments that ask for clarification are valuable for improving questions and should usually accompany a downvote. I would be more than happy to make any improvements necessary as like I said I am relatively new to relational databases – johnchase Sep 5 '18 at 15:35
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.