2

I was thinking three tables, one for entity A, second for entity B and third for the relationship and describe the total participation in the third table by importing primary keys from first and second table and terming them as not NULL. Am I on right track?

Consider a relation between students and courses, each student must be associated with at least one course (can be more than one too) and each course must be taken by at least one student (can be taken by more than one students too). This would result in a many to many relationship with total participation from both sides.

CREATE TABLE student ( sid INTEGER, age INTEGER, PRIMARY KEY (sid) )
CREATE TABLE COURSE ( cid INTEGER, timings DATETIME, PRIMARY KEY (cid) ) 
CREATE TABLE TAKES ( sid INTEGER, cid INTEGER, FOREIGN KEY (sid), 
    FOREIGN KEY (cid), instructor CHAR(12), PRIMARY KEY (sid,cid) )
Improve this question
5
  • Seems OK - might need a bit more of a clue as to what entities A and B are as well as the fields that you're planning on using to join them!
    – Vérace
    Oct 7, 2017 at 22:08
  • You can edit your own question and put that info in. Use the editing tools above the box to format the code. p.s. welcome to the forum - take the tour, visit the help centre. Using a many-to-many table (also called joining or linking tables) for students and course is absolutely classic for this sort of relationship.
    – Vérace
    Oct 7, 2017 at 22:55
  • Thank you so much for that info. I do have one more question, since sid and cid are primary keys from Students and Courses table do I have to specify a NOT NULL constraint on sid and cid in TAKES table too or is it redundant?
    – goodfellas95
    Oct 7, 2017 at 23:10
  • It is redundant - see here.
    – Vérace
    Oct 8, 2017 at 0:21
  • I'd make the columns NOT NULL in TAKES to guarantee that behaviour. Both because NULL may be allowed in where a foreign key constraint exists, and because it may save a little on database storage space when table values don't have to be stored with NULLs - since the fact of NULL has to be stored somewhere, and there may not be room in column data. For instance, Microsoft SQL Server adds as many bytes to row storage as are required to point to all columns, representing NULL - one byte for eight columns, two bytes for nine - unless all columns are declared NOT NULL.
    – Robert Carnegie
    Oct 8, 2017 at 18:06

1 Answer 1

Reset to default
2

Using a third table to hold links between two independent tables is the correct method when designing a simple many-to-many relationship. You don't specifically need flag the fields as NOT NULL because they form the Primary Key and so cannot be NULL anyway.

One note, however, you've stated in your example that a Student record MUST be associated with at least one Course record, and vice versa. Your table design would not enforce this requirement, it would only allow the possibility that Student and Course records could be linked - not that they must have at least one linked record.

Your example would not enforce total participation on both sides, and I am not sure that you even could enforce it. It raises the question of which comes first, the Student or Course record given that each of those record types requires at least one link to the other record type.

Without a Student record, no Course could be created because it doesn't have a linked record, and vice versa - it's the classic "which came first" chicken or egg scenario!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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