Planning a Database

From my experience, the most important thing for you to understand fully, and know the implications of, is how to model relations in Wix's document model. If you are coming from something like a MySQL background, you will understand relational database concepts. Wix has some limitations in this area that you should be aware of. 1:N relationships - Wix allows you to model 1:N relationships using Single Reference Fields (SingleRefs for short).

Limitations of SingleRefs: In a SingleRef, the relationship is only visible on the 1 side. It is invisible to the N side. For example, in my data model (for a plant nursery), plants have a classification. Classification Collection Perennial Annual Tree Plant Collection Plant name: California Poppy ...other fields... Classification -> ClassificationCollection.Annual

Plant name: Clarkia ...other fields... Classification -> ClassificationCollection.Annual In this case, Plant knows its classification. Classifications do not know which plants have a particular classification.

In my example, you cannot ask the Classification table "give me all plants that are annuals". You have to, instead, ask the Plant collection "filter by classification".

M:N relationships - Wix allows you to model M:N relationships using Multiple Reference Fields (MultiRefs for short). This is where Wix's limitations really get in the way. It is VERY difficult to model M:N relationships in Wix in a way that actually functions. In my example above: Color Collection:

Orange

PlantIDs -> [111]

White

PlantIDs -> [111, 222] Pink

PlantIDs -> [222]

Plant Collection

ID: 111 Plant name: California Poppy Color -> ColorCollection.Orange, ColorCollection.White ID: 222 Plant name: Clarkia Color -> ColorCollection.Pink, ColorCollection.White The relationship is visible on BOTH sides, which is great. It is very easy to query either side of the relationship.

i.e. I can ask the Color Collection "give me all the pink plants". I can also ask the Plant Collection "filter by color Pink" (If I were speaking SQL, I'm talking about the WHERE clause). The first limitation of MultiRefs is around how to RETURN them from a query. (If I were speaking SQL, I'm talking about the SELECT clause). It is basically impossible to return the data within a MultiRef field. There are 2 ways to get around it. Both are incomplete and non-functional in different ways (and I won't go into it here. Wix has an article about it if you're curious). The result of this is that you CANNOT put values from a Multi Referenced Field into a repeater for the Referencing table. In my example, I want to surface information about Plants and display that information in a repeater.

The repeater cannot contain the color information for a plant.

(This is not strictly true. You can actually return the color information, but in a way that has such horrible performance implications that you really shouldn't do it unless your query only returns like...10 rows at max and you're really adept at populating repeater fields manually, asynchronously...)

The second limitation of MultiRefs is that there are no hooks that will detect an update to any MultiReference field. In my example, if I update a Plant to add/remove a color, and I need to do ANYTHING in a database hook for that plant upon the update of Color, I cannot. MultiRef changes are 100% invisible.

If I had to do it again, I would think LONG AND HARD before using MultiRef fields. I am using them as ENUMS (to restrict the legal values of a field so that someone can't set a Plant's Color to: GIRAFFES AND ELEPHANTS!). Instead, I would probably have a Color Collection, but instead of using a MultiRef on the Plant collection, I'd just use an Array. It's like flattening out the MultiRef data. This comes with its own set of headaches, though, which I won't get into here either.

I wish I had a better answer for you about how to model M:N relationships. Wix just does not give you one.

Ok so that video is nice to understand the basics of how databases works , but he explained with a relational database. Those concepts cannot be applied to Wix which is a document database here are some links i found via a quick search:

1. mongodb.com/developer/article/mongodb-schema-design-best-practices/

2. https://docs.mongodb.com/manual/core/data-model-design/

3. https://www.youtube.com/watch?v=QAqK-R9HUhc

Hey @ExWeiv,

Wix used a document database system so look online for any information about structuring database in MongoDB or any other document database system. That should be similar. (even though Wix does not have all the capabilities of MongoDB good practice should be the same)