1. Allows multi users access, retrieve, and fast database operation (Insert, Update, Delete..). For example, PC Database is not a Client/Server database. It is good for a single user uses. Oracle, DB2... are true client/server multi users database. XP is a single user OS. But Windows 2003 Server is a multi users client/server OS.
2. Lets you retrieve data very fast by scrolling row, and not loading them all. While non Client/Server database is flat, and retrieves all data every time it loads. You can't scroll them. For example, if your database has 1 million records of data. A none Client/Server database will load them all. But a Client/Server loads only 10 records at a time( or what ever you define how many rows to load ).
3. Scalability not Flat. 1 millions records or 10 records, the speed and the operation are the same.

myNet.Dbase is a true Relational Database. Not all database applications are relational database.

1. A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Relational databases are the most common kind of database in use today.
2. Relational databases are created using a special computer language, structured query language (SQL). So you have the power of SQL built -in the database.

myNet.Dbase is a Web Database. It is:

1. Location Independent – Your data is available wherever you have access to the Internet. It is simple: Login ->Access -> Sharing and Collaboration. That is it! You can't do this on PC database.
2. Ease of Sharing and Collaboration – With myNet.Dbase, you can share data with as many people as you want, regardless of their physical location. Web database is a multi-user system, so several people can work within one account, all at the same time. Collaboration, Collaboration, and Collaboration!
3. Scalability – A typical desktop database is designed for one person to use. myNet.Dbase is a client/server-based database platform. This way, it can meet your company's needs, regardless of its size. This means your data can grow exponentially without the restrictive limits of a desktop computer's memory, storage, and processing power.
4. Simple Integration with your Web Sites – Data is not very useful if it cannot be easily accessed online. myNet.Dbase helps you create custom forms, reports, and complete end-to-end applications without having to learn or use a programming language. Your database and the data it holds, and can be easily placed on your own web site.

 
Developing Relational Database begin with the Basic Schema Relational Database

Once entities and relationships have been identified and defined, the first draft of the entity relationship diagram can be created. This section introduces the ER diagram by demonstrating how to diagram binary relationships. Recursive relationships are also shown.

Figure 1 shows examples of how to diagram one-to-one, one-to-many, and many-to-many relationships.

Figure 1: Example of Binary Relationships

One-To-One Relationship

Figure 1A shows an example of a one-to-one diagram. Reading the diagram from left to right represents the relationship every employee is assigned a workstation. Because every employee must have a workstation, the symbol for mandatory existence—in this case the crossbar—is placed next to the WORKSTATION entity. Reading from right to left, the diagram shows that not all workstation are assigned to employees. This condition may reflect that some workstations are kept for spares or for loans. Therefore, we use the symbol for optional existence, the circle, next to EMPLOYEE. The cardinality and existence of a relationship must be derived from the "business rules" of the organization. For example, if all workstations owned by an organization were assigned to employees, then the circle would be replaced by a crossbar to indicate mandatory existence.

When to use One-to-One Relationship?

One-to-One Relationship is often used when you want:

1) You don't want to mess up, to change structure the original pre-built and programmed table, like x_customer. And you want to extend an original table having many smaller tables that have One-to-One with the original table in order to satisfy more business logistics on demand.
2) You want to divide a big table into many smaller tables in order to relieve its loading. It is slow loading for big table because it loads all fields, but not all fields you need to see.
3) If one data from one table, you can get one and only one data from another table then it is One-to-One Relationship.

How to Apply One-to-One Relationship in myNet.Office in order to extend the original pre-built table. For example, extend table customer = x_customer to give customer reward and promotion.

For example, we have pre-built a customer table = x_customer. It is about 40 fields. Maximum allowed is 64 fields in a table. So it is relatively big. See Table x_customer
Now, you want to extend table x_customer in order to give each customer the following:

- Each customer can have the Reward point, so you name the field = CUST_REWARD_POINT.
- Each customer can have 2 Promotions, so you name the field = CUST_PROMOTION1, CUST_PROMOTION2.
- Each customer can have 2 Coupons, so you name the field = CUST_COUPON1, CUST_COUPON2.
- Each customer can be categorized in membership = (Gold, Silver, Brown ) so you name the field = CUST_MEMBERSHIP

Now you create this table named Customer_membership_reward, an extend of the original table x_customer. So you make it the relationship = One-to-One with the x_customer.
The Primary Key of table x_customer = CustomerID. You then use this primary key as primary key for the new extend table of customer = customer_membership_reward
 
CREATE TABLE customer_membership_reward  (
CustomerID int not null primary key, /* Primary Key  of  customer_membership_reward and x_customer*/
EMAIL varchar (50),  /* a text 50 character Email address of this customer. It is used as Member Collaboration Key */
CUST_REWARD_POINT int, /* an integer number */
CUST_PROMOTION1 varchar (250),  /* a text 250 character description of first Promotion */
CUST_PROMOTION2 varchar (250),  /* a text 250 character description of second Promotion */
CUST_COUPON1 varchar (250),  /* a text 250 character description of first Coupon */
CUST_COUPON2 varchar (250),  /* a text 250 character description of second Coupon */
CUST_MEMBERSHIP  enum ("Gold", "Silver", "Brown" )
/* ..ad more fields if needed ..*/
);  

NOTE: Do not use Auto increment for Primary Key. Just use Int. Because you want to enter different CustomerId in different order.


Picture: Copy the blue code -> Click [Submit SQL Code] -> The empty table with 1 default record.

Thus, now you see, you have extended the original table x_customer to customize your needs without changing its original structure by using One-to-One Relationship.
- So let say you want this customer, in table x_customer, has: Name = John Smith, Email = johnsmith@ibm.com, CustomerID  = 12390. When he logins will see his own promotion and reward, you then do the following:

1) Copy the blue code in myNet.Dbase -> Click on button [Submit SQL code] to create new table =  customer_membership_reward. -> Next, you enter data entry in this table

CustomerID = 12390
Email = johnsmith@ibm.com
CUST_REWARD_POINT = 5000
CUST_PROMOTION1 = Buy 2 T-Shirt will get 1 for FREE
CUST_PROMOTION2 = Shipping Free for all purchase over 500$
CUST_COUPON1  = 5$  Off . Bring this coupon in our store only.
CUST_COUPON2  = x
CUST_MEMBERSHIP  = Gold


Picture: Data entry for customer = 12390

2) Working with Member Collaboration Next, you create a Member Collaboration Action = View, Task name = Check Promotion  mapping in this table. When John Smith logins, clicks on the Member Collaboration left menu = Check Your Promotion. John Smith will see his own targeted promotion based on his email. Use the same steps to create promotion for other members. Thus each member has each own promotion.

NOTE: If you enter another record that has the same CustomerID = 12390 in table = customer_membership_reward for John Smith. It will not allow because CustomerID is a Primary Key for this table. Thus adding another row record with the same CustomerID =12390 will cause duplication. And this table will automatically reject your data entry when you insert. This will ensure the integrity of  relationship One-to-One. Which means that one record of CustomerID=12390 in table = x_customer has only one record of CustomerID=12390 in table customer_membership_reward.

So as you can see, by applying One-to-One Relationship, not only you keep the original database table = x_customer unchanged, but also you expand this table into many more business logistic tables, like customer_membership_reward, that satisfy your business needs without changing or messing the original fields of x_customer table.

Get data and information from multi-related tables

Let say you want to know the customer John Smith, with CustomerID = 93, his First Name, Last Name.. in table = x_customer, and his Customer Reward Point, his Membership  in table =  customer_membership_reward ... So you are looking data from 2 different tables to get his data. You run this script:

SELECT x_customer.fname, x_customer.lname, x_customer.phone, x_customer.address, x_customer.company,
customer_membership_reward.cust_reward_point, customer_membership_reward.cust_membership
 from x_customer, customer_membership_reward
where  x_customer.customerID ='93'  and customer_membership_reward.customerID = '93'

NOTE: x_customer.fname -> x_customer = table name, and fname = field name of fist name -> x_customer.fname means looking for data first name in table x_customer.

Since two tables are related One-to-One, you are for sure that what ever information you get from table = customer_membership_reward, will belong to CustomerID=93 in the other table x_customer because their relationship said so. 

Another application: Use One-to-One Relationship with Member Collaboration in myNet.Office to create powerful customer collaboration tasks

Let's say you want to collect data and information Dealer Ship, Training Seminar, Supply, Extra Addresses... What ever extra data and information you want to collect from your customers, members. And then request them to upload files, Pdf, Spread sheet, Pictures, Videos...
When the customers login, they will enter the required data and information. And then upload requested files. Here are what you will do:

1) Create new One-to-One Relationship Databases: Dealer_info, Seminar_Training_Info, cust_upload_files with database = x_customer. See Table x_customer
Why One-to-One, as we learn because 1 customer has only 1 dealer info or seminar info, or 1 upload files info. So clearly it is One-to-One Relationship with database x_customer.

2) Next, Create Member Collaboration, with Member Collaboration Action = Form, Database = dealer_info. Which means  when member login, will see a task menu = Submit Dealership Info. Data collected from this form will be saved in the database = dealer_info. Based on this concept, you can collect many other data and information that your want members to fill in.

- Next, Create Member Collaboration, with Member Collaboration Action = Upload, Database = cust_upload_files. Which means  when member login, will see a task menu = Upload Requested Documents (See Member Collaboration) . DONE!

NOTE: Do not use Auto increment for Primary Key (Like = int not null auto_increment primary key). Just use Int. Because you want to enter different CustomerId has different number in different order. Such as one CustomerID = 12340, next you enter one CustomerID = 12330.

You see, understand theory of Database Relationship is powerful.
Now you are able to extend the customer database = x_customer to many different small databases that have logistics data related and connected to the database of customers, of members. This gives you the tools you need to collect as much data and information of members, of customers, of people  as possible.
Then using Database Programming Script, you can gather all data and information that no Gui Customized Database Application Software can do this.

Of course, you need smart staffs who can understand the theory and then applied in real business. Or else it will not working.


One-To-Many (the most popular relationship)

Figure 1B shows an example of a one-to-many relationship between DEPARTMENT and PROJECT. In this diagram, DEPARTMENT is considered the parent entity while PROJECT is the child. Reading from left to right, the diagram represents departments may be responsible for many projects. The optionality of the relationship reflects the "business rule" that not all departments in the organization will be responsible for managing projects. Reading from right to left, the diagram tells us that every project must be the responsibility of exactly one department.

This is another example of 1 - to - Many relationship between Customers and Addresses. The primary key for Address table is AddrID. To setup this 1-to-Many relationship, you put CustID, which is the Primary key of table Customers into the relational table Addresses. Formula: Customers (1) -> CustID -> (m) Addresses

Analyse The Relationship of Database Diagram

Picture: Database Relation Diagram. - Most tables in this diagram is One-to-Many Relationship ( 1 - ∞ ). You can build a whole database like in this picture using myNet.Dbase. NOTE: The Products and Orders table relationship is Many-to-Many. WHY? Because in business, we see in an Order, there are many products. And a Product can be in many Orders. As you see, it is deferent from the relationship 1-to-Many between database Customer and Invoice. Because, a customer has many invoices. But an invoice, example I2190, is belong to one and only one customer. If you had an invoice  I2190 belongs to 2 different customers, then you double bills. It can't happens. Your accounting database is wrong. But, because the relationship Many-to-Many is not acceptable in real business world, in database design. Therefore, the table Order Details is the associate table used to break up Many-to-Many into 2 of One-to-Many. That is why you see the associate table Order Details contains 2 primary keys of the break up tables: OrderID of table Orders, and primary key ProductID of table Products. Make it simple, in order to link or connect the 2 database tables Products and OrderID that has the relationship = Many-to-Many,  the the associate table or break up table Order Details is introduced. To design the associate or break up table Order Details, the 2 foreign keys OrderID and ProductID are included in the design of table Order Details. - WHAT HAPPENS IF DON'T BREAK UP THE TABLE RELATIONSHIP Many-to-Many of two database Product and Orders? GARBAGE IN. GARBAGE OUT. Or, hard to get data! Confusion! Lost data  integrity! - There is no 1-to-1 relationship in this Database Diagram.

The Condition of 1-to-Many Relationship:

- The table that is 1, like table [Suppliers], has no PK of table it relates to, the table [Products].
- VS. The table that is Many, like table [Products], has PK of table = SupplierID as its a foreign Key, to table [Suppliers].

Many-To-Many Relationship

Figure 1C shows a many-to-many relationship between EMPLOYEE and PROJECT. An employee may be assigned to many projects; each project must have many employee Note that the association between EMPLOYEE and PROJECT is optional because, at a given time, an employee may not be assigned to a project. However, the relationship between PROJECT and EMPLOYEE is mandatory because a project must have at least two employees assigned. Many-To-Many relationships can be used in the initial drafting of the model but eventually must be transformed into two one-to-many relationships. The transformation is required because many-to-many relationships cannot be represented by the relational model. The process for resolving many-to-many relationships is discussed in the next section.

NOTE: There are many different ideas about Many-to-Many relationship between developers, computer scientists. Some are accepted it is OK to have Many-to-Many. But others don't, and want to break up this Many-to-Many relationship into different 1-to-Many that we are going to learn here.

Resolve Many-To-Many Relationships

Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model. Because it creates redundancy and duplication. John Smith - the CEO, and John Smith - the Employee, missed up identity, could happen if two tables are Many-to-Many relationship.  Therefore, Many-to-Many relationships must be resolved early in the modeling process. The strategy for resolving Many-to-Many relationship is to replace the relationship with an association entity and then relate the two original entities to the association entity. This strategy is demonstrated below Figure 6.1 (a) shows the many-to-many relationship:

Employees may be assigned to many projects.
Each project must have assigned to it more than one employee.

In addition to the implementation problem, this relationship presents other problems. Suppose we wanted to record information about employee assignments such as who assigned them, the start date of the assignment, and the finish date for the assignment. Given the present relationship, these attributes could not be represented in either EMPLOYEE or PROJECT without repeating information. The first step is to convert the relationship assigned to to a new entity we will call ASSIGNMENT. Then the original entities, EMPLOYEE and PROJECT, are related to this new entity preserving the cardinality and optionally of the original relationships. The solution is shown in Figure 1B.

From the above Database Diagram, we resolve the Many-to-Many relation of table  [Products] and table [Orders] by introducing the associate table [Order Details].
The Products and Orders is Many-to-Many. But because the relation Many-to-Many is not optimized. Therefore, we introduce a middle table [Order Details] to break up this relationship in to 2 of 1-to-Many. Thus in table [Order Details], the associate table, must contains 2 PK of the 2 break-up tables.

- [Products] 1-to-Many with [Order Details]
- [Orders]   1-to-Many with [Order Details]
 

Figure 2: Resolution of a Many-To-Many Relationship

Design the Relational database tables Many to Many
CREATE TABLE _acme_employees ( EID int not null auto_increment primary key, NAME varchar(20), PHOTO varchar(20), ADDRESS varchar(200), PHONE varchar(20), EMAIL varchar(50) /* ..more fields ..*/ ); DBR Formula: _acme_employees (1) -> EID -> (m) _acme_assigment Which means: One Employee is Assigned to -> Many Projects.	CREATE TABLE _acme_assigment ( PID_EID int not null auto_increment primary key, EID int, /* Primary key of _acme_employees */ PID int /* Primary of _acme_project */ /* ..more fields ..*/ ); • _acme_assigment is the association table (entity) between Employees and Projects.	CREATE TABLE _acme_project ( PID int not null auto_increment primary key, PRJ_CODE varchar(20), PRJ_COST double, PRJ_FUNDING double, PRJ_DESCRIPTION varchar(20), PROJECT_START_DATE date, PROJECT_END_DATE date, SUBJECT PRJ_TYPE enum ("Tech Support", "Marketing", "Accounting", "Software Developer"), DESCRIPTION text /* ..more fields ..*/ ); DBR Formula: _acme_project (1) -> PID -> (m) _acme_assigment Which means: One Project is Assigned to -> Many Employees.
Another Example Many - to -Many: Introduce a middle (association) table. Then divide Many-to-Many into two of 1-to-Many and 1-to-Many with the associated table. Example: - Students and Teachers are Many-to-Many relation ship. Because a Student can have many Teachers. And a Teacher has many Students. - Invoice and Product are Many-to-Many relationship. Because a Invoice has many ordered Products. And a Product can be on many different Invoices. The Setup: 2 steps: Create the association table _acme_assigment. This table must include EID - the Primary Key of _acme_employees, and PID - the primary key of _acme_project, plus has its own table primary key is: PID_EID.

Easier to understand, a different way of looking at Relational Tables from examples in myNet.Office

• Table Primary Key:  x_customer with Primary Key = CustomerID, and x_invoice has Primary Key = InvoiceID
• Table Relationship:  x_customer and x_invoice = One-to-Many relationship.
- Which means that a customer, example CustomerID=12345, can have many Invoices because this customer has ordered many times. But, an Invoice, example InvoiceID=21900, belongs to one and only one customer. Can't belong to 2 customers.
• How can we do in order to create the 1-to-Many relationship between these two tables?
- We first design table x_customer with Primary Key = CustomerID. Next, We design table x_invoice with Primary Key = InvoiceID + Its foreign Key = CustomerID. Now this 2 table x_customer and x_invoice are relating by the relationship 1-to-Many. So every time a CustomerID = 12345 has ordered an Invoice ID = 21900, in the table x_invoice will contain the Invoice record ID = 21900 that is also included CustomerID = 12345. NOTE: Table x_customer contains no key from table x_invoice.
• How to get data or RDBMS ( Or Relational Database Management System)
- When looking at the InvoiceID=21900, in order to know who is the CustomerID = 12345 that bought this InvoiceID=21900, all we need to do is running this simple database script:
Select * from x_customer where CustomerID='12345'

• Most myNet.Office built-in tables are 1-to-May relational tables, the most table relationship. For example:
- x_invoice is 1-to-Many with x_orderdetail simply by putting Primary Key= InvoiceID of x_invoice in to table x_orderdetail. Which means that an invoice can contain many orders.
- So to find how many orders in InvoiceID = 21900. All you need to do is running this database script in myNet.Dbase.
Select * from x_orderdetail where InvoiceID='21900'
- x_blog is 1-to-Many with x_blogcomment simply by putting Primary Key= BID of x_blog in to table x_blogcomment. Which means that a Blog can contain many user's Comments.

Let's apply Database Relationship in a simple example to build a database in order to collect data and information of Husbands, Wives and Children using myNet.Office

Up to here, you have understood about Database Relationship. Now, let's apply in this example.
Let say you want to collect data and information about: Husbands, Wives, and Children. Here are steps you should do:

1) You need 3 tables ( or smaller databases) called: tbl_husband, tbl_wife, tbl_children.
Ofcouse, the first thing you need is 3 tables in order to store data and information of Husbands, Wives, and Children. So you named them: called: tbl_husband, tbl_wife, tbl_children.

2) Think and analyze about their relationship between Husband and Wives and children.
Which applies that you must analyze the relationship of 3 tables: tbl_husband, tbl_wife, tbl_children. Now let's analyze:
- tbl_husband has One-to-One relationship with  tbl_wife. Why? Because it is always true that a husband, named John Smith has one and only one wife named Maria Smith.
- tbl_children has One-to-Many relationship with  tbl_wife. Why? Because it is true that the wife Maria Smith has at least 1 children. Any her children can't have 2 mothers.
- tbl_children also has One-to-Many relationship with  tbl_husband. Why? Because it is always true that a father has at least 1 children. And a child belong to only one father.
Now, let's get started creating new 3 tables.

3) Design and Create new 3 tables and their Relationship
To create 3 tables, you must pay more attention to table Primary Key, and table Foreign Key. You can add more or delete table Fields later. Nothing will effect your table structure. But if you change table keys, it will effect your database. It doesn't matter any kind of relationship of a table, but it is always true that:
- A table can only has 1 Primary Key and many Foreign Keys.
- A table can only has one and only one record row with a unique Primary Key, for example HWID = 5. There are no second row in table Husbands or Wives that has HWID= 5 in table tbl_husbands, tbl_wives.
 

CREATE TABLE tbl_husbands ( HWID int not null auto_increment primary key, Name varchar(50), Background Enum ( 'White', 'Yellow', 'Black'), Email varchar(50), Age int, Salary double, Appointment Datetime, Hobbies Set ( 'Bowling' ,'Football', 'Baseball'), Register_Date Date, History Text ) - You can add more fields later. - Use Primary Key HWID = Husband Wives ID. You can name Key any name you like.

CREATE TABLE tbl_wives ( HWID int not null auto_increment primary key, Name varchar(50), Email varchar(50), Phone varchar (30), Company varchar (30), Age int, Ethnicity Enum ( 'White', 'Yellow', 'Black', 'Mix' ), Hobbies Set ( 'Music', 'Movies' ,'Shopping',  'Travel'), History Text ) - Since it is 1-to-1 with tbl_husbands, we use the same name primary key = HWID. You can name differently like WID, but use the same name key to make it easy track the relationship 1-to-1 between husband and wife.

CREATE TABLE tbl_children ( CID int not null auto_increment primary key, HWID int, /* Foreign Key */ Name varchar(50), SchoolName varchar(50), Age int, Grade int ) - Since it is 1-to-Many with tbl_husbands, and tbl_wives, we use the primary key = HWID as Foreign Key in this table.

- Data Collection: Choose Data Type for table fields

Data Type is what kind of data you collect for that field? Is it a Integer Number (int), a Text (Text) , a String (Varchar) , a Date (Date)? ...

- Fields names, like Email or email, or EMAIL, and Data Type like Varchar (50) or VARCHAR (50), are not case sensitive. 
- int not null auto_increment primary key = means the primary key is an Integer number, and automatic increment to one when add a new record information. Thus there is no record that has the same primary key HWID. This ensures the uniqueness of a table.
- varchar(50) = Means Data Type of the field = Name, or Email  is a string or words or text. There are 50 characters this field used for this field. Maximum is 255 characters. For example Name = John Smith. Data string John Smith  = 11 characters including space in between. Why don't you use Email varchar (250) because your computer memory, your database will allocate unnecessary space for Email. Thus makes your database fat. Thus slow loading database. No email that is long 250 characters.
- int = Integer Number, e.g.= 218, double = decimal number, e.g.= 3500.36, Date = 2009-05-15 (yyyy-mm-dd) format, Datetime = yyyyy-mm-dd HH:MM:SS
- Enum = Type of data you can choose only 1 option.
- List = Type of data you can choose at least 1 option.
- Text = Type of data can contain a big text like Text Area. Maximum the field HISTORY can store in database is 64000 characters.

Theses Data Type you collect for database Husbands are the most popular used data type.

- Next, Create theses relational tables
You just need: to Copy the blues code and Paste in myNet.Dbase [Code Area] -> Click on button [Submit SQL Code]. Now you have 3 tables.


Picture: Create table tbl_husbands with the first empty default record created.

- Next, to Enter data.
You just click on the record row =1 -> myNet.Dbase automatically creates Data Entry Interface that matches with each Data Type of  each field of the table. For example, field Register_Date is Datetime, it will creates an input box Date time. Or Hobbies = Set, it will creates many check boxes your you to select as many data. You don't need to enter the HWID key = 1 because it is automatically created.


Picture: Auto Input Data Entry is created to enter proper data type.
Buttons: [UPDATE] = update data - [CLEAR] = clear the previous data entry - [INSERT] = Insert a new record. - [FORMAT] = display in 1 or 2 columns.
- To enter the next record, click [CLEAR] button first. -> Enter data in the Nifty blue form. -> Click [INSERT] button.
- These are the most popular Data Type = VARCHAR, ENUM, INTEGER, DOUBLE, DATE, DATETIME, SET, TEXT

- The result after to Enter data.



- We just show you how Table and how Input Data Entry are created in myNet.Office.

Now, let's do few data entry in the tables. It is important or else Garbage In. Garbage Out.
 

tbl_husbands HWID =1, Name  = John Smith Background = Black, Email = JohnSmith@yahoo.com Age = 38 Salary = 35900 Appointment = 2009-05-30 23:50:55 Hobbies = football, baseball Register_Date = 2009-05-30 History = Saw Mr. John Smith in the Office for ...   NOTE: John Smith has 3 kids	tbl_wives HWID = 1 Name = Maria Smith Email  = MariaSmith@hotmail.com Phone = 1-800-213-1900 Company = Self-Employed Age = 35 Ethnicity = white, Hobbies = Music, Movies History = What ever HWID must enter = 1 because Maria Smith has the relation with John Smith 1-to-1. And John Smith has ID key HWID = 1.	tbl_children CID = 3 HWID = 1 Name = Peter Smith SchoolName = Junior High Age  = 17 Grade = 11 -------------------- CID = 7 HWID = 1 Name = Ken Smith SchoolName = ABC High Age  = 16 Grade = 10 -------------------------- CID = 8 HWID = 1 Name = Tob Smith SchoolName = SDW High Age  = 15 Grade = 9 - CID = 3 because in the table tbl_children, Peter Smith is in record 3. *** The foreign key HWID must enter = 1 because his relationship with Mr. John Smith. *** - From HWID = 1, you can find all information of his father, John Smith, his mother Maria Smith.
Let say in record row = 15 in tbl_husbands HWID =15, Name  = Mike  P Background = White, Email = MikeP@hrabc.com Age = 41 Salary = 50000 Appointment = 2009-06-10 9:30:00 Hobbies =  baseball Register_Date = 2009-03-10 History = Having problems with ...	HWID = 15 Name = Dian P Email  = x Phone = 1-888-233-5900 Company = Hello Inc Age = 31 Ethnicity = Yellow, Hobbies = Travel, Movies History = what ever you enter here... - What ever you enter, just make sure to enter the key right. If husband has key HWID = 15 the the wife must has the same HWID = 15 with her husband.	CID = 4 HWID = 15 Name = Henry P SchoolName = York High School Age  = 16 Grade = 10   - What ever you enter, just make sure to enter the key right.

Picture: Create a new table = tbl_children. Enter data entry  6 records. HWID is Foreign Keys. You can have many Foreign Keys duplications in a table, like HWID =5,  1. But you can only have a unique Primary Key = CID. For example, you can't have 2 record rows that have the same Primary Key CID = 4. Ofcouse, you can delete a record row = 5.
- In the table tbl_children, Peter Smith, Tob Smith and Ken Smith are children of HWID = 1. Bam Ng and Maria Ng have the same parent HWID = 5.
- Let's find out who are Father and Mother of Peter Smith, whose HWID = 1.

Data Entry 5 records in table Wives


Picture: Table Wives = tbl_wives: 1-to-1 with tbl_husbands. You can enter 10000 records of wives if you want. It loads only 10  recs or whatever you specify.
- If you enter another record that has key HWID=3 in table wives. And then insert. The table tbl_wives will reject this record because this means Bog Ing has 2 wives.
More over, a database table will not allow a duplication row that has the same key in order to ensure its Primary Key's uniqueness.

Data Entry 5 records in table Husbands


Picture: Table Husbands  = tbl_husbands: 1-to-1 with tbl_wives. The Primary Key = HWID of each table matches husbands and wives.
- If in the table husbands, Bob Ing, primary key HWID = 3 of row 3. Then in the table wives, row 3, key =3, must be Bob's wife = Nance Ing. Data entry is important. 

Finally, How to look for information in Multi Related Tables

Let's say you want to know who is the Father's Name.., and the Mother's Phone, Ethnicity, Children ... of John Smith All you need to do is:

SELECT tbl_husbands.HWID, tbl_husbands.Name as Father_Name, tbl_husbands.Background, tbl_husbands.Appointment as Father_Appointment,
tbl_wives.Name as Mother_Name, tbl_wives.Ethnicity as Mother_Ethnicity, tbl_wives.Phone As Mother_Phone_Contact,
tbl_children.Name as Child_Name, tbl_children.Grade, tbl_children.SchoolName, tbl_children.hwid
FROM tbl_husbands, tbl_wives, tbl_children
WHERE tbl_husbands.HWID = 1 And tbl_children.hwid =1
GROUP BY tbl_children.cid


Picture: It looks into 3 multi-related tables to get the right data and information based on Key HWID = 1. It found 3 children of John Smith.

- You can always add more fields in the table for your business needs. BUT DON'T CHANGE KEYS
- Ask question: If without built-in Relational Database Feature, HOW CAN I GET ALL DATA?
- Answer: It is really hard for your database software application. It will not handle complex tasks of processing data and information.

Congratulations! You have just created the multi -related table Relationship 1-to-1 and 1-to-Many !

This is like HELLO WORLD simple example in the beginning. Now, having idea how it works, you will apply the more complex relational tables like in the Diagram.

Let's Get Started Designing Company Database in this diagram using myNet.Dbase
Use this as an example for future design more complex company web database

Let's get started building database. Here are 5 steps that we recommend for users, who have no database administration skills.
The requirement to do these steps is: To understand How to CREATE a Table using simple SQL script.

What tables do you need? Understand its use. List them down.
Of course you know why you need them. You always can expand, add more tables in the future. At the present, you need tables for Employees, Project, Department, Workoffice, Customers, Invoices, Products.
 

Table Description: Table Suppliers: is a small database used to store all information about your suppliers. The fields are easy to understand. Example: - Company Name: Name of the Supply. - It is 1-to-Many relationship with table Products, because a Supplier will supply many Products. And a Product is supplied by one Supply. Table Categories: is a small database used to store the Categories of your products. This table similar to myNet.Office Category table. When creating, do not use same name. - It is 1-to-Many relationship with Products, because 1Product Category contains many Products. And 1 Product belongs only 1 Category. Table Products: is a small database used to store all information of your products. This table is similar to table Catalog. - It is 1-to-Many relationship with Products, because 1Product Category contains many Products. And 1 Product belongs only 1 Category.  Table Customers: is a table used to store all customer info. It is 1-to-Many relationship with table Orders. Which means a Customer order at least 1 Order, but in any Order belongs to a Customer only. ( Similar 1-to-Many between x_customer and x_invoice in myNet.Office)
Table Employees: is a table used to store all employees info.	It is 1-to-Many relationship with table Orders. Which means 1 Employee takes care of many Orders, but in any Order is taken care by an Employee.
Table Order Details: is a small database used to store UnitPrice, Quantity, Discount.	It is an associate table to break up Many-to-Many relationship of table Products and Orders.
- ... To be continued later ... -

 CREATE TABLE simple syntax first plus CREATE the PRIMARY KEY for each table .
Use an ID number by increment is the easiest way to make a key, because it is unique and automatic every time you add a new record.  Don't worry about what table attributes (or fields, columns) you add them.. later. The write the SQL Syntax English-like expression like this: Create Table <tablename> ( Key_name type );  (see the table). In this step, you begin to write SQL table expression in the definition table below.
• How To Choose a Primary Key
In this case, it is chosen as: int not null auto_increment primary key means int = Integer type, not null = Key should always has value, auto_increment means that every time you add a new record, the number key will automatically increases to plus 1, and it is chosen as a primary Key. Thus it is unique and simple. If you make up an integer and assigned to the key, you can't keep track which number has been used. If you use Name ( even many experienced db designers used to ) as a key. Then  it is possible you miss up the identity of  John Smith - the CEO, and John Smith - the Employee. So you should choose Primary Key as a Number referred to a String.
NOTE: Database programmers like to choose Name in their database as you often see because the customer Name John Smith is better reference than put CustomerID of John Smith, which is 543325. So that users can easily understand and refer. But if it is designed relational database, then just one click on CustomerID, you can find out much more information about that customer. Not only customer Name, but also that customer's Address, Phone, Company Name...
 

CREATE TABLE suppliers ( SupplierID int not null Auto_Increment Primary Key, CompanyName varchar(50), ContactName varchar(40), ContactTitle varchar(50), Address varchar(200), City varchar(20), Region varchar(20), PostalCode varchar(20), Country varchar(20), Phone varchar(30), Fax varchar(30), HomePage varchar(20), Supply_Categories Set ( 'Software', 'Hardware' 'Network Server ', 'Food', 'Cryptocard', 'Ebusiness Services')  /*add extra field*/ ) • Relation: 1-to-Many with Products. Why? Its primary key SupplierID is in table Product. • To create this table: Copy the blue code -> Paste in myNet.Dbase Code Area -> Click on [Submit SQL Code] button. Then begin enter input data in this table.	CREATE TABLE products ( ProductID int not null Auto_Increment Primary Key, ProductName varchar(50), SupplierID int, CategoryID int, QuantityPerUnit double, UnitPrice double, UnitsInStock int, UnitsOnOrder int, ReorderLevel int, Discontinued Enum ( 'yes', 'no', 'pending' ) /*add extra field*/ ) • Relation: 1-to-Many with table order_details. ProductID: is called Primary Key. SupplierID: is called Foreign Key. CategoryID: is called Foreign Key.	CREATE TABLE order_details ( OrderDetailID int not null Auto_Increment Primary Key, OrderID int , ProductID int, UnitPrice double, Quantity int, Discount double ) - This is called Associate table, or Break-up table used to break up Many - to -Many Relationship of 2 tables: products and orders. - This table doesn't have primary key, so you can add OrderDetailID as its primary key. In the Relationship Diagram doesn't show. But for safe, we recommend you add a Primary Key = OrderDetailID for this table. - Use JOIN syntax on the break-up table in order to find information of the other two tables.	CREATE TABLE orders ( OrderID int not null Auto_Increment Primary Key, CustomerID int, EmployeeID int, OrderDate date, RequireDate date, ShippedDate date, ShipVia varchar(30), Freight int, ShipName varchar (50), ShipAddress varchar (50), ShipCity varchar (50), ShipRegion varchar (50), ShipPostalCode varchar (10), ShipCountry varchar (50) ) • Relation: 1-to-Many with table order_details.
CREATE TABLE customers ( CustomerID int not null Auto_Increment Primary Key, CompanyName varchar(50), ContactName varchar(40), ContactTitle varchar(20), Address varchar(200), City varchar(20), Region varchar(20), PostalCode varchar(50), Country varchar(20), Phone varchar(30), Fax varchar(30) ) • Relation: 1-to-Many with table orders.	CREATE TABLE employees ( EmployeeID int not null Auto_Increment Primary Key, LastName varchar(50), FirstName varchar(40), Title varchar(30), TitleOfCourtesy varchar(30), BirthDate date, HireDate date, Address varchar(200), City varchar(20), Region varchar(20), PostalCode varchar(50), Country varchar(20), HomePhone varchar(30), Extension varchar(10), Photo varchar(30), Notes varchar(250), ReportsTo varchar(30) ) • Relation: 1-to-Many with table orders. • Relation: 1-to-1 with table workoffice	CREATE TABLE categories ( CategoryID int not null Auto_Increment Primary Key, CategoryName varchar(40), Description text(40), Picture varchar ( 200) ) • Relation: 1-to-Many with table products. Which means 1 Category has many Products	CREATE TABLE  workoffice ( EmployeeID  int not null auto_increment primary key, /* Primary Key  of workoffice*/ OfficeUserName varchar (50), OfficeType List ("Tech support", "Marketing", "Accounting", "Software Developer", "Manager" ), OfficeDepartment enum ("Tech Dep", "Marketing Dept", "Accounting Dep", "IT Dept", "Manager" )  /* ..can add more fields ..*/ ) • Relation: 1-to-1 with table employees because each employee can have only 1 work space. Therefore, we put the primary key of employees into table workoffice. • Relation: 1-to-1 is often used to extend the original table employees, because you don't want to make employees long and big table. NOTE: This table is not in diagram. We introduce so that you can see all 3 Database Relationship.

• To create these table: Just copy the blue code -> Paste in myNet.Dbase -> [Code Area] -> Click on [Submit SQL Code] button. Then begin enter input data in this table.

• myNet.Dbase lets you add more fields to expand these tables...It is OK to add more fields when business needs more fields...BUT...

• Do not change the keys because the keys are creating the Table Relationship. If you change the keys you change the Table Relationship.

• After creating tables, next, begin Data Entry Input.. myNet.Dbase like Smart Form will automatically create a Input Data Entry for each table so that you can input data in. It knows how to create screen interface Input Data Entry for different data type like List, Enum, Varchar, Int, Double, Date, Datetime...

• The Relational Diagram Tool lets you see the table relations easily. But the one who design the table relation is YOU. The diagram doesn't know what relation for the table customer, or between tbl_husband and tbl_wives. But you know. The diagram only re-constructs the relationship by looking at the keys in tables.

Can you have Multi Table Relationship?
It happens when a table, for example table employees, has 1-to-One with another table xyz. And then table employees has 1-to-Many with another table as well.

Create more Relational Tables to expand your company database system to department, project, assignment, workoffice, employees...

In these database script code, just look at the Table Primary and Foreign Keys that are creating the Relationship. The fields are not important. You can add more fields later. When your company database begin growing, hard to keep track the relationship, then you should use some diagram tool like the diagram in picture.
 

Table Primary	Relationship and Key	Table Relation	Analyze
CREATE TABLE customers ( CustomerID int not null auto_increment primary key, /* more fields later*/ );	1-to-Many customers  (1) -> CustomerID  -> (m) invoices	CREATE TABLE invoices ( InvoiceID int not null auto_increment primary key, CustomerID int, /* more fields later*/ );	- One Customer has purchased many Invoices. But One Invoice has only one Customer. • How? Just use common sense right? So this is:  One-to-Many Relationship. - Who is One: customers - Who is Many: invoices
CREATE TABLE employees ( EmployeeID int not null auto_increment primary key, /* more fields later*/ );	1-to-Many employees  (1) -> EmployeeID  -> (m) assignment	CREATE TABLE assignment ( PID_EID int not null auto_increment primary key, EmployeeID int, /* PK of employees */ PID int /* Primary of project */ /* Expand table add more fields... */ );	To divide Many-to-Many relationship of Employees and Projects, we introduce association table assignment in order to create two of 1-to-Many relations. assignment has 2 foreign keys EmployeeID and PID includes.
CREATE TABLE projects ( PID int not null auto_increment primary key, /* more fields later*/ );	1-to-Many projects  (1) -> PID  -> (m) _acme_assigment	CREATE TABLE assignment ( PID_EmployeeID int not null auto_increment primary key, EmployeeID int, /* PK of employees  */ PID int /* Primary of project */ /* Add more fields... */ );	projects is relating to employees with Many-to-Many. So table assignment is introduced in order to divide the relationship Many-to-Many into 1-to-Many with assignment
CREATE TABLE department ( DID int not null auto_increment primary key, DEP_NAME enum ("Engineering", "Human Resources", "Marketing", "Accounting", "Software Development") /* Expand table add more fields... */ );	1-to-Many department  (1) -> DID  -> (m) projects	CREATE TABLE projects ( PID int not null auto_increment primary key, DID int, /* PK of _acme_department  */ /* Add more fields... */ );	• One Department has managed many Projects. But one Project belongs to only one Department. Thus it is 1-to-Many.
CREATE TABLE workoffice   ( EmployeeID int not null auto_increment primary key, /* used same key with employees*/ /* more fields later*/ );	1-to-1	CREATE TABLE employees ( EmployeeID int not null auto_increment primary key, /* more fields later*/ )	• One work office (computer used, or office, or a cubic) has occupied by only one Employee. And one Employee occupied 1 office.

Finally, Fill in Fields (or Columns) for the rest of all Tables. myNet.Dbase allows you to add more fields (or columns) or delete them. What ever you do, please
DO NOT CHANGE THE PRIMARY KEY AND THE FOREIGN KEY.

Next, how to get data and information from Multi Related Tables you just created in Relational Database?

- Unfortunately, to get data and information in Relational Database is NOT EASY. You have no choice but you have to use Database Script Language. There is no Window Gui, no Screens, no Interfaces that can help you get data in Multi Complex Related Tables easily. You can learn Database Scripting Language here LEARN DATABASE SCRIPTING LANGUAGE. It is easy, English-like syntax to learn. Matter of fact, it is easy to learn than to learn many Window Gui steps.  Therefore, we can say that: If your software application doesn't allow you to run Database Script then your software application is not Relational Database or RDBMS.

- This Database Script looks up the Invoice database = x_invoice, and displays a report of Member Dave. Click on button [Submit SQL Code]
SELECT customerid, company, count(invoiceid), saleperson, phone, sum(sub_total), min(sub_total), max(sub_total), sum(tax1_gst_amt), sum(tax2_pst_amt), tax2_pst_exempt from x_invoice where customerid = '93' group by customerid


Picture: Calculate Tax, Sub Total, number Invoices... of CustomerID =93. You also can calculate in the period of invoice date as well.

Have Relationship with myNet.Office.



How to recognize a computer database system or a software application is a Relational Database System.

After you have ideas about what is a Relational Database, it is not difficult to know my database program is a relational database. At work, you probably run application like Customer-Invoice software, BV= Business Vision, ACT, or HR program, or Company database program. Let say you are running Customer Invoice program. When there is a time you see a customer record that has an Invoice ID = 129033. In order to find more information about this InvoiceID, such as: the Date, Amount, Total, Pst, Gst, the Address Delivery, Time Delivery, What Orders on it. See the above picture and the code design? ...  And you have to go through many steps to piece them together then probably your database application is not built-in Relational Database, or the dbase designer didn't design the software probably. And then you have to spend hours and hours to look for information. This means your work productivities affected. Thus cost your business more. If that database is built in relational database. Then just one click you can gather a whole View of Information of all its relations.

• Any Software Applications, Online Application, Web Based Software Applications that you can't run Database Script SQL are example of Not Relational Database Software or Is Not Built For Relation Database Management System (RDBMS)
• If your Computer System can't run Database Script SQL then it is  Not Relational Database Computer System or Is Not Built For Relation Database Management System (RDBMS)

WHY? Because, as we said, to get data from multi-related tables are not easy, you need to use Database Script Language. There are no Windows Gui Interfaces, no Filters that can help you get data from multi-related tables in Relational Database (RDBMS). The only solution is Database Script Language. 

*** Important *** Think about this Database Script Feature before you choose your web based software applications for your online business. It is not kidding.
ORACLE is Relational Database Management System. But it is too expensive, hard to maintain, need to hire DB Administrator. ORACLE is used by BIG Businesses and BIG Organizations.

You should choose Web Based Software Application, any Web Site Builders that have built in the powerful Database Programming Script, that let you run database programming script. Because all the Net, every thing is about Data. Without this Database Programming Script, it is hard to get data when business grows bigger like B2B. There are no interface, no Gui, no screen that can be design to get complex data if compared to Database Programming Script. It is quick and fast.

Summary

• The Entity-Relationship diagram provides a pictorial representation of the major data objects, the entities, and the relationships between them. Once the basic diagram is completed, the next step is Design Tables that represented by these relationship, and Code Implementation.
• We are living the the world governed by data and information. Database is the most important property of your business. Business can't run without them. Garbage in. Garbage out. Therefore, if you are in the position of Management, or dbase Designer, you should take it seriously.
• In the past, you get used with software designed to get data from Gui interfaces. Time to learn Database Programming Script that lets you get data, manage your database system. It is fun! You can learn Database Scripting Language here LEARN DATABASE SCRIPTING LANGUAGE. It is easy, English-like syntax to learn. Matter of fact, it is easy to learn than to learn many Window Gui steps.
 
Additional Resources:

We hope this article will give you some information about the background, the design, the code, and how to do with relational database. You can find more information from these links.
- The Relational Database Design ( Computer Scientist Level - Some background theory of Relational Algebra and Relational Calculus )
- IBM Database Relationship. (Developer Level)
- Design Your Own Database  from Dr Lorna Scammell, University Database adviser, Newcastle University. (Basic Level)
- Database Journal for Developer - Introduction to Database Relationship (Developer Level)