Basic databases concepts

Posted by in Databases

As a result of a question I received a few dais ago about basic database stuff, I realized that I’ve never written about basic database concepts on the blog, so, maybe a bit late, but I’m sure more than one reader can use a refresher on basic concepts. The fist one is table!

Table

It is a set of information that is related to each other.

For example, in a table of provinces, the number of inhabitants living in each province is a type of information related to the name of the province, or the assigned identifier.

Database

It is the set of tables that describe a higher-ranking entity. The tables can have relations between them and be complementary.

It is normal that to describe or work with a system, it is necessary to have more than one table to understand the whole, there being relationships between them.

As an example, if you want to know the towns of Spain, we will need a table of provinces, and another of municipalities, which are related to each other because each municipality is linked to one and only one province.

Records

They are the basic unit that describes an object or a transaction. The tables are formed by a set of them, always following the same pattern in each one of them.

Fields or columns

The record consists of one or more fields, which are the basic unit of information, and are a specific property of an object or transaction.

A record, in the case of the province table as an example, will consist of the province identifier, the name of the province, and the census of that province.

Type of data

Depending on the column, each of them will have defined a specific data type that will indicate the way of storing, the characteristics and the limits.

Thus, there are different types: numeric, alphanumeric or string type, date type, ….

Each database engine defines different types of data, and some are specific to each. Also, every engine even can have different engines, as it happens with MySQL and MariaDB.

Database design

If you want to design a database, you MUST follow a series of rules:

  • Each table must hace an unique and specific name.
  • Each table must have at least one field
  • Each table may have zero or more rows and may be unordered
  • Each value in a column will have the same data type
  • Every table will contain a field or set of fields that will be unique (whose value is not repeated between records) and with which it is possible to access the information. This unique value is uniquely identified with the record, and is known as the primary key.
  • There will only be one primary key per record.
  • A secondary key is a field or set of fields that are a primary key in another table. A table can have more than one secondary key.
  • The relationship between primary keys and foreign keys creates parent-child relationships between database tables.

In my case, I usually name the tables in plural, and each table usually has at least 2 fields (except very rarely). I also have my own rule that all tables always have a unique identifier field (autonumeric), even though I may use some other field. The main reason is that it is less expensive for the database engine to compare numbers than strings.

Another rule that I have is not to include prefixes in the names of the tables or queries, which you may find in some schemes. The reason is that I find it unnecessary.

I guess that every database designer has its own rules when designing and developing agains databases, not only me!

I hope this content will be useful for you, and if you can, contribute to maintain this blog! Happy coding!