Relational Database Architecture

Database Architecture is an essential unit of product design\architecture and performance. Standardization and Rules are much needed while planning a database design factor.

Database Architecture covers complete schema design, under flow of application, using SQL, NoSQL and In-Memory cache structure. Interesting subject of schema design is – How to achieve everything? Performance, Load, RDBMS(multiple database entity relationship), Loose\Tight coupling with n-databases, degree of parallelism (DOP) and such.

Superlative Architecture Design – a distribution of user requests at various levels of the database such as Cache, NoSQL and SQL Database. Cache and NoSQL plays vital role as virtual database in front of transactional database and serving maximum data to reduce SQL database burden. To manage and maintain a virtual database, transaction chain is the key and must be well formed and horizontally scaled.

Database Design Elements

  • Database Technology Selection
  • Database Modeling
  • Product Development Factor
  • Database Administration Plan

Database Technology Selection

Each database technology provides certain well-defined features with functional advantages. It is important to meet the requirement with database technology beyond uniform solutions.

Database Technology Selection depends on criteria of technical operation, Development and Resource costing.

The most obvious goal should be data-centric solutions such as Capabilities, Robustness, Large Data size Pipelines, Reliability, Availability, Scalability and Disaster Solutions. Limited programmatic solution need to meet the requirement solutions such as procedure, function, Recursive Loops, etc.

Database Modeling

Database modeling is a logical architecture and a conceptual structure of a database with entity relationship. It represents the flow of application in order to schema-entity level of varying structure including data type and constraint.

As part of good architecture, the table structure should be in normalized form with appropriate data types. That will make the read and write transactions to be distributed in multiple entities.

Whenever you find business logic is fully database dependent with much load, you should look forward to manage product specific, module-wise separate database among multiple database server instance, if required.

Product Development Factor

Transaction concurrency plays vital role in database load management and performance. A bunch of database operations and requests should be in with well formed ACID property to avoid database Locking and Blocking problem.

ACID property and Locking\Blocking are huge topics to discuss individually and out of the scope of this blog. In brief I would say, database requests should be well formed with transactions.

Database Administration Plan

Nothing is good if administration is not disciplined. In order to have decent administration plan once should have following things well documented:

  1. Index Maintenance Plan
  2. Backup strategies
  3. High Availability(Failover)
  4. Disaster Recovery
  5. User Management
  6. Security measures, policies and standards
  7. Installation of platforms
  8. Query performance monitoring
  9. Functional unit monitoring  

There are more but these are the needed things in an administration plan along with the standard resolving steps.

As we all know, a centralized system is preferred for small and medium sized products for better performance, where all applications run together including database. However, sometimes it leads us to gloomy results. In order to avoid such  adverse situations, it is suggested to separate the user applications from the physical database.

Why separate the application server and database server?

Privacy and security: Database server will allow application server requests only. If someone wants to connect production database server then in middle we allow only one system for deployment and Querying.

CPU and Disk I/O Performance: When a query retrieves data from the database, the CPU would remain idle. And if application and database are in same server, then huge requests\responses of individual may become the cause of insufficient CPU or Memory.

Speed UP and Scale UP: Application server have different hardware and configuration requirements than the database servers. Database servers fare better with a lot of memory and a fast disk while web servers only require ample memory to manage requests. Horizontal scalability, It is easy to add web servers effortlessly and put them into load balancer to handle more load.

Points to be taken care of, while designing a Database:

  1. Relationship between tables must be with integer value.
  2. Use LOB datatype only when required.
  3. Each varying columns must be defined with length.
  4. Avoid duplicate type of records with n-Level normalization.
  5. Audit history tables.
  6. No heap table in database.
  7. Database indexing.

At smartSense Solutions, we provide a well structured database solution with Semantic Data Modeling, Normalization, Database Query Optimization Solutions, Security and Administration tips.