Cross-platform design of database

Design | data | Database developers should be aware that the use of non-standard SQL commands (such as Oracle, Microsoft, and MySQL database systems) is a fairly high risk. In other words, from a cross-platform and compliant perspective, you should try to adopt ANSI SQL, which is a platform-independent database language. Regardless of which database system you are using, if it fully supports SQL then it should support the ANSI SQL-92 standard. However, "should support" and "really support" are completely different. Not all systems fully support the ANSI SQL standard when making DBMS Cross-platform programming. The thrust of this article is to suggest the risks associated with using non-standard SQL and the related recommendations.


Do not use stored procedures

In some programming environments, for reasons of efficiency and security, stored procedures are the only way for programmers to develop database applications. Neither Visual Basic, C, or Java programmers need to understand SQL. However, in some other programming environments, stored procedures are completely disabled. MySQL is typically used in combination with the Apache Web server because the software is not only free, reliable, but also powerful, but MySQL does not support stored procedures.


--------------------------------------------------------------------------------

Definition of Stored Procedure
A stored procedure is a set of SQL statements that form a logical unit and perform special tasks. Stored procedures are used to encapsulate a complete set of operations or queries against the database server.

--------------------------------------------------------------------------------

Both Oracle and SQL Servers include built-in stored procedures that are easy to use. However, if it is wise to never count on them, you cannot guarantee that the database administrator has locked, deleted or modified these stored procedures. If you find that some of the stored procedures that are available are exactly what you need, you can use their functionality in your own code.

Typically, you can use stored procedures on Oracle or SQL Server but cannot be leveraged on MySQL. If you do use them, then you have to make sure that you don't use the commands associated with a particular platform (discussed later).

Do not use connections
Connectivity is a headache for both Oracle and SQL Server DBMS, because the two systems are fundamentally different in the concept of connectivity. Basically, the connection works for each DBMS is not always the same, and you may end up with unexpected sets of results. Before we talk about the conceptual differences between systems about connections and how to solve them, you should understand the basic connection clause:

Connection
A join is a SQL data table creation command that obtains data from multiple data sources.
and other connections
Equi-join gets the data from two separate data sources and combines them into a large table.
Internal/external connections
The INNER join (Inner join) joins the inner columns of the two tables. The outer joins connect the outer columns of the two tables.
Left/Right Connection
Left join (left join) connects two columns to the left-hand side of the table. The right connection connects the columns to the right of the two tables.
Composite/Complex Connection
All other connections-left/inside, left/outside, right/inside and right/outside.
Anyway, you have to remember that if you want your application to work on a variety of database servers, then don't use the connection.

ANSI sql:create, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
In short, the only commands you should use are as follows:

Create and drop under Data definition language (DDL).
Add, UPDATE, delete, and insert under Data manipulation Language (DML).
Gets the select of the data.
If you use a case-sensitive table or field name in these commands, or if the names contain spaces or other special characters, you'd better enclose the names in square brackets. Doing so helps prevent nonstandard characters from causing abnormal responses from the DBMS.



Referential integrity: Key and data type

The defining reason for using a relational database is to identify the relationship between the data and maintain the integrity of those relationships. This allows developers to store data in the least redundant, most efficient way. The key defines the relationship. Data types define the rules for storing and manipulating data. These are basic aspects of a database, but the specific syntax may vary depending on the database.

Data type
Only a very small subset of data types are supported by all database servers. Each DBMS has its own data type and the reason why they are used in this way. Some proprietary data types, such as the AutoNumber of Microsoft Access, are actually quite handy to use.

The following are the ANSI data types:
BIT, CHARACTER, DATE, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INTERVAL, NUMERIC, Real, SMALLINT, TIMESTAMP, Time, Varbi T, VARCHAR, CHAR

The following is a "standard" data type that ORACLE/ACCESS/SQL does not support:
INTERVAL, Time, VARBIT

Only Oracle supports date, but the following "standard" data types are not supported by Oracle:
BIT, DECIMAL, NUMERIC, TIMESTAMP

The following "standard" data types are not supported by Access:
BIT, CHARACTER, DATE, NUMERIC, SMALLINT, TIMESTAMP

In this way, only the following data types are guaranteed to be available on a variety of databases:
DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, Real, SMALLINT, VARCHAR, CHAR



Primary/FOREIGN Key

Assuming that there are two fields in a table, the syntax for creating the table is as follows:
CREATE TABLE [MyTable] ([Fielda] VARCHAR, [fieldb] VARCHAR);

To add a primary key so that each record in the table uniquely identifies you can use the primary key expression:
CREATE TABLE [Mytablea] ([RecordID] VARCHAR PRIMARY KEY, [Fielda] VARCHAR, [fieldb] VARCHAR);

When you create another table, one of the fields is indexed by the 1th table, you can define the field as a foreign key that has a relationship with the field in the 1th table:

CREATE TABLE [Mytableb] ([RecordID] VARCHAR PRIMARY KEY, [Fielda] VARCHAR, [fieldb] VARCHAR, [FIELDC] VARCHAR REFERENCES [ Mytablea] ([Fielda])

In the example above, all the fields are varchar types, but remember that a data type field can only index fields of the same data type.


ODBC and JDBC
If you're writing an application that uses a SQL database connection, you can do that with ODBC. If it is a Java application, you can use JDBC (in short, the Java Interface for ODBC).

Because you want your application to be the best not to get the administrator's attention, you should include all the necessary ODBC drivers for your user's DBMS. In this way, if users move from one platform to another, the drivers they need are already in advance.

Your application should programmatically determine what DBMS the ODBC driver is currently using. So you can use the powerful, platform-specific commands on the database server.

The Battle of alternative browsers
The most popular browsers are, of course, Microsoft's Internet Explorer and Netscape's navigator. But regardless of which one you're using, does it guarantee support for all existing sites? Wrong! A lot of fancy things, such as DHTML, Frames, and multimedia, are all supported by the non-standard HTML tags in both types of browsers.

Because the program is at a crossroads in these two international standards, you have to write code for two browsers or write the most universal functional code to give up a lot of dynamic content.

The database server also faces the same problem. ANSI SQL-92 is a standard that everyone agrees on. However, Oracle, Microsoft, and other database vendors have also made a lot of the same features that have corrupted SQL code. As a result, you not only have to write code that complies with the basic specification (SQL-92), but the code also has to implement different features based on the specific product you are using.

Within your own application, you must correctly determine what type of database the application will use and where the database should be located. You also have to deal with any errors that the application or user produces. Of course you don't want the program to crash (or server crashes) simply because the file is deleted or the network cable is loosely connected. On a well-designed network, database servers are often physically detached from workstations and Web servers and application servers, so disconnecting is common.

Summary
As a web developer, you need to be cautious about the dangers of using non-standard SQL commands for your applications. The central idea of this article is to recommend using ANSI SQL to write Cross-platform applications. Use ANSI SQL to reduce the headaches of nonstandard dialects of business database systems.