Database structure
A relational database uses tables to store data. Each of these tables corresponds to an entityA concept in which data can be collected e.g. Customer, employee, appointments. relationshipIn a database, the link between entities. Can be one to one, one to many, or many to many. are created between entities.
Vicki's Vinyl has many customers. Each customer can place many orders. Each order may, in turn, be made up of many parts. Each part relates to individual albums.
For example: Alice is one customer who has placed many orders. Only one of them was placed in April and consisted of four albums.
Alice's order, placed on 09/04/16, was comprised of three parts. One of those parts was for two Radiohead albums. This database structure allows Vicki to record all of her sales.
Entity relationship diagrams
A databaseA structured set of data held in a computer which makes it easy to search, select and store information can be described graphically using an entity-relationship diagram (ER diagram), in which tables are represented by rectangles and relationships between tables by lines.
Relationships exist in ER diagrams where processes exist in real life. A customer places an order, while an order contains many parts.
There are three different types of relationship:
One to One
For example, a husband has one wife and a wife has one husband. This is represented by the ER diagram:
One to Many or Many to One
A sports team can have many players, but each player can only represent one team. This is represented by the ER diagram:
Many to Many
If a product can have several suppliers, and a supplier will provide several products (many in this instance means more than one), then this type of relationship exists.
A database should not be implemented with this type of relationship, and a linking tableA table used to resolve many to many relationships must be inserted between two ends of a many-to-many relationship 鈥 allowing each relationship to become a many-to-one or a one-to-many relationship (thus reducing the potential for redundant data).