Hi,

I have some doubts here and I hope someone would be able to help me out with it.

I'm developing an Online Community, mapping university environment.

My ERD has the following:

TERM, which has a M:N relationship with SUBJECT.
Thus, I created a composite entity with the name TERM_SUBJECT that acts as a bridge between the two, with both SUBJECT and TERM having a 1:M relationship to TERM_SUBJECT.

Under my SUBJECT, I have a few more entities such as NOTE, FORUM, GRADE, ASSIGNMENT, etc...

My problem is, in this way, in the event that I archived subject "DBMS" under term "Spring2002" and I wanted to run a search on notes that were used for Spring2002 DBMS subject, I can't because the entity NOTE is related to SUBJECT only.

I thought of putting what's originally under the SUBJECT entity such as NOTE, FORUM, GRADE, ASSIGNMENT, etc... to TERM_SUBJECT.
It just doesn't seem right and I'm not sure if I can even do that because TERM_SUBJECT is a composite entity.

Another solution that comes to my mind is adding the primary key of TERM as a foreign key to SUBJECT. This increases the entries in the table as there'll be similar subjects appearing on different terms.
This I feel is messy and I would like to keep a clean list for my SUBJECT table, such as having each subject appearing only once.

Can anyone please help? I do appreciate it.

Thank you in advance.

Why does the note entity hang off the subject entity? Does the note entity represent notes made about the subject generally? Does it represent notes made about instances of subjects for terms?

I have the same questions about assignment, grade, and forum. What does grade represent? Does it represent a particular student's mark for a course? If so, you're probably missing a few more entities.

It sounds like term_subject could have it's own 'note' attribute, unless you're planning on storing multiple notes for the same term_subject. In that case you'd want a foreign key in a note table pointing to term_subject.

Hi bcrockett,

Thanks for the reply.

NOTE hangs off SUBJECT because a note is really related to a subject. It's like we have notes for each subjects we take.
Regarding your question "Does it represent notes made about instances of subjects for terms?"
I don't quite understand what you mean but what I intended to do was to to be able to have different sets of notes in different terms for the same subject.
For example, the subject DBMS in term Summer2003 can have note1.doc, note2.doc, note3.doc. In term Spring2003, the subject DBMS can have note4.doc, note5.doc, note6.doc.
I want to be able to search for note5.doc for subject DBMS and know from which term it was in. The same goes the other way round where I want to know which sets of notes are available to term Spring2003, subject DBMS.
In my current design, I'm only able to know which notes are from which subjects only and not terms.
Can you please advice?

The same concept applies to ASSIGNMENT, GRADE, FORUM.
And yes, GRADE represents student's mark/grade for a particular subject.
About the missing entities, please advice.

Regarding TERM_SUBJECT, I do plan to to have multiple notes for a particular subject.
TERM_SUBJECT is actually a composite entity which I created and serves as a bridge between the M:N relationship of TERM and SUBJECT. In unnormalised form, TERM and SUBJECT shares a M:N relationship. After normalisation, I created a TERM_SUBJECT composite entity and there are 1:M relationship between TERM and TERM_SUBJECT and 1:M relationship between SUBJECT and TERM_SUBJECT.
I initially relate entities such as NOTE, ASSIGNMENT, GRADE, FORUM to SUBJECT in unnormalised form where TERM_SUBJECT has not been created yet. After normalising, can I shift what I initially relate to SUBJECT to TERM_SUBJECT?

Please help. Any help would be greatly appreciated.

Thank you in advance.

Hi, serioussam.

OK, it's getting a little clearer for me, but we have a very long way to go.

We might best begin by getting some terminology straight. When you insert an intermediary entity (terms_subject) between two entities that have a many:many relationship with each other, it's called 'resolving' a many:many relationship. Normalizing has to do with eliminating redundant data, and is quite different. Both are important, and you should be clear about what they are.

It's also important to think about what entity represents in the real world. I mentioned missing entities earlier: when you're modeling a system to keep track of grades, be clear about what each grade represents. If it represents a student's mark in a course, then it should have a relationship with that course. The course should have a relationship with the student, too.

Hi bcrockett,

Thanks again for your reply. And thank you for distinguishing between an intermediary entity and normalisation.

Since you have a clearer picture of what my intention is, can you give me your opinion on the way I do things that I did, such as the relationships I explained to you?

As I said, the TERM_SUBJECT is a composite entity, or according to you, intermediary entity.
I would like to ask, can this composite entity(TERM_SUBJECT) has relationships with other entities?
In the example I gave, I said TERM_SUBJECT is a composite entity between TERM and SUBJECT. I originally relate entities such as NOTE, FORUM, GRADE, ASSIGNMENT to SUBJECT. Can I modify that and relate them(NOTE, FORUM, GRADE, ASSIGNMENT) to TERM_SUBJECT instead?
This allows me to do things that I wanted to do, such as searching for notes under a subject and able to determine which term the subject that contains the notes was in.

Please advice.

Thank you in advance.

Yes, if that's what they represent. It's hard for me to know what each of your entities are for without a full description of each, but I think it may be closer to what you are trying to model.

Some of those entities may be related to subject. Some may be related to entities that you haven't talked about yet. Grade, for instance, is probably related to something like 'student_term_subject', and 'student_term_subject' might be related to 'term_subject' and 'student'. There might be another entity in there somewhere if you're running the same subject multiple times during the same term.

Hi bcrockett,

I think I got it. Thanks a lot.