Cardinality (data modeling)

For example, consider a database of electronic health records. Such a database could contain tables like the following:

• A doctor table with information about physicians.
• A patient table for medical subjects undergoing treatment.
• An appointment table with an entry for each hospital visit.

Natural relationships exist between these entities:

• A many-to-many relationship between records in doctor and records in patient because doctors have many patients and patients can see many doctors.
• 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.{{Cite web |last=Clarke |first=Alex |last2=Hasnani |first2=Aleen |last3=Al-Ahasan |first3=Abdullah |last4=Islam |first4=Nazmul |date=7 September 2022 |title=Data Modeling and Entity Relationship Diagram (ERD) |url=https://www.cs.uregina.ca/Links/class-info/215/erd/ |website=University of Regina - Computer Science Department}}
• 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.{{Citation needed|date=September 2022}}

In data modeling, 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.{{Cite web |title=Entity Relationship Mapping |url=https://docs.oracle.com/cd/A97688_16/generic.903/a97677/ormap.htm |access-date=1 August 2002 |publisher=Oracle Corporation}}

A complex data model can involve hundreds of related tables. Computer scientist Edgar F. Codd created a systematic method to decompose and organize relational databases.{{Cite book |last=Codd |first=E. F. |url=https://www.worldcat.org/oclc/19590880 |title=The relational model for database management : version 2 |date=1990 |publisher=Addison-Wesley |isbn=0-201-14192-2 |location=Reading, Mass. |oclc=19590880}} Codd's steps for organizing database tables and their keys is called database normalization, 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.

File:ERD-artist-performs-song.svg

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.{{Citation needed|date=September 2022}}

The entity–relationship model 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 Crow's foot shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship.{{Cite web |title=Crow’s Foot Notation |url=http://www2.cs.uregina.ca/~bernatja/crowsfoot.html |publisher=University of Regina}}

In the object-oriented application programming paradigm, which is related to database structure design, UML class diagrams may be used for object 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:{{Cite web |date=7 September 2022 |title=Cardinality |url=https://datacadamia.com/data/type/relation/modeling/cardinality |publisher=datacadamia}}

• Arity
• Entity-relationship model
• Unified modeling language

• [http://www.agiledata.org/essays/umlDataModelingProfile.html#Relationships UML multiplicity as data model cardinality] - http://www.agiledata.org
• [https://community.mis.temple.edu/mis3506digitaldesignfall2018/files/2018/10/Adam-Alalouf_Cardinality.pdf Cardinality in Data Modeling] - Adam Alalouf, Temple University
• [https://www.techopedia.com/definition/18/cardinality-databases Cardinality] on Techopedia
• [https://www.geeksforgeeks.org/cardinality-in-dbms/ Cardinality] on Geeksforgeeks
• [https://www.complexsql.com/database-cardinality-database-cardinality-with-real-examples/ Database Cardinality] on SQL World

{{database}}

{{DEFAULTSORT:Cardinality (Data Modeling)}}

Category:Data modeling

Category:Relational model

de:Kardinalität (Datenbanken)

pt:Cardinalidade (modelagem de dados)