Good database design has two major components, the back-end database as outlined below and the front-end interface that is seen by the user.
Front-end interfaces were typically desktop applications and now more often web browser based.
Simply, a back-end database is a collection of information in tables that is managed by a database engine.
Tables
A table is a collection of data such as a list of employees.
The vertical like information is the column, and the horizontal related information is the row.
Employee
ID
Dept.
Salary
Smith
45
Sales
$35,000
Jones
54
Sales
$42,500
Gates
15
Admin
$52,500
In the above example, Employee, ID, Dept. and Salary are columns while Smith, Jones and Gates are rows.
The power of databases is when tables are linked through relationships in a query.
When the first table is linked to the second table through Department then the following query is the result.
Dept.
Department name
Sales
Sales Office
Admin
Administration
Employee
ID
Dept.
Salary
Department name
Smith
45
Sales
$35,000
Sales Office
Jones
54
Sales
$42,500
Sales Office
Gates
15
Admin
$52,500
Administration
Not only can specific columns be selected from specific tables but selection criteria can also be applied to limit the result to only some of the rows.
In the above example, a selection that includes only employees in dept Sales would produce rows Smith and Jones.
If the Department name was changed in table 2 then the change would automatically appear in above query.
This is significant because having relational tables avoids duplicating data and makes updating simpler.
Database Engines
Data in the database is stored and retrieved by the database engine that forms part of the back-end database.
SQL Server Express
is a free limited version of the full Microsoft SQL Server that is recommended for larger sites and critical data.
If an alternate, free, robust and platform independent back-end database is required then MySQL is an option.
For most small-business installations, Microsoft SQL Server 2019 Express edition is suitable. This free edition has limitations compared with the full commercial editions.
Limited to using 1 CPU and 4 cores.
Limited to using 1 GB of total RAM per instance.
Limited to 10 GB per database.
No Analysis Services.
No Integration Services.
No Notification Services.
No Database Mail.
No Report Builder.
No SQL Agent.
No Database Tuning Advisor.
No native log shipping.
Otherwise SQL Server Express has all the features and power of the commercial product.
In the list of limitations, the significant one is the absence of the SQL Agent and with that the inability to natively schedule database backups.
Eniware has a replacement for the SQL Agent that can not only schedule full and differential database backups but transaction log shipping.
Transaction log shipping allows database activity on a primary server to be replicated on a secondary server or servers.
This allows you to have a warm standby backup server that has the same database information as the primary server.
If the secondary server is off line for a period, the primary server is not affected and the secondary server will automatically re-synchronise when it comes back on line.
With the Eniware addition to SQL Server Express, backups and Log shipping are logged and optionally an operator may be notified by e-mail if the backup or log shipping fails or completes.
Databases