Hi, newbie to this forum, and databases, to be honest.
I'm aksing for you lovely people's help with designing a database.
I have attached a photo of my relationship diagram.
The part in question is the box in the top left corner. Basically, there are a number of subjects (1-10) and a number of codes (A-D). Members of the database can have an infinite number of subject interests (1-10), each with a code rating (A-D). Additionally, each subject and code combination can be a teaching or research subject.
This led me to the tables I have. A table defining each of the subjects (1-10), one defining each of the codes (A-D) and a "link" table to join the two together with a member number, and whether it is a research or teaching interest.
However, in discussion with my lecturer today, about the correct-ness of the composite primary keys used in the "link" table, without me obviously being able to directly tell him my problem or him directly being able to offer advice, he said he had a horrible feeling he knows what I mean and that the database should be designed so the situation he thinks I have never arises, or it doesn't matter if it does.
Sorry for the paragraphs, but could someone help me with my design, if it is wrong. Or tell me if I'm right.