Database design is a sequential process of adapting available knowledge and tools to represent and process information.
The real scope, specific task, description of the incoming information flow and general ideas about the information processing process are gradually added up to a certain conceptual idea of what a database is in a particular case and how to work with it.
Modern database
Relational relationships are at the heart of any information model. Solutions from Oracle are equivalent to MySQL in essence, but they are fundamentally different in many aspects. Database design is also a matter of security, volume of information, and accountability for data integrity, but these are secondary to the issue of designing an efficient, reliable, and user-friendly database.
Excel tables are no different from Oracle and MySQL in the context of rectangular (relational) structures: columns and rows=one cell at the intersection of the column name (field) and selection index (row). If you do not take into account the measure and amount of manual labor, then, thanks to the developed means of combining cells vertically and horizontally, Excel is ahead of even Oracle!
Excel, according to its basic idea, never "shines" the dynamics, the functionality of Oracle, and it cannot transfer something from one sheet to another "according to the remnants". Here Oracle is more promising, but its considerations on the issues of migrating large amounts of information and combining formalized positions from various sources leave much to be desired. Here MySQL is more promising: it does not set itself global tasks, but it does its job perfectly.
Relational relationships are convenient, practical and well-established tools, from private Excel-level solutions to Oracle global volumes, are used everywhere, in demand and they have a guaranteed job-provided future.
A modern database is tables, rows, columns and indexes surrounded by full functionality, advanced additional tools that take into account multiple operations, heavy loads and huge volumes.
Knowledge and experience of modern database management systems (DBMS) take into account not only the issues of reliability, data reliability, access regulation and security issues, but also make it possible to track negative external influences, analyze possible attacksand attempts to intentionally harm.
A modern database is a reliable foundation for any web resource and local application, the ability to migrate information, transform and transfer data, intersect and combine different views.
The only essential condition: highly qualified developer. To perform effective design of relational databases is available to a specialist, and more often to a team of specialists and experts in the field of application of the problem being solved.
Scope, possible solution and obstacles
Information circulates everywhere. Many projects are directly connected to the Internet, but the factor of having a formal data representation here is no better than the uncertainty factor when creating a web resource for a steel plant.
The development and massive interest in online stores does not give grounds and opportunities to transfer the experience of creating one store to creating another. The trade secret factor creates many obstacles to the transfer of knowledge, although, in fact, you should separate the actual store from the software tools created for this store.
Of course, the customer paid and the site code is his property. A characteristic feature of modernity: the transfer of knowledge and developments between tasks of the same type and related fields of application is impossible and this is a problem.
Parsing is a wide range of applications for database management systems. First of all, it is scanning information from the Internet. It is equally important to compare the information accumulated indatabase, and web visitor requests.
Keyword analysis also involves the need to form an optimal solution, but database design on Access may be more promising than on MS SQL Server or Oracle.
The list of information sources can be dynamic. Dynamics can be inherent in source database tables, table field names, and call (query) rules. Designing relational databases from multiple sources clearly forces you to design from the source data, and not from the optimal organization of the information collected.
There are two things that are inherent in any database:
- orientation to content, dynamic database generation algorithm in priority;
- orientation to use, the structure of the database is more important and the algorithm for using information is based on it.
In any field of application there is a formal model of the incoming information flow, an information storage model - the actual design of the database and a model (algorithm) for using data.
Various procedures and design steps
The basics of database design usually fall into three stages. Different specialists refer to the stages of work in different ways, but, in fact, there are three positions:
- conceptual planning;
- logical design;
- technical execution.
Practice contributes to established traditions. No matter how complex the scope and the problem being solved. It always takes choosing the right onetools. For example, you need to collect information from visitors to a web resource, but you need to compare it with data from MS SQL Server. The web resource is hosted on FreeBSD (Internet, Apache server), and MS SQL Server in another city is available via the company's distributed network.
In this solution, you first need to solve a particular problem: to establish data exchange with the internal server.
The technical execution of a common task will necessarily have an impact on the initial stage: it is rare that database design can be done from scratch. Even with proven problem solving technology, the scope is evolving, it is always required to do something differently than it was originally intended.
Recently, many theorists and practitioners operate with entities as special data. These are abstractions that allow you to describe the model of information at the input, during processing and in the final result - the database.
Data and entity views
DB design through abstractions and entities: the ability to create an information picture, define data types and relationships between them.
Usually such design of a database model ends with a graphical model, using MS Visio or visual tools of the chosen DBMS. Access has its own way of forming an information picture, MySQL has its own, and some content management systems hide the database altogether, imposing a data model on the developer through their own entities -objects of the task being solved.
A characteristic feature of many content management systems (CMS) is that they make an "application" for a level of greater abstraction when describing the information area of the problem being solved. The real database is hidden, CMS offers the developer his own idea of the information picture of the world.
As a result, the stages of database design are reduced to compliance with the fundamental requirements and the execution of the steps proposed by the creators of a particular CMS. There is nothing shameful in using the ideas of databases and their design from Symfony or Bitrix, Zend or Yii, but for the developer it is a "burden".
Ideally, database design tools should be selected and applied individually, without outside opinion, but with the application of experience and knowledge.
Ideal for the developer to be certified by Oracle, but perfectly acceptable for the developer's qualifications to include insights into Oracle's information ideas and a working knowledge of MySQL applications.
In complex projects and distributed information processing, not only the database is important, but also the sources of information, ideas about the needs of consumers.
Stages or team: balance of priorities
The requirement of consistency is of the most immediate importance. The basics of database design also include phasing of work, monitoring of intermediate results, rethinking of each completed stage based on the execution of the following type of work:
- systematic;
- phasing;
- feedback from any point in time, to the very starting position.
These provisions are abstract, but present in any theoretical and practical technology for creating an effective database.
No technology develops by itself, it is driven by people. The qualifications of the development team is essential. The database information model is not only a framework, but also information flows.
What is more important: beautiful graphics in the representation of the database structure or an accurate description of information flows in dynamics - a matter not only of the task and scope, but also the opinion of the development team in dynamics.
Personnel is everything, but in context: the conceptual design of a database is everything qualification. All people are unique, and in the field of information systems, representations of specific people exist and develop.
It's important to build a team of developers, not some mythical database design steps suggested by an authoritative expert. The authority of this specialist was formed on the basis of specific works, at a specific time. Work needs to be done today, new task, modern equipment, fresh technology, …
Possible reverse. There is Excel and Access and "abundant" data in these formats from ancient times, when Windows for Workgoups was still alive and well. Partially remained dBase and Quattro data. Today these words have already been forgotten, but the informationremained, it is in demand and needs to be extracted and formed new ideas.
Old and new: balance of knowledge
Cloud technology is not like the databases that Ashton-Tate did. What Oracle once bought is in no way comparable to what it does today. But variables, algorithms, functions, loops and conditions have remained in programming since those early 80s. Unless the concept of the procedure has sunk into oblivion, and everything remains as it was in ancient times.
Even modern ideas of object-oriented programming are clothed in the classic syntactic and semantic "fetters" of the last century.
What to do - programming is inertial, and the formalization of information and the design of information databases is more a process than a result. Staged work is a prerequisite for achieving results. But who counted the number of iterations from intermediate stages almost to the start of work?
Information is always dynamic, nothing stands still: especially the subject area of the task and user requirements. Each completed stage of work allows you to evaluate at a new level what has already been done and what remains to be done.
To consider designing a database structure as a task and get the final result is futile. As soon as the database is put into operation, a new idea will surely appear, even if the tool for creating the database was "simple" Excel, and not a fantastically powerful and versatile product from Oracle,manipulating millions of transactions, hundreds of thousands of concurrent users and terabytes of information.
The priority is not the structure of the database, but the formation of a qualified team of specialists, plus the mandatory requirement for greater dynamism of the result, so that upon completion of the work it would not be necessary to contact the developers, at least a couple of months.
Sequential development and/or high jumps
Windows is not a database, but it has a relic - the registry. The hosts file is simply an identification of the local machine's IP addresses and symbolic names. But through this file, information flows from different domains or to different DBMSs are formed.
It is possible to understand the many-sided Windows as a working computer or server, but it will not work in any way to justify the logic of the versions of this product. PHP isn't a database either, but the developers' arguments for why version 5 immediately follows version 7 are inconsistent. PHP is a MySQL access tool, its syntax defines how to form queries and get responses from the database using the SQL dialect.
Examples of incompatibility between modern programming tools and database support have become the norm in recent years, but this is not the most original. What will be behind the version of Windows 10? What are the prospects for Oracle Database 12c?
Information of the developer-author: Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level DBMS based on the Oracle Database 11g Release 2 DBMS code. This DBMS is free for development,deployment and sale, fast download and easy to administer.”
A user developer's perspective: "In 2013, Oracle released Oracle Database 12c (version 12.1.0.1) with key benefits of reduced storage costs, high data availability, easy database consolidation, and data access protection "".
Real Practice: An objective, efficient and effective logical database design is available only to a team of qualified developers. Getting a working result is not difficult, it is difficult to formalize the incoming information flows and determine the optimal foundation.
To the world of smooth shapes from precise rectangles
With the advent of object-oriented programming, data serialization has taken on a new lease of life. Indeed, everything around is just lines, preferably of indefinite length. Numbers and dates are also character strings.
The power and objectivity of relational relationships is undeniable, but does the dynamics of columns and rows damage their reputation? A table is simply data that may have a header (a list of columns) or no rows. Let the table be just a collection of data, not necessarily named.
The set of data can be heterogeneous and you can find data of different structure in it. Fundamentally, the homogeneity of the data indicates the development of the scope. The distribution of data by types and species is a sign of a systematic and objective approach, but it is still advisable to admit the possibility of structure dynamics.
If outputdesigning and creating a database beyond rigid structures and assuming that a table is a collection of rows that are not necessarily of the same type and similar in semantics to each other, then database design will change dramatically.
The subject of the work will not be a description of the database structure, but the dynamics of the movement of information. The stages of work will be divided into three centers of gravity:
- input information flow;
- transformation and movement of information within the database;
- select data to use.
There is no concept of table structure. There are no rows or columns. There is an abstraction - a given, of a certain structure, that satisfies a specific point in the algorithm. More specifically, the information processing function requires certain information in a certain amount.
The obligatory requirement of recursiveness of all information processing functions and focus on functions, not data, allows you to design a database in the dynamics of the accumulated information and incoming data flow, which are used at the initiative of the user, process or other function.
In fact: a use signal came, a fetch request was received, a trigger in the application fired, and the incoming information, through what was already there, provided the desired solution.
Fundamental knowledge and rigid constructions
Knowledge is the prerogative of man, programs are the burden of computers. The developer is free to apply knowledge as he sees fit in a particular situation. An ordinary person uses a lot of databases, without attaching importance to it. howdatabases are organized in the head of an ordinary person, no one knows, but everyone knows how he conducts his business, where he writes down what he finds, and when he needs to use it.
The result of the programmer's work - at the level of a program in "Basic", which retrieves data from the website of an online store via ODBC, is equivalent to a titled Oracle developer who makes a request to fetch data from the MAKS Aviation and Space Salon. Both results "freeze" in static from the moment the work is completed. This is not active knowledge that a person uses, this is the secret of creating a database design system.
The algorithm cannot be fixed. Everything must be defined dynamically. The merits of qualified developers are undeniable, but they do not lie at all in the elegant forms of solutions from Oracle, MySQL, or Access, which is limited in its capabilities. Another Excel spreadsheet can provide dynamic content and not require the participation of a programmer for more or less decent time after completion of work.
The question is how well the dynamics of the application area is formalized, not the structure of the database.
Live Solutions
It is impossible to plan work in such a way as to tie a team of professional developers to a task. Not that the team was offended, but this is not the right approach.
The task of designing a database should be formulated in such a way that the developed functionality improves itself, accumulates knowledge and, in the performance of its “duties”, does not start from the code,created by experts, but from the knowledge acquired through this code.
