What is a Database?

  • Views Views: 10,238
  • Last updated Last updated:
Page

  • Contents

    This wiki entry is designed to explain the basics of SQL databases for those who have not come across them before.

    Database Tables​

    The basic building block of an SQL database is a Table.

    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).

    Primary Keys​

    Each table should have its own 'Primary Key', a column (or sometimes a set of columns) for which every row of the table must have a unique value. Often you use a unique ID number for each record, and SQL provides an automatic incrementing number field, which Fabrik creates by default for each List using an internalID element.

    Table Joins​

    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. 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).

    Good explanations of database joins can be found at:
    So going back to the example of a user profile form, where a 'userid' element has been created for that form (see the Fabrik Parameters), 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'.

    So you might JOIN a customer and an order table as follows:
    SQL: 
    SELECT Customer.Name, Orders.Amount
    
FROM Customer
    
JOIN Orders
    
ON Customer.ID = Orders.Customer_ID;

    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
    • LEFT JOIN
    • RIGHT JOIN
    • OUTER JOIN
    [Note: Venn diagrams could be useful here.]

    JOIN​
    A plain 'JOIN' (also known as an 'INNER JOIN') will return rows only when there are rows in both tables with matching join fields. So in the above example, you would get records only for customers who have at least one order, and only for orders which are associated with a customer.

    Customers with multiple orders will have multiple rows. Customers with no orders, and orders without an associated customer will not appear in the results.

    LEFT JOIN​
    A 'LEFT JOIN' (also known as a 'LEFT OUTER JOIN') returns all rows in the first table, together with any matching rows in the second table. In the above example, this would return all customers, and any orders associated with these customers.

    Customers with one or more orders will appear as before. But this time, customers with no orders will have a single row and the order columns will be blank. Orders not associated with a customer will still not be shown.

    RIGHT JOIN​
    A 'RIGHT JOIN' (also known as a 'RIGHT OUTER JOIN') returns all rows in the second table, together with any matching rows in the first table. In the above example, this would return all orders, and any customers associated with these orders.

    Customers with one or more orders will appear as before. But this time, orders not associated with a customer will have a single row and the customer columns will be blank. Customers without any orders will not be shown.

    OUTER JOIN​

    An 'OUTER JOIN' (also known as a 'FULL JOIN') returns all rows in the both tables. In the above example, this would return all customers and all orders, regardless of whether a customer has any orders or whether an order is associated with a customer.

    Customers with one or more orders will appear as before. Customers with no orders will have a single row and the order columns will be blank. Orders not associated with a customer will have a single row and the customer columns will be blank.

    See also ...​

Back
Top