Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Cardinality (data modeling)
open-in-new
<h2 id="relationships">Relationships</h2> <p>For example, consider a database of <a href="/facts/Electronic_health_records/0my0rktq">electronic health records</a>. Such a database could contain tables like the following: </p> <ul><li>A doctor table with information about physicians.</li> <li>A patient table for medical subjects undergoing treatment.</li> <li>An appointment table with an entry for each hospital visit.</li></ul> <p>Natural relationships exist between these entities: </p> <ul><li>A many-to-many relationship between records in doctor and records in patient because doctors have many patients and patients can see many doctors.</li> <li>A one-to-many relationship between records in patient and records in appointment because patients can have many appointments and each appointment involves only one patient.<a class="footnote-ref" id="fnref:1" href="#fn:1"><sup>1</sup></a></li> <li>A one-to-one relationship is mostly used to split a table in two in order to provide information concisely and make it more understandable. In the hospital example, such a relationship could be used to keep apart doctors' own unique professional information from administrative details.</li></ul> <h2 id="modeling">Modeling</h2> <p>In <a href="/facts/Data_modeling/bryA2moP">data modeling</a>, collections of data elements are grouped into "data tables" which contain groups of data field names called "database attributes". Tables are linked by "key fields". A "primary key" assigns a field to its "special order table". For example, the "Doctor Last Name" field might be assigned as a primary key of the Doctor table with all people having same last name organized alphabetically according to the first three letters of their first name. A table can also have a foreign key which indicates that field is linked to the primary key of another table.<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a> </p> <h2 id="types-of-models">Types of models</h2> <p>A complex data model can involve hundreds of related tables. Computer scientist <a href="/facts/Edgar_F._Codd/QCSbPYkK">Edgar F. Codd</a> created a systematic method to decompose and organize <a href="/facts/Relational_databases/pEKGmJLy">relational databases</a>.<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a> Codd's steps for organizing database tables and their keys is called <a href="/facts/Database_Normalization/uLCKbIBz">database normalization</a>, which avoids certain hidden database design errors (delete anomalies or update anomalies). In real life the process of database normalization ends up breaking tables into a larger number of smaller tables.<a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a> </p> <p>In the real world, data modeling is critical because as the data grows voluminous, tables linked by keys must be used to speed up programmed retrieval of data. If a data model is poorly crafted, even a computer applications system with just a million records will give the end-users unacceptable response time delays. For this reason, data modeling is a keystone in the skills needed by a modern software developer. </p> <h2 id="database-modeling-techniques">Database modeling techniques</h2> <p>The <a href="/facts/Entity%E2%80%93relationship_model/U8A0mwYe">entity–relationship model</a> proposes a technique that produces entity–relationship diagrams (ERDs), which can be employed to capture information about data model entity types, relationships and cardinality. A <a href="/facts/Entity-relationship_diagram/U8A0mwYe">Crow's foot</a> shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship.<a class="footnote-ref" id="fnref:5" href="#fn:5"><sup>5</sup></a> </p> <h2 id="application-program-modeling-approaches">Application program modeling approaches</h2> <p>In the object-oriented application programming paradigm, which is related to database structure design, <a href="/facts/Unified_Modeling_Language/3tlNS0tH">UML</a> class diagrams may be used for <a href="/facts/Object_(computer_science)/eX99dtAw">object</a> modeling. In that case, object relationships are modeled using UML associations, and multiplicity is used on those associations to denote cardinality. Here are some examples:<a class="footnote-ref" id="fnref:6" href="#fn:6"><sup>6</sup></a> </p> <table><tbody><tr><th>Relationship</th><th>Example</th><th>Left</th><th>Right</th><th>Narrative</th></tr><tr><td>One-to-one</td><td>person ←→ birth certificate</td><td>1</td><td>1</td><td>A person must have their own birth certificate, it is specific to that person by its Id number.</td></tr><tr><td>One-to-one <i>(optional on one side)</i></td><td>person ←→ driving license</td><td>1</td><td>0..1 or ?</td><td>A person may have a driving license, it is specific to that person by its Id number.</td></tr><tr><td>One-to-many</td><td>order ←→ line item</td><td>1</td><td>1..* or +</td><td>An order contains at least one item</td></tr><tr><td>Many-to-one</td><td>person ←→ birthplace</td><td>1..* or +</td><td>1</td><td>Many people can be born in the same place, but 1 person can only be born in 1 birthplace</td></tr><tr><td>Many-to-many</td><td>course ←→ student</td><td>1..* or +</td><td>1..* or +</td><td>Students follow various courses</td></tr><tr><td>Many-to-many <i>(optional on both sides)</i></td><td>person ←→ book</td><td>0..* or *</td><td>0..* or *</td><td>A person may own many books(copies), and a book may be owned by many people(readers).</td></tr></tbody></table> <h2 id="see-also">See also</h2> <ul><li><a href="/facts/Arity/zSVuwHvP">Arity</a></li> <li><a href="/facts/Entity-relationship_model/U8A0mwYe">Entity-relationship model</a></li> <li><a href="/facts/Unified_Modeling_Language/3tlNS0tH">Unified modeling language</a></li></ul> <h2 id="external-links">External links</h2> <ul><li><a href="http://www.agiledata.org/essays/umlDataModelingProfile.html#Relationships">UML multiplicity as data model cardinality</a> - <a href="http://www.agiledata.org">http://www.agiledata.org</a></li> <li><a href="https://community.mis.temple.edu/mis3506digitaldesignfall2018/files/2018/10/Adam-Alalouf_Cardinality.pdf">Cardinality in Data Modeling</a> - Adam Alalouf, Temple University</li> <li><a href="https://www.techopedia.com/definition/18/cardinality-databases">Cardinality</a> on Techopedia</li> <li><a href="https://www.geeksforgeeks.org/cardinality-in-dbms/">Cardinality</a> on Geeksforgeeks</li> <li><a href="https://www.complexsql.com/database-cardinality-database-cardinality-with-real-examples/">Database Cardinality</a> on SQL World</li></ul> <h2 id="references">References</h2> <ol> <li id="fn:1"><p>Clarke, Alex; Hasnani, Aleen; Al-Ahasan, Abdullah; Islam, Nazmul (7 September 2022). "Data Modeling and Entity Relationship Diagram (ERD)". University of Regina - Computer Science Department. <a href="https://www.cs.uregina.ca/Links/class-info/215/erd/" target="_blank">https://www.cs.uregina.ca/Links/class-info/215/erd/</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li> <li id="fn:2"><p>"Entity Relationship Mapping". Oracle Corporation. Retrieved 1 August 2002. <a href="https://docs.oracle.com/cd/A97688_16/generic.903/a97677/ormap.htm" target="_blank">https://docs.oracle.com/cd/A97688_16/generic.903/a97677/ormap.htm</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li> <li id="fn:3"><p>Codd, E. F. (1990). The relational model for database management : version 2. Reading, Mass.: Addison-Wesley. ISBN 0-201-14192-2. OCLC 19590880. <a href="0-201-14192-2" target="_blank">0-201-14192-2</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li> <li id="fn:4"><p>Codd, E. F. (1990). The relational model for database management : version 2. Reading, Mass.: Addison-Wesley. ISBN 0-201-14192-2. OCLC 19590880. <a href="0-201-14192-2" target="_blank">0-201-14192-2</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li> <li id="fn:5"><p>"Crow's Foot Notation". University of Regina. <a href="http://www2.cs.uregina.ca/~bernatja/crowsfoot.html" target="_blank">http://www2.cs.uregina.ca/~bernatja/crowsfoot.html</a> <a href="#fnref:5" class="footnote-back-ref">↩</a></p></li> <li id="fn:6"><p>"Cardinality". datacadamia. 7 September 2022. <a href="https://datacadamia.com/data/type/relation/modeling/cardinality" target="_blank">https://datacadamia.com/data/type/relation/modeling/cardinality</a> <a href="#fnref:6" class="footnote-back-ref">↩</a></p></li> </ol>