Learning database part 1

Posted on Categories database, programming

Data Integrity

Why do we need correctly designed database scheme

      • save space
      • Data Integrity
      • Enable flexibility
      • Faster processing

Working in a big company(100,000+), there are a lot of procedures you need to follow, quite often you need to fill in excel forms. Most of the time there are some information that is totally irrelevant to you, but you have to fill in anyway. because the other side do not know which part is rrelevant to your domain.

now that excel file can be very big because everyone in this procedure has to fill in a row, and in that long row, a lot of columns are garbage information. this is where a database can help:

  1. the long row in the excel file can be broken up into different section, you only need to fill in the relevant section: you fill in fewer characters, you save space
  2. there could be duplicate rows in the excel file, and later someone update of them, you ended up with two information that you don’t know which one to trust, with a database, we can solve this with a primary key that enforces uniqueness: you have data integrity
  3. after you collect back this big excel, you need to present it to the management team for approval. before that you need to organize it, you can use excel for that like filter…, but a database create much rich functionality: so the flexibility and faster processing

To enable all these functionalities, we have to bring in some new concept:

  • Atomic
  • Entity
  • Key(super key, candidate key,primary key,foreign key, composite key, compound key)
  • relationship( one-one, one-many,many-one,many-many)
  • modality(foreign key can be null/not-null)
  • index(clustered index, non clustered index)

some tools that can help us to communicate:

  • Entity diagram(Cardinality)

what are the benefits to use entity relationship in spring JPA?

  1. data integrity

Instead of write the logic by yourself, you can let database to enforce the rules:

  • the row has to be unique
  • column/attribute has to be a specific type

  1. faster query with index
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Index;
import javax.persistence.Table;

@Table(name = "region",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Region{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;


Leave a Reply

Your email address will not be published. Required fields are marked *