myNet.Dbase

Why does website need database? Because Database empowers website. Without database, website does not achieve its full potential and power.

At the core of myNet.Office is built in myNet.Dbase, a user friendly interface, that lets you, with non SQL database programming background, can do Create, Update, Delete, Queries…. and all DBMS (Database Management System) online easily. myNet.Dbase is designed for easy to run on any platform Windows, Unix, Linux. It is designed specifically to work with very large, high volume sites that connect to databases back end. myNet.Dbase can contain millions of records.

There are many web databases but it costs money for license, hard to maintain, required developers, and required big infrastructure. myNet.Dbase is easy to use, powerful, and affordable.

What can I do with myNet.Dbase?

Simply, myNet.Dbase helps you store data and manage data on the web. Then Process, Share and Collaborate data and information with customers, staffs, and others. Things you can do:

1) To Create new table like Employee_data with maximum 64 fields per table.
2) To Delete the table.
3) To Update or Edit the table.
4) To Backup table, Save table Employee_data As another copy table named BAK_Employee_data  .
5) To Add extra column field, such as EMAIL in the table.
6) To Rename filed such as NAME become LAST_NAME and Add extra field FIST_NAME
7) To Search single table, multi tables for contact with the phone number is 416
8) To Insert a new contact record into Employee_data table.
9) To Input, Update, Delete, View... the data of table use table Data Entry
10) To Send Email, electronic invoice from table record.
11) Add extra advanced fields like ENUM, SET to an existing table.
12) Built in SQL language that allows users run Database Programming, and get results displayed in a table row/col format. This is all power you need for database.
Example: Select Fname, Lname, Phone  from table_employees 
13) Import/Export data to file.
14) View the display of table in different view.
15) Automatic generate Data Entry for the table.
16) It is Relational Database. Which means you can have multi tables having relationship of 1-to-1, 1-to-many, many-to-many, in order to create complex big database.
For example, the built-in table x_Customer has 1-to-Many relationship with table x_Invoice.
17) .. More ....

myNet.Dbase -> Main Screen


Picture: myNet.Dbase main screen. It is a scroll list. Thus even the table has 10000 records. It loads only 10 or 50 .. records per page at one time. Thus load faster.

To edit a record or insert, simply click on the link primary key of the first column or for Data Entry. Click send email quickly to that person.
 

MAIN SCREEN COMPONENTS	DESCRIPTION
CODE AREA	Area in the left corner where you submit SQL database programming.
[Submit SQL Code] button	Click on this button to run the SQL programming code. The result will display in table row/col format. In this example the SQL code= Select * from _acm_employees. Which means read and display all data of table _acm_employees.
[Save As Query] button	Will save the SQL programming code in library as Query. Query is a short database program. Later, you retrieve and run it like a script. No need to enter code again. Such as, you have the code to read and display: Total, Gst, Pst ... for the first quarter in library programmed by some developers. You can open and run the Query script any time.
[ Create New Table] button	Click on this button will take you the Create New Table Template Tool in order to create a new table.
[ Add New Entry] button	Click on this button will take you the Data Entry screen in order to create a new record for this opening table.
expand	This button lets you hide the top area so that the display area has bigger space. Click again will un-hide the top area that include Code Area and Admin Area.
ADMINISTRATION AREA	Area in the right corner where you select Table to display pull down list of all tables in order to do Action of database admin like: Insert, Update, View, Search, Delete... records.
[ACTION] list	Click on Action pull down list, you will see a list Action of database admin: 1) SEARCH: will let you search info of the display table base on Phone, or Email, Name... 2) DELETE TABLE: Let you delete the open displaying table. 3) BACKUP, SAVE AS TABLE  Save the opening table for another copy of this table with different a name. For example, Copy table customer to another copy table named em_customer. Then add extra EMESSAGE for eCampaigner.
[ACTION] list continue...	4) DROP PRIMARY KEY Delete the primary key of this opening table. 5) ADD A TABLE PRIMARY KEY Add the primary key for this opening table. Make sure use DROP first. 6) ADD A COLUMN | ADD ENUM COL Add extra column field like EMAIL for the opening table. The extra column can be type of Date, Varchar, Enum... 7)  MOVE COL POSITION & CHANGE TYPE Move a column like POSITION from position 4 currently to 8 after PHONE. Change its type from Varchar to Enum type. You can keep unchanged. (see picture). Do not move Primary Key = EID column always in the first column. 8)  RENAME A COL & TYPE Rename a column like POSITION to name TITLE and change its type of POSITION from Varchar to Enum at the same time. You can keep unchanged as Varchar type. Do not rename or change type of Primary Key = EID column always in the first column. 9)  DELETE A COL Delete a column like POSITION. Do not delete Primary Key = EID column always in the first column. 10)  REORDER AUTO NUMBER KEY Re-order the key EID to continuous number. ** WARNING ** Do not re-order myNet.Office tables begin with x_, like x_customer, or x_invoices... because x_customer key = Customerid is linked to  Customerid of other tables like x_invoices. If you re-order x_customer table, thus change its CustomerID. Thus will not matched with CustomerID in x_invoices table.
STEPS TO DO ADMIN ACTION LIST	To do Admin Action list: 1) Select Table: to open the table name such as: _acm_employees. 2) Select from the list of Action only one Action: such as DELETE A COLUMN, or ADD A COLUMN | ADD ENUM 3) Follow step by step prompted until all conditions satisfied and the RUN button from disables to enables. Click on [RESET] not continue. 4) Finally, click RUN button to run the command action. DONE!
TABLE DISPLAY OUTPUT AREA	The table display output of table when ever you submit a SQL code or select a table from the list of table.
Record per Page select	Lets you select how many pages, min 5 pages, max 1000 pages you want to display the output. The smaller pages display the faster loading.
<< First	GO back to the first record
<< Previous	GO back to the previous record
<< Next	GO back to the next record
Last >>	GO to the last record
List All >>	List all records of the opening table.
Reset	Reset the table to initial opening.
(sort Up) (Down)	Sort any column UP or DOWN by clicking on the column heading.
	If there are many columns display that is hard to read, slow to scroll. Delete that column display.
	Table relation icon of this filed ex. EMAIL with other tables that share the same EMAIL column. You must setup using Create Table Relation Template. Click on that relation icon will display the info of the other relational table. Mouse hover on that icon will display the relation table. For example, table x_customer shares the same EMAIL of customer = jsmith@netthru.com column with table x_invoice. When click on the relation icon on table x_invoice, will display all info of the table x_customer record that shares the same EMAIL =  jsmith@netthru.com. It lets you access info of that person from related table x_customer. The relation between x_customer and x_invoice is 1 -> to Many.
ACTION column	is an action commands that let you do on records of the opening table.
Edit	Edit, Update this record.
Delete	Delete this record. Before delete, a prompt asks to confirm.
Email	Email the person by looking up the field EMAIL of this row record.
View	View entire info of this record. You can use View menu to setup the current view of table display if the table has many columns displayed.
eBilling	Send an electronic invoice from a database record.
myNET.DBASE SUBMENU	From main menu [Net.DBase] is a list of sub menu such as: Administration, Tables, Reports, Form, Queries... are called Database Objects.
Administration	Open the main screen myNet.Dbase. The above picture.
Tables	Open the Create New Table Template where you begin to create a new table using interface.
Reports	Run the table report.
Form	Make a web form data entry for the open table.
Queries	Open a Queries Library where stores all short SQL database programming code. Click on each Query will run the program and display data in the main screen interface.
View	Let you select the View of selected columns for the current opening table in order to reduce the loading time, and view only selected column. For example, table x_customer has more than 35 columns too big to display, and slow down the loading. There are many fields you are not interested. Use View to select only column you want to see. But you can always see all data for that record by click on View icon from Action column.
Relationship	Open the Create Table Relationship Template where you begin to create a new table relationships using template interface.
Import/Export	Open an interface let you import raw or text file exported by other databases.  It also let you export raw data text file in format like comma delimited so that other external third party tables can read.
Properties	Table properties.

WARNING: Tables used by myNet.Office are named beginning with x_ like x_customer, x_invoice. Please do not name the same format. Do not delete.

Introduction to Database

Basic Database Administration	Description
What is Database?	Is a data warehouse, where you store data and information. A database contains a lot of tables.
What is Web Database?	- Unlike fixed PC database stored on PC you can't access from any where. Use web databases, you can work, share with staff, people any where, any time. Your staff, people from different multi locations, just login your website and access the web database. - Web database is no limited by how many license users can use the database like PC database. - Think fixed PC database like Outlook email vs. web database like web based Hotmail, Yahoo.
What is Admin Database ?	Is a set of operations or tasks that you do on the data, such as: INSERT, DELETE, UPDATE, SEARCH...
Table Note: All tables used by myNet.Office for configuration begins with x_, such as: x_email_property table. Do not name same format.	A database is made up by many tables. Each table stores different category of information. Example tables: Employee data, Contact, Sale Inquiries... Table contains field or column name, i.e., L_NAME, TITLE, EMAIL ... to define the table. There are few important notes about table: • Table name is one word. Can't name separated by space like employee data, but OK with employee_data • Table name is case sensitive. You should name table in lower case so that it can work on both server UNIX, LINUX, or WINDOWS platform. • In order to get data from table, we need a key. Table should always have a key called Primary Key. If you don't know key then just add an ID auto inc key. • If you can't create table using code, select Create Table Template Tool wizard. It will take care the rest.
Record or Row	Row contains data of all Fields of a table. myNet.Office's table can have hundred of thousands of records.
Field or Column	• Table contains names, called Field. Such as table Sales_Inquiries has fields like Phone, Name, Email.. • Like name of table, name of field is one word. Can't name First Name, but First_Name is ok. • A field is defined by the data type ( i.e., varchar, or  text, Int ...) and the size. Example: EMAIL varchar (20) is defined as character (not number) and can contains 20 characters. It input over 20 characters will be truncated to 20 characters.
Data Type or Attributes or Field Characteristics. Not case sensitive.	To define type of data for a field, such as a number (Integer, Float..), or a character word (String, Varchar..)
varchar (has maximum 250 characters) Data type is not case sensitive. Varchar, VARCHAR is same.	is a string of many character. 'a', 'b' is a character. "hello" is a VARCHAR of 5 characters or varchar (5). To define a field called f_name you write f_name varchar (30). Thus First name with at least 30 characters. Don't need to fill all 30 characters. Can be blank or no character.
blob, text    (contain 64 thousands of characters, or few hundreds of pages) For example, you may want to declare (select) type for field: Resume, Cover_Letter as blob or text for your web form Submit Resume	• Text: to define a field like DESCRIPTION in table Sales Inquiries can have tens of thousand characters. • Blob: like Text data type that can contain tens of thousand words but in binary characters. Use Blob to store images files, word doc files... like for submit Resume form. • myNet.Office will automatically creates a Text Area ( big box) interface for input data or form for Tex, Blob data type. Here is the data type for String, characters. CHAR(x): where x can range from 1 to 255. VARCHAR(x): x ranges from 1 - 255 TEXT: slightly longer text, case insensitive, stores up to 65.000 characters. BLOB: slightly larger blob, case sensitive. Stores up to 65.000 characters. NOTE: If you need to contain more characters than 65.000, then use these type: MEDIUMTEXT A string with a maximum length of 16777215 characters. LONGTEXT A string with a maximum length of 4294967295 characters. MEDIUMBLOB A string with a maximum length of 16777215 characters. LONGBLOB A string with a maximum length of 4294967295 characters. How to use? Look at the blue code you declare DESCRIPTION as: DESCRIPTION text --> DESCRIPTION mediumtext --> or DESCRIPTION LONGBLOB
int, long ( long is bigger value than int)	• int is an Integer number. Exp: 1, 50, 13900. Maximum value is -35000< 0 < 35000. To define a field called Age as an integer: Age int. ( see define of COMPANY_SIZE data type as an integer in the blue code of table Sales Inquiries) • long is also defined as integer number but bigger value than int
float, double (double is bigger value than float)	for table field data is a decimal number. Exp: 1899.35, 26.00. To define a field called salary you write code: Salary double  ( See define of TOTAL_REVENUE data type as a double in the blue code of Sales Inquiries) Here is the data type for Number. INT: UNSIGNED integers fall between 0 to 4294967295 or -2147683648 to 2147683647. BIGINT: Huge numbers. (-9223372036854775808 to 9223372036854775807) FLOAT: Floating point numbers (single precision) DOUBLE: Floating point numbers (double precision)
ENUM or enum ( set of list of selection. Choose only one choice.)	Example: SUBJECT  Enum ('Product', 'Service', 'Other Helps' ). Use Create Table Template Tool, you just need to select its Name Type as Enum. • myNet.Office when encounter ENUM data type, will convert it into an option list to choose just one. • To add an ENUM field in table = my_support_center, example SUBJECT after SUBMIT_DATE column, you run this code: ALTER TABLE my_support_center ADD SUBJECT  Enum ('Product', 'Service', 'Other Helps' ) not null AFTER Submit_Date  • To Delete SUBJECT column (field) in table = my_support_center Alter table my_support_center  drop column SUBJECT
SET (Select many choices in a list selection)	LIST_PRODUCTS_SERVICES Set ( 'Cryptopology', 'Compaq Server XP 35', 'Perfect Mail', 'Cryptocard', 'Ebusiness Development Services', 'Online Consulting Services') This is data type used when users can select many choices in a set of many options.  Example, you have a LIST_PRODUCTS_SERVICES of many products and services you want users to select as many as they wanted by click on the check boxes [v]
DATE (yyyy-mm-dd) or DATETIME (yyyy-mm-dd HH:MM:SS)	Is for Date field : 2008-03-24 or DATETIME for field has date with time : 2008-03-24 14:56:35. Example, APPOINTMENT_ DATE is Datetime data type from table Sales_Inquiries. • myNet.Office when encounter field Date or Datetime will has a calendar for easy input data.
Int auto_increment is an integer number auto + 1.	Used for table Primary Key type like ID primary key. Table needs a key, if you don't know or don't need then just select Int auto_increment as its key. Every time insert 1 record, ID is automatically + 1. It can't be used for field only key.
Primary Key	An unique field to identify that table record. It shouldn't be duplicated 2 records that have the same key. Exp: emp_id, like Social Insurance, is a key because whole company, no employee has the same emp_id. If you don't need each row record is unique in a table the use the key Auto Increment Key like ID key of Sales Inquiries table. • Table should have a Primary key. Use Auto Increment Key like ID if don't know or don't need. • Primary key should always be in the first position of table. • Primary key is used to protect the integrity of data. For example, the Employee table if you use people FIRST_NAME as a key. What happens there are maybe many employees with the same first name John. When you do an UPDATE operation change the salary of John Smith, it also updates the salary of John Doer, John Lang... Who ever has first name John. As a result, every John has the same salary. Therefore, make sure primary key is unique for table. Use Employee ID rather than use Employee first name. • Not all tables required a unique primary key like Sales Inquiries table  but should have a key. If you are not sure, use an Int Auto Increment like ID of Sales Inquires table. Each record has a unique ID number auto increment when insert. • The most popular Primary key are: Email, Phone, ID auto increment, SIN, UserID, CustomerID. Email is unique and used common for online because no member or customer has the same email. It is also used to send info like Password, Invoice.. to people. So if you don't know to select Primary key, but see table has Email then use Email is Primary key.
Database Programming Language is SQL Learn more: INTRODUCTION TO DATABASE SCRIPT SQL.	Is database scripting language. You can use SQL to Create, Insert, Delete, Update... database. A database that has built in the ability of SQL Programming like myNet.Dbase will have the ultimate power of data manipulations and relational database. Database that doesn't have the ability of SQL Programming will depend on the many steps of gui and interfaces in order to get data is a weak database. You don't need to know but just in case the business logic required to get complex data, SQL becomes handy. • Not all databases built-in database programming, and Client/Server, Relational Web database
Example - The script to create a new database table. It helps you understand about	database data type, fields, and how to create a new database table using simple script.
CREATE TABLE my_support_center ( ID int not null auto_increment primary key, NAME varchar (50), Email varchar (50), Phone   varchar    (50), Company varchar (50), Submit_Date Datetime, SUBJECT Enum ( 'Question', 'Feedback/Ask', 'Help', 'Others Interest' ), LIST_PRODUCTS_SERVICES Set ( 'Hosting Server', 'Note Book', 'Hardware Product', 'Software Product', 'E-Development Services', 'Consulting Services', 'Training Services', 'Sales Inquiries'), Description Text, Name_Of_Support_Staff varchar(50), Staff_Email varchar(50), Staff_Phone varchar(50), Support_Time Datetime, Office_Notes Text ) ANALYZE THE DATABASE SCRIPT CODE. - CREATE TABLE  = Syntax to create new table. - my_support_center  = Name of new table. Must be lower case. - ID int not null auto_increment primary key = Name field = ID. Defined as Primary Key and integer number and auto increment. - NAME varchar (50) : NAME = name of the field. varchar = Data type of varchar - (50) = Size of this field that can contains 50  characters for NAME of that person. - Code is not case sensitive, like STAFF_EMAIL or Staff_Email is not important, space not important. - Field name, like STAFF_EMAIL or STAFFEMAIL, must be 1 word. Can't be: STAFF EMAIL	- To Create this table, Copy and Paste the blue code in the [Code Area] of myNet.Dbase. The click [Submit SQL Code] button. It will create the table my_support_center. - Next, enter data by clicking on the first column field or the primary key field of record. - Code is not case sensitive. EMAIL or Email is same. - Table name should be small case. - You can also create new table using Create New Database Table Using Gui Template Tool. But use Code is faster.  It is like English-syntax language. - See HOW TO CREATE NEW TABLE. - This database my_support_center contains advanced Data Type like: - SUBJECT  Data type = Enum Because Enum data type is used when there is a selection of one option in many options. - LIST_PRODUCTS_SERVICES data type = Set Because Set data type allows to select not on1 but many options. It is in the opposite with Enum. - Submit_Date Data Type = Datetime Because it is format: yyyy-mm-dd HH:MM 2009-12-28 15:35 - Office_Notes Data Type = Text  Because it can contains text up to 65000 characters, at least few hundreds of pages. - Click see the result New table my_support_center is created by simple database scrip

Picture: This is the database table = my_support_center just created using database script. After having created a table, you need to input data.

Click to see its Data Entry interface  Data Entry of table my_support_center  ( Click on picture to enlarge)

Create new table using interface Create Table Template no SQL programming involved

Login to your website as Admin -> Click on [Net. Dbase] topmenu -> Select [Tables] sub menu. It opens Create Table Template Tool Interface.


myNET.DBASE -> Data Entry Form

In order to input data into table, you open table Data Entry Form. It lets you: Edit, Delete, Update, Search, Insert new table records.
To open a row record for edit or insert, simply click on the link primary key of the first column to open Table Data Entry.

Picture: Table Data Entry for the first record EID =2. To Insert click [Clear] button first.

myNET.DBASE -> Advanced SQL Database Programming

You can admin myNet.Dbase database by using database programming or SQL. You can learn more click on button [SQL Help] from the left area.
Unlike Java, C/C++, SQL or database language is easy to learn like English syntax. You can do almost advanced SQL Database Programming by Copy your SQL code in the left Code Area. 
Then click on [Submit SQL Code] button to run the program. You will see the result at the bottom int the Output Area.

This is a few basic syntax SQL database programming. To run SQL code: Copy or Type the code in the [Code Area]-> Click [Submit SQL Code] button to run.

myNet.Dbase -> SQL Database Programming Tutorial

SQL Database Programming is a universal database programming language. Any good database like Oracle, Ms SQL... supported SQL will run SQL programming.
Click here: http://www.webdevelopersnotes.com/tutorials/sql/mysql_tutorial_selecting_data_using_conditions.php3 for the Database Programming Tutorial. We have setup a sample table named employee_data matched with table name in the tutorial for you to exercise. All you need to do is Copy the code in this tutorial and Paste into myNet.Dbase->[ Code Area]. Then Click Submit Code button. You will see exactly the result matched with the result of this tutorial. It is good for learning SQL Database Programming.

SELECT f_name, l_name from employee_data where salary > 70000 AND salary < 90000 This code will read the table employee_data and display First Name, Last Name of all employees who has salary greater than 70000 and salary less than 90000.	This is the out put result if run by MySQL on Unix, Linux, Windows server.
Picture: The same output result if run by myNet.Dbase
DESIGN A PRIVATE LINK DATABASE DATA ENTRY FORM - USE WITH EMAIL When you want suppliers, customers, partners, or in-house staffs privately to submit forms that are not posted on your website. You don't need to create a web page with form.  You don't need your in-house staffs to login as admin in the website in order to input data. You just need is create a table to hold form data. Then you use private link web database form. You send email with a link. The customers, the in-house staffs click on that link will take them directly to the private data entry form for that table. • To setup, all you need to do is: 1) Design a table for the form to hold data. 2) Send email this Url link = http://www.mycompany.com/pub/FormBuilder_Table.jsp?tb=_feedback . Click on this link will open _feedback table, and begin enter data. - Where you change _feedback  is the table name that will store form data. - Change www.mycompany.com to your company website domain name.

Conclusion

myNet.Dbase is not only built in a web database but also it is built in a set of database features of Database Management System (DBMS) that allow you, either a non web database skills or a web database programmer, can admin and maintain database.

You should be able to:

- Create new web database tables using programming or interface template.
- Use the Table Data Entry to Update, Delete, Insert... data.
- Do advanced Program Database SQL by using Code Area.
- Manage and Maintain your database without any web skills.