Hi guys!
I have already read this: Planning Your Database Model | Help Center | Wix.com
Do you have any source (to learn (video, course etc.)) to provide me. I need to learn how to plan a database correctly in Wix while creating custom apps. Because sometimes I’m having very different problems with databases and you can also tell me your suggestions about how can I learn it and what should I do about it.
Hey @loeix ,
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)
Don’t hesitate to share your finding with us!
@quentin I’m very very new about databases I just started to watching this long video on YouTube in the beginning of the video he says you can use these information’s anywhere here is the video :
I want to learn how should I design my database before start coding and developing my app. I didn’t even know there was database styles :D. I’m just trying to find a video that can be helpful for my future projects.
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:
Thank you so much @quentin Can I understand (with these links) what is the document 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.
@loeix Yes I believe so. Start with the video that’s a good introduction.
Basically you need to design your database to answer specific question. So before building the database you need to know what request you’ll need to answer then build the database to best response to these request