Database_Structures_&_Operations

Database Table Structure

As mentioned, a database table is structured like a spreadsheet, with column names (these are the Elements in your form) and rows (these are the individual records submitted via your form). As your operation increases in size or complexity, you will find that you will want to cross reference data from different tables, and that there are columns that repeat in separate database tables.

Database Operations

When this happens, the solution you will come across is given the term 'database join' - this is both an action (ie the searching for sets of related data) and a result (ie the data that is then outputted).

A good explanation of this term is given here:

http://www.w3schools.com/sql/sql_join.asp

and here:

http://en.wikipedia.org/wiki/Join_(SQL)

So going back to the example of a user profile form, where a 'userid' element has been created for that form (see the aforementioned tutorial on this), if we have created other Forms associated with other database tables that also have their own 'userid' element, then the common element or column in both tables is 'userid'.

Remember at this point, that each table has its own 'primary key', the column where each corresponding row must have a unique value.

[EXAMPLE NEEDED]

In both instances, there could only be one record of that specific 'xxxxx' or 'yyyyy'.

In other words, a primary key defines the basis for which a record is unique and ensures that each instance of a record cannot be repeated.

There are four types of database joins (these are presented in UPPERCASE as this is the syntax used when running the SQL query ie the request to get related data) :

JOIN: Return rows when there is at least one match in both tables

A join (also known as an 'inner join') returns all rows where there is a match in both tables, for example if the user profile contains a 'Favourite Recipe' field where the 'Recipe Name' is the column name, then the common column for both is 'Recipe Name'. Where a user like a 'Recipe Name', the database join would return all users that liked 'Pumpkin Pie'. (?)

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

A left join (also known as 'left outer join')...

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

A right join (also known as 'right outer join')...

FULL JOIN: Return rows when there is a match in one of the tables

A full join...

• Fabrik join features
• Design strategy

<hr/>

Next: Maintaining Fabrik