What is database composition in database?

Answer 1

Composition is a term that comes from object oriented design. It is a relationship in which a number of parts comprise a whole, and these parts can only be part of one whole.

There is no direct translation of this concept to database design, but there are some situations in the database world that represent the concept of composition.

Field to record

A record is composed of fields and these fields are part of only one record. When the record is removed, the fields are also removed. So, each field has a composition relation with its record. This means that when you store a collection (a comma separated list of names for example) in a database field, this is in fact a composition relationship.

Record to record from other table

If you want to create a composition relation between records from a table A and records from another table B, where A is the whole and B the parts you must ensure that

1. There is a one-to-many relationship between table A and B. This is done by adding a foreign key to table B.
2. That all part records in B are deleted when the whole record in table A is deleted. This can be done by setting ON DELETE CACADE on the foreign key relationship.
3. That the records in table B don't become part of another composition relationship. But this restriction cannot be enforced by the database to my knowledge.