Organize a collection of data somehow for later usage and you will get a database. In the last couple of years, with the spread of the Internet, databases have become something you will come across everywhere on the World Wide Web. Most websites have a strong database behind them. With this phase of the web's evolution, if you work in software development, eventually you will need to create and plan a database from scratch. This article is here to teach you and show you what traits a solid plan should have.
Once you have the database management software (DBMS) up and running on your computer, your next step is to plan the database. The data model is a general theoretical structure of the database, and it is independent from the DBMS in which you implement it. To present how to construct this is the main objective of this article. The data model consists of a marking system that describes the data and an operation set that we use to organize the data.
We use the entity-relationship (ERM) data model to construct a conceptual structure for our database. The building blocks of the ERM are entity, attributes and relations. An entity is anything that can exist on its own and that we can differentiate an instance of it from another. For example, in planning the database of a company, entities are the employees, managers, branch shops (if they exist) and so forth.
While an entity may have multiple traits, one of its traits (or a combination of them) should identify uniquely any instance of the entity. To go back to the database of the company, this is the personal identification number of the employee. This is the key of the entity. In a company spread around the world, this could also be the employee's country of residence and passport number.
By no means should this include only a single trait. Furthermore, if a few traits of the entity cannot identify it uniquely, we can create an internal ID number that we assign to it upon entering it into the database. For example, there is the account or membership number you receive from the gym, which is generated when you sign up. It can even be text, as in the case of your e-mail address. You can create whatever you want. The idea is to immediately and uniquely identify an instance of the entity.
In the ERM, an entity is a rectangle with the name of the entity in the middle:
The attributes of an entity (traits of the instance) we model with an ellipse with the name of it inside it. We link this with a line to the entity to which it corresponds, and if it is a key, you underline the attribute word. For example:
The final piece is the relationship. We use this to link together two or more entities. The object used is a diamond in the middle containing the name of the connection, and two lines from and to the rhombus to connect it with the entity. A relationship can be, for example, between the shop and the employee. The employee works in the shop.
The relationship itself can have traits if it symbolizes the work, and may differ depending on to whom it is connected. A good example for this is the connection between the supplier and the shop. The price at which the supplier sells depends on the relationship, as next time he may charge extra or make a better offer to the shop. In this case the relationship will have a price attribute, and for this we use the same ellipsoid notation paired with the line connection to the diamond.