You have to show the professors and the subjects they teach, but how will you do it? Let me show you how to represent this relationship by first defining the entities. One should be the professor entity, and the other is subject. One professor could teach one or many subjects, but one subject could also be taught by one or many professors.
Many-to-many relationships are not ideal. If left as it is in the above example, the data would be duplicated. This is quite inefficient. So, how would you resolve this many-to-many relationship between these two entities? By introducing a junction table into your model. It will resolve the many-to-many relationship into multiple one-to-many relationships.
It should look like this:. It contains the following attributes:. This composite primary key ensures that a professor can be assigned to one subject one time.
The same goes for the subjects; each one can be assigned to one professor one time. The composite key ensures the uniqueness of the attribute combinations. One professor can be allocated only once to the same subject. On the other hand, one subject can be assigned only once to the same professor.
It seems that the junction table serves its purpose. I am learning about databases and SQL for the first time. In the text I'm reading Oracle 11g: SQL by Joan Casteel , it says that "many-to-many relationships can't exist in a relational database. It seems to me to be the latter case, and the bridging entity minimizes the duplicated data. But maybe I'm missing something?
I haven't found a concrete reason or better yet an example that explains why to avoid the many-to-many relationship, either in the text or anywhere else I've searched. I've been searching all day and only finding the same information repeated: "don't do it, and use a bridging entity instead.
Think about a simple relationship like the one between Authors and Books. An author can write many books. A book could have many authors. Now, without a bridge table to resolve the many-to-many relationship, what would the alternative be? But how many do you add? A true many-to-many relationship involving two tables is impossible to create in a relational database. I believe that is what they refer to when they say that it can't exist. In order to implement a many to many you need an intermediary table with basically 3 fields, an ID, an id attached to the first table and an id atached to the second table.
The reason for not wanting many-to-many relationships, is like you said they are incredibly inefficient and managing all the records tied to each side of the relationship can be tough, for instance if you delete a record on one side what happens to the records in the relational table and the table on the other side?
Cascading deletes is a slippery slope, at least in my opinion. I suspect the author is just being controversial. It is an emergent result of declaring multiple 1-M relations to the table. However, it is a common approach to achieve the result of a M-M relationship and it is absolutely used frequently in databases designed on relational database management systems. I haven't found a concrete reason or better yet an example that explains why to avoid the many-to-many relationship,.
They should be used where they are appropriate to be used would be a more accurate way of saying this. There are times, such as the books and authors example given by Joe Stafanelli, where any other solution would be inefficient and introduce other data integrity problems. However, M-M relationships are more complicated to use. They add more work on the part of the GUI designer. Thus, they should only be used where it makes sense to use them.
If you are highly confident that one entity should never be associated with more than one of some other entity, then by all means restrict it to a 1-M. For example, if you were tracking the status of a shipment, each shipment can have only a single status at any given time.
It would over complicate the design and not make logical sense to allow a shipment to have multiple statuses. Of course they can and do exist.
That sounds to me like a soapbox statement. They are required for a great many business applications. Take a look at FaceBook. How many many-to-many relationships exist between friends and friends of friends? That is a well-defined business need. The statement that "many-to-many relationships can't exist in a relational database.
Many-to-many relationships are in fact very useful, and also common. For example, consider a contact management system which allows you to put people in groups. One person can be in many groups, and each group can have many members. The rules are: A student can be enrolled in multiple classes at a time for example, they may have three or four classes per semester.
A class can have many students for example, there may be 20 students in one class. This means a student has many classes, and a class has many students. We use a concept called a joining table or a bridging table. Our table would look like this: Student ID Class ID 1 3 1 5 1 9 2 1 2 4 2 5 2 9 This stores separate records for each combination of student and class. Our student and class tables remain the same: Student : Student ID Student name 1 John 2 Debbie Class : Class ID Class name 1 English 2 Maths 3 Spanish 4 Biology 5 Science 6 Programming 7 Law 8 Commerce 9 Physical Education Having our data structure in this way makes it easier to add more relationships between tables and to update our students and classes without impacting the relationships between them.
Opinions expressed by DZone contributors are their own. Database Partner Resources. You then create a relationship from the primary key columns of each of those two tables to the matching columns in the junction table. In the pubs database, the titleauthor table is a junction table.
In your database diagram, add the tables that you want to create a many-to-many relationship between. Create a third table by right-clicking the diagram and choosing New Table from the shortcut menu.
This will become the junction table.
0コメント