which is the best structure?

[ghassan_aljabiri]

hello

my database contains two tables (subjects,lessons)
every subject consists of many lessons

[B]subjects table[/B]
--------------
subjectid      bigint (pk)
subjectname text

[B]lessons table[/B]
--------------
lessonid      bigint (pk)
lessonname text

now a relation of many to one should be constructed between lessons and subjects

i have two options and i want to know which is better and why?

first

construct a third table with the name lessonsInSubjects with two foriegn keys

subjects table
--------------
subjectid      bigint (fk)
lessonid       bigint (fk)

second

add a field to the lessonstable as a foreign key

[B]subjects table[/B]--------------
subjectid      bigint (pk)
subjectname text

[B]lessons table[/B]--------------
lessonid      bigint (pk)
belongsto    bigint (fk)
lessonname text

so which is better?

 

[rcombs4]

Thats completly up to you. Best is a very subjective word. The best normalized model could cause you a lot of extra work in the applicaiton. But the easiest model for you to implement might be a nightmare of a design.

So really the main question is if one lesson can be applied to different subjects. If one lesson can be in different subjects then you basically have to go with the first. But if not then I'd go with the second.

 

[cjard]

The second

The first is how you would break up a M:M relationship into two 1:M relationships

Rather than calling the field belongsto, i'd call it subjectId