Cardinality (data modeling)
Encyclopedia
In data modeling
, the cardinality of one data table with respect to another data table is a critical aspect of database
design. Relationships between data tables define cardinality when explaining how each table links to another.
In the relational model, tables can be related as any of: many-to-many, many-to-one (rev. one-to-many), or one-to-one. This is said to be the cardinality of a given table in relation to another.
For example, consider a database designed to keep track of hospital records. Such a database could have many tables like:
In that model:
one-to-one relationship is mostly used to split a table in two in order to optimize access or limit the visibility of some information. In the hospital example, such a relationship could be used to keep apart doctor's personal or administrative information.
In data modeling, collections of data elements are grouped into data tables. The data tables contain groups of data field names (known in the science world as database attributes). Tables are linked by key fields. A primary key assigns that field's special order
to a table: for example, the DoctorLastName field might be assigned as the primary key of the Doctor table (#correction: PK are suppose to be unique. People can have same last name. Maybe introduce a new field called DoctorID). A table can also have a foreign key which indicates that that field is linked to the primary key of another table.
A complex data model can involve hundreds of related tables. A renowned computer scientist, C.J. Date, created a systematic method to organize
database models. Date's steps for organizing database tables and their keys is called Database Normalization. Database normalization
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, so there is are common sense data modeling tactics
called de-normalization which combine tables in practical ways.
In real world data models careful design is critical because as the data grows voluminous, tables linked by keys must be used to
speed up programmed retrieval of data. If data modeling is poor, 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.
As an alternative to UML, older Entity Relationship Diagrams (ERDs) can be used to capture information about data model cardinality. A crow's foot shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship.
Data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
, the cardinality of one data table with respect to another data table is a critical aspect of database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
design. Relationships between data tables define cardinality when explaining how each table links to another.
In the relational model, tables can be related as any of: many-to-many, many-to-one (rev. one-to-many), or one-to-one. This is said to be the cardinality of a given table in relation to another.
For example, consider a database designed to keep track of hospital records. Such a database could have many tables like:
- a Doctor table full of doctor information
- a Patient table with patient information
- and a Department table with an entry for each department of the hospital.
In that model:
- There is a many-to-many relationship between the records in the doctor table and records in the patient table (Doctors have many patients, and a patient could have several doctors);
- a one-to-many relation between the department table and the doctor table (each doctor works for one department, but one department could have many doctors).
one-to-one relationship is mostly used to split a table in two in order to optimize access or limit the visibility of some information. In the hospital example, such a relationship could be used to keep apart doctor's personal or administrative information.
In data modeling, collections of data elements are grouped into data tables. The data tables contain groups of data field names (known in the science world as database attributes). Tables are linked by key fields. A primary key assigns that field's special order
to a table: for example, the DoctorLastName field might be assigned as the primary key of the Doctor table (#correction: PK are suppose to be unique. People can have same last name. Maybe introduce a new field called DoctorID). A table can also have a foreign key which indicates that that field is linked to the primary key of another table.
A complex data model can involve hundreds of related tables. A renowned computer scientist, C.J. Date, created a systematic method to organize
database models. Date's steps for organizing database tables and their keys is called Database Normalization. Database normalization
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, so there is are common sense data modeling tactics
called de-normalization which combine tables in practical ways.
In real world data models careful design is critical because as the data grows voluminous, tables linked by keys must be used to
speed up programmed retrieval of data. If data modeling is poor, 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.
Formal Database Modeling Technologies
UML class diagram may be used for data modeling. In that case, relationship are modeled using UML associations, and multiplicity is used on those associations to denote cardinality. Here are some examples:| left | right | example | |
| 1 | 1 | one-to-one | person <-> weight |
| 0..1 | 1 | optional on one side one-to-one | date of death <-> person |
| 0..* or * | 0..* or * | optional on both sides many-to-many | person <-> book |
| 1 | 1..* | one-to-many | person <-> language |
As an alternative to UML, older Entity Relationship Diagrams (ERDs) can be used to capture information about data model cardinality. A crow's foot shows a one-to-many relationship. Alternatively a single line represents a one-to-one relationship.
External links
- UML multiplicity as data model cardinality - http://www.agiledata.org