The Wix Code Database and Data Modeling

Original Source: https://www.sitepoint.com/the-wix-code-database-and-data-modeling/

This article was created in partnership with Wix. Thank you for supporting the partners who make SitePoint possible.

One of the cool features of Wix Code is the ability to separate your site’s design and layout from its content. This means you can create and maintain your information in a database and then have your pages dynamically retrieve and display this information in whatever way you like.

Let’s take an in-depth look at what you can do with the Wix Code database, including the types of information you can store, ways you can manipulate data with code, and how you can dynamically display the information on your site.

Throughout this article, we’ll use a simplified example of an art school that stores and displays information about its courses and teachers.

The Wix Code Database

Like all databases, the Wix Code database is made up of individual tables, which we call collections. In our example of the art school (see image below), we have two collections, one each for the courses and teachers.

You can create as many collections as you need and populate them with a near unending amount of data. A robust permissions model means you have complete control over who can access your information and what they can do with it.

You can work directly in your Live data, which is the information your visitors see when they view your pages. You can also work with Sandbox data, so you can try stuff out without affecting your live site. You can sync between them at any time.

Populating Collections

You have several options for populating your collections. You can manually enter data directly in the Wix Content Manager, either to your Live data or your Sandbox data.

If you’re an Excel ace, you can do all the work in Excel (or whatever spreadsheet program you prefer), save your sheet as a CSV file, and then import it into the Wix Code database. In fact, you can create your entire collection this way, schema and all. You can import to your Live data or your Sandbox data.

You can also export your Wix data to CSV files. If you make sure to include the built-in ID system field, you will be able to modify your content in your spreadsheet and then re-import it into your Wix Code database so that each record, or what we call item, is updated.

A third option is to build a form to capture user input and store it in your database.

Using External Databases

If you already have a database somewhere, you might be thinking that you don’t want to recreate it in Wix. The good news is that you don’t have to. As long as your database exposes an API, you can access it from your Wix site.

For simple applications, you can use the wix-fetch module—an implementation of the standard JavaScript Fetch API—to access your external database with an HTTP request and use that data in your Wix site’s pages.

You can also pair the wix-fetch module with another Wix module, wix-router, that lets you control the routing of incoming requests. Using the functionality provided by both of these modules, you can create SEO-friendly dynamic pages that show different data depending on the URLs used to reach them.

For example, you can design a single member profile page that can be used by all of your site’s members.

Using wix-router and wix-fetch you can write code that pulls information from incoming requests for the profile page, queries an external database to retrieve the information for the page, and then injects that data into the profile page. You can even add security to your page by using the wix-users module.

So if you create another page for users to update their profile pages, you can check who is trying to access it and only allow users to update their own profiles.

Data Hooks

You can add hooks to actions on your collections using the wix-data API.

For example, in our Teachers collection, we have two separate fields: First name and Last name. To make displaying names on our pages easier, we also want to have one field that has both names together. To do this, we can add a beforeInsert hook to our Teachers collection that hooks into the insert action, reads the contents of the First name and Last name fields, and then concatenates them and populates the Full name field.

Modeling Your Data

Now that we’ve covered the database itself, let’s talk about modeling your data in the Wix Code database.

Collection Schemas

Like all databases, each collection has a schema to define its fields. All standard field types are supported, including text, image, boolean, number, date and time, and rich text.

There is also a field type specifically designed for URLs. It automatically formats the URL into clickable links that you can add to your pages. For example, teachers in your school could supply the URL of their portfolio website, and you could include that link on their dynamic page.

You can also use the document field type to store a wide range of file types. You can allow your users to download files stored in your collections (such as reading lists for each course) or to upload their own files.

ID Fields and Primary Fields

Each collection has an _ID field, which is the primary key for that table. Collections also have a primary field (indicated by a lock icon), which is the display key for each item.

When you create joins using reference fields (see the next section), the values come from the primary field. The reference itself uses the _ID field, of course. If you plan on using reference fields, it’s a good idea to make sure the data you store in the primary field is unique.

Reference Fields

Reference fields create a connection between collections that is defined in the collection schema itself. This is similar to foreign keys in relational databases.

Each reference field points to a specific collection. The value that is displayed in the reference field in each item in the collection is taken from the value of the primary field of the referenced collection.

In our example, we created a reference field in our Courses collection that points to our Teachers collection so that we can indicate who teaches each class.

The advantage of reference fields is three-fold. First, they help maintain data integrity because their value is taken directly from the referenced collection. Second, they help eliminate data duplication, which we all know is the enemy of good database design. And third, when we create our page layouts, reference fields let us access information in the referenced collection as well as in the main collection we are using. This allows us to create master-detail pages, such as a list of all the courses taught by each teacher.

Creating Pages from Your Content

Of course, storing and maintaining data is nice, but the real point of having a website is displaying content to visitors. So let’s talk about how that works with Wix Code.

Back to our art school example. We have two different types of information: courses and teachers. So you could start by designing a page layout to display all the information about each of the courses. Then you might want to create a master-detail page that lists all of your teachers and the courses they teach.

Continue reading %The Wix Code Database and Data Modeling%