Data Structure from Excel

Need help
1

I am trying to understand the Data Structure of Bubble. I understand that it can be very flexible but I have some specific questions on things I haven’t grasped fully yet.

I am making a database of all my art. I have paintings, sculptures, sketches… all physical objects.

So DATA TYPE is OBJECT (1)
The FIELD will be the second column = Object Type, Title, Medium, Size etc.
The FIELD TYPE will be text or numerical etc.

But this is where my first problem is: Do I need to create a field “Title” as a text field type AND then a field “Title NAME” as a text field type LIST OF ELEMENTS ? In the walkthroughs we seem to be taught to create BOTH, but I don’t understand why, and what the real difference is.

Here is my Google Sheet (Excel):

Screenshot of Copy of Oeuvre Tables and Fields - Google Sheets (1).png1152×789 78.7 KB

  1. Some OBJECTS will be films, with a duration of minutes and hours. What FIELD TYPE should I choose?

  2. The OBJECT will be in a physical location, eg. my studio, a gallery, or sold to a collector. But also, the PHOTO OF OBJECT will be at a physical location. In a relational database, I would have LOCATION as a separate DATA TYPE. Do I do that here, and then link it to the OBJECT?

  3. Many of the OBJECTS are related to one another. For example, sketches (OBJECT) for a sculpture (OBJECT), or painting (OBJ) in a SERIES (OBJ). Is RELATIONSHIP another OBJECT?

  4. A PHOTO OF OBJECT could be taken at an EXHIBITION. (These are both DATA TYPES, I think). Can a DATA TYPE be an ELEMENT on a LIST of a FIELD of a different DATA TYPE?

Thanks for any help and advice.

2

I will have another look later to see if I can answer your questions, but I think it would be worth thinking about your data in another way.

So…rather than making it data-centric, make it query-centric. Instead of “what answers do I have” it is “what questions do I have”. What are the scenarios for how you are pulling this out ? That tends to drive non-relational database design.

One of the things that is very different is the idea of denormalisation, i.e. that redundant data is not bad. If you are familiar with relational database design, then this is (almost) entirely opposite to the way you think.

Bubble can link things in several ways, but the simplest is just to embed one this in another. This tends to be how you do the “one to many” situation.

So instead of having an “Art Object” and “Photo Of Object” and linking the two with a key … you put the Photo inside the Art Object thing.

So our art object might look like…

{
type : Painting
title : The Man
medium : oil on canvas

photo {
image : //filepath/filename etc
view : front
location : studio}

photo {
image : //filepath/filename etc
view : rear
location : gallery}

date : 1/1/2016
etc
}

So here we have two “things”. The Object thing and the Photo thing.

In Bubble this means a field with type Photo which is a List. You don’t need to worry about ids and relationships, because you embed the photo in the object it relates to, in the same way you might embed an excel in a word document. The relationship is explicit in the document, not implicit in a field in the file.

You can of course embed Object things in other Objects. So this would be a field of type Object on the Object, which is a list, and this could be your “related objects” concept. This is how we do things like “friend list”. The User thing has an embedded list of other users. If I make someone a friend I just add one user to the other’s list. That user might exist as an embeded user in hundreds of other other users, but it makes the QUERY easier (it just works). We don’t worry about redundancy, in fact we look to achieve denormalisation where possible. That way the data you want is there when you want it, you don’t have to go navigating multiple joins and things. Want a repeating group with photos of the object … you have it already.

Excel makes everything a bit hierarchical, so it can be difficult to visualise this (arguable word is a better tool !) …

But on first glance I would say you add Photo as a list to Object, as well as Series (it will be duplicated, but don’t worry about redundancy). And that you embed Exhibition in Photo. Location is embeded in Object and Exhibition.

Now… it looks like you might have a many-to-many relationship with your various relationships between objects ?

That is probably the only slightly tricky one to model, and it depends (again) on what you want to query. But in this case you might want to have a thing that holds an “index” to the two things and the relationship type.

{
object1name : “The Man”
object2name : “The Tree”
relationship : “a sketch of”
}

That would, potentially, be better than embeding the BOTH objects in there. Just think about how you want to query.

2 Likes
3

So, for example, if you had a list of objects, with photos, and wanted to show the Country of the exhibition of the photo of the object …

Objects’s (Cell’s) Photo’s Exhibition’s Location’s Address : formatted address : Country.

(assuming Photos are also repeating).

You can reference it in just the text box.

Trying to do that as a series of searches would be very complex. Make your data do the hard work.

1 Like
4
  1. My suggestion is to save it as one of these: a. total number of seconds b. a new Field type with three subfields of type number called hours, minutes, seconds or c. using https://bubble.io/reference#Data.Messages.dateinterval
    Really depends on what your use pattern for this data is: are you just displaying it as is, maybe different formats, or transforming it, editing it somehow etc.
  2. You can create Location as a separate data type, create a few locations and then have a Location field on the Object. Since you are fluent in databases, it’s helpful to know that when creating fields of other custom types (such as a custom Location on a User) Bubble doesn’t actually copy the location, it just creates a reference to the Location entry in the DB.
  3. You could have a field say related_sketch if you plan on having just one, or related_sketches for more (and make it a list of Sketches). Same with other related objects etc. The field or list go on the object, you generally don’t need a relationship object since we create references to them anyway.
  4. Yes. You can have a list of Objects on a user for example. This can be deeper if you need it to: A user can have a list of Sculptures, and each Sculpture can have a list of Sketches.
3 Likes
5

Three ! In this case it is genuinely a “relationship” that is being modeled, not just the link, as the link has a type.

AKA an “Association Class”.

So you could put the relationship on the object, and have a new thing with a type (text) field and an object (object) field.

1 Like
6

@NigelG Do you mean point 3, not point 1? [quote=“lonetour, post:1, topic:1031”]
Many of the OBJECTS are related to one another. For example, sketches (OBJECT) for a sculpture (OBJECT), or painting (OBJ) in a SERIES (OBJ). Is RELATIONSHIP another OBJECT?
[/quote]

(As an aside I had a problem with the number of the questions. In the Input box of the forum (where you type your question) I had questions 1 to 5 (with 1 being above the picture, 2-5 being below, but in the Output it showed TWO number 1. Odd. So I removed the first question number)

7

:slightly_smiling:

yes, it is typed as 3 in the input, but is changed to 1 by the forum !

8

Who ya gonna call? Ghostbusters!! @emmanuel (See above re. numbers of lists not “sticking” )

9

So, I think I have realised that there are “lists” and “Lists”.

I have a “list” of OBJECTS (Data Type) but in the DATA TAB a “List” refers to a “Lists of Things” where there is more than one example of the Thing for that Object.

So, “Title” type would be text only (no list) as there is only one Title per OBJECT. Similarly, Medium as an OBJECT can only have one medium (oil on canvas, watercolour, bronze).

However, I would choose “This type is a list of elements” where it is possible to have more than one. So, strangely, as it is the art world, and OBJECT can have more than one PRICE as it can be sold any times, from a gallery, then from a Auction House, then privately. Similarly with Literature, as there could be many books that mention the same OBJECT.

So where there is only ONE instance of a field for an OBJECT, no lists. Am I right? (Because, so far, I made my data fields all wrong!)

1 Like
10

Yes, you have got it right :slightly_smiling:

Title is an attribute of the Object. The Object has a Title. So you have a field called Title on the Object thing.

And in this case it makes sense for it to be a Text field.

And Object has many photos. So we have to use the list on the database, as there are many of them. And each photo is of only one object (although we could upload the same photo several times, each one can only go against one object for the purposes of this example !).

This means we can upload photo after photo, and ADD them to the list on the Object.

However, that is just a list of photos, in itself it isn’t all that helpful as you want to know location / time etc (let’s ignore the info on the image !).

So, what we then create is a PHOTO thing. Let’s call it Object Photo.

We then create a field, very similar to the one above, on the Object. But instead of “image” the type is “ObjectPhoto”.

All of these fields are then on the Object for each photo.

So I have created a “Field Type” for myself. Notice I have also sneakily put “location” in the “ObjectPhoto” … which is another customised field type.

So…my Object has many Photos, and a Photo is taken at a Location, which has a LastVisited date. All of this is in the data that comes back on a single Search. You don’t have to go linking and searching.

5 Likes
11

So… Here is my likely data input form for an artist’s database of her art:

dataA.png1104×286 72.8 KB

At the moment, each field (Series, Medium, Title and Image) is a field of a DATA TYPE Object, and when SET is clicked the data is created as a new OBJECT.

Then, the image will clear, and a new image will be uploaded, keeping the majority of the fields, but changing the title. (Not quite sure how this workflow will look, but I’m confident it can happen).

However, sometimes more than one photo of an OBJECT will be taken. So then, all the data will be the same, only the image will change ( and possibly a Point of View field (left, right, etc). BUT there will be TWO OBJECTS, but the same work of art. Which is not ideal. What’s the solution?

@NigelG has suggested having a Field of type: Lists of Images. But how would that work in practice?

I did think about using the Multifile uploader, but can’t get it to save the data to the images.

Remind me, why did I think this was going to be easy? @emmanuel @georgeciobanu

12

It sounds like there is additional data to be stored with the image, so multi upload might not work.

You could create a “carousel” (you could use a horizontal repeating group) of your uploads. And offer a delete button too.

The thing we need to consider is what point you save the object. Storing the photos in a temporary list might be tricky. Could there be a two stage process ? Set up your basic object data and then add photos ?