many-to-many relationship in database design - Stack Overflow
You can also see SQL databases in action by checking out the SQL scripts, apps When selecting data from multiple tables with relationships, we will be using . When retrieving data with a JOIN query, you can still treat this. It also explains how to represent many-to-many relationships using a technique In databases, there are a few different ways to describe the relationships. A many-to-many relationship is an intersection of two entities. have to use workarounds to handle many-to-many relationships in their code.
The most natural representation is shown at the top. A many-to-many relationship simply combines the identifiers of its underlying entities and uses them as the primary key.
This is the representation that most database developers use. The middle model is more cumbersome from a database perspective but still correct.
The model maintains the dependence on the underlying entities and enforces that the entities combination is unique. This model meets both database and programming needs. The bottom model is the incorrect design that we too often see.
It satisfies programmers but has lost an important constraint — that the combination of Person and Company is unique. The relationship identifier combines the primary key of one entity with an arbitrary sequence number.
Design pattern: many-to-many (order entry)
The sequence number adds bulk to the model and database but no meaning. Use Parallel References Another bad idea is to use parallel references. Join tables typically hold fields that might not make sense to have in any other table.
You can add fields to the Enrollments table, such as a Date field to keep track of when someone started a class, and a Cost field to track how much a student paid to take a class.
Create a relationship between the two Student ID fields in the tables. Then create a relationship between the two Class ID fields in the tables. Using this design, if a student registers for three classes, that student will have one record in the Students table and three records in the Enrollments table—one record for each class the student enrolled in. For example, to display a list of all the classes a student enrolled in, create a portal on a layout based on the Students table.
Design the portal to show related records from the Classes table. Then add the appropriate fields from Classes to the portal.
Database Design - Many-to-many
As you browse through records in the Students layout, the portal displays all the classes a particular student is enrolled in. To uniquely identify each order line, we need to know both which order this line is contained in, and which product is being ordered on this line.
The two fk's, from Orders and Products, together form the only candidate key of this relation and therefore the primary key. There is no need to look for a smaller pk, since OrderLines has no children. Data representation The key to understanding how a many-to-many association is represented in the database is to realize that each line of the junction table in this case, OrderLines connects oneline from the left table Orders with one line from the right table Products.
Each pk of Orders can be copied many times to OrderLines; each pk of Products can also be copied many times to OrderLines.
But the same pair of fk's in OrderLines can only occur once. In this graphic example, we show only the pk and fk columns for sake of space.
Large image - Description text [ Previous: Keys ] [ More: Notecards ] [ Next: Links to this site are welcome and encouraged.
Individual copies may be printed for non-commercial classroom or personal use; however, this material may not be reposted to other web sites or newsgroups, or included in any printed or electronic publication, whether modified or not, without specific permission from the author.