Forum Academy Marketplace Showcase Pricing Features

Field in table stores unique id of potentially many tables

Need help Database
#1

Ok, thanks for answering my previous question.

I have a table called “notes” and one of the fields is the unique id of the “thing” which the note is “for” or “to”.

For example, in my app, I have properties, jobs, users etc.

I want any user to be able to write a note for the property, for the job or to another user. Do I need a separate table for each type of note, or can I have all notes in one table and have the unique id of the thing which the note is for stored in one of the fields?

Thanks in advance…

#2

You could do both approaches.

A property could have a list of notes as a field, or you could as you say have a reference to of the uniqueid in the note field as a text.

if you are working with a note type that can be referenced to different parent types, I would also hold some text value to be able to differentiate the parent types so if, for example you had a page that had a list of notes, then you know where to navigate to, a property page, employee page, a car page etc.

I use this approach for a notification type that can hold any type of parent type by using a text field.

I hope that one day we could get an ‘object’ type and then a ‘typeof’ action that you could use to determine endpoints rather than having to hand code unique names etc.(nudge: @Bubble)

#3

Thanks for the quick reply Dave

In the note table I have noteid, noteuser, notedate, notetype, notevalue, notefor

I can use the type, date, user information for sorting.

My problem is, I want to have each note allocated to a thing (person, property, tenancy, invoice) but all my things are in different tables. So what field type do I allocate to notefor? If I make it tprop (property table) then I can’t allocate notes to anything but properties.

#4

They way I do it is is store the uniquieId as a text field for the the parent thing and have a second text field that holds a value that you specify to represent the data type. I just use something like “PROPERTY_ITEM” or “EVENT_ITEM”

You can then test this to help navigate, for example, consider this use case:

  1. A user has a page “my_notes” which lists all the notes they have added across the application, in a repeating group. (i.e. from your note table)
  2. The user can click a note in the repeating group to navigate to the parent thing that contains the note, e.g. the property
  3. You would have a Click event on the repeating group, with a workflow which would have a chain of navigate actions, each one is a Navigate to Page with the link “mysite/therelevantpage/” + CurrentItem parentthinguniqueIDfield .
  4. each action using the “Only when” would check the second text entry containing the custom constant e.g." Only When CurrentItem ParentType is “PROPERTY_ITEM”, this ensures that the navigate directs you to the right type of page for the note entry, if it hits a success and navigates to the page, the workflow will automatically stop checking against any other navigation actions after it.
#5

I would be tempted to have a “Note” Table with the fields that are pertinent to all notes.

Then have a PersonNote, PropertyNote etc that just holds the thing (not a key).

Slightly more complex when creating the note in the first place, but you avoid having to search for ids. Which can get overly complex if you have deeper searches.

#6

Hi Nigel

The only problem with that is if I want to create a note for something new, then I would have to add a field to the table.

Also, there would be a lot of redundant space in the table, wouldn’t that slow the whole thing down during searches?

I have updated all the other tables where I used to store my generated ID and have changed them to TYPE ttype/tprop etc. This is the only one which doesn’t work nicely, so I’m thinking of keeping it a text field and using bubble uniqueid instead of my own generated one - is there a reason not to store the bubble uniqueid in a field in a table?

Thanks

Paul

#7

Hi Dave

Thanks for the example, it helps a lot!

I think that is what I am going to end up doing for this one, as it is the only field which cannot be one type of thing, so using the uniqueid makes sense to me, unless there are good reasons not to store the bubble uniqueid in a table.

Many thanks

Paul

#8

No reason you can’t. It just makes searches more complex to do it that way,

#9

agreed.

So my options are:

1, a separate table for each type of note;
2, store uniqueid as a text field
3, have a big table for notes with every type of note as a separate field

Out of these options, I think storing the uniqueid as a text is going to be the easiest.

Thanks for your help guys!

Paul

#10

4 . a single table “note”, with fields:
content
type

Possibly you’ve already considered this option.

Oops I didn’t read enough … yes you can have a unique_id text and a separate type text to reference the related object.