Database design question - how can I structure this best?

1

Hello community,

I am new to Coda and I still struggle with best practices around how to structure my data. I am breaking my head on how to best manage the data in my doc, so I am reaching out for help here.

The doc is an overview of all the features and elements of a SaaS platform we plan to build. We use Coda to work out the features and have an overview. the actual DB design for the actual database of our app will come later, so this is just to be able to have an overview

Instead of writing it down in txt, I thought it would be much better to have it in one or more DB’s that reference each other. Then, for each core feature or screen, we can use a DB View and the filter function to just show the relevant data for each core functionality (applies to which users, which screens do they see, which data is stored/retrieved)

What we are looking to get is:

GENERAL

  • an overview of all user roles
  • an overview of all core features (5 in total)
  • an overview of all screens (probably approx 20 in total)

FOR THE CORE FEATURES

  • a overview of which screens belong to this core feature
  • an overview of which user roles are active
  • an overview of all the data we gather (and from which user roles)
  • an overview of all the data we display (and to which user roles)

FOR THE SCREENS

  • an overview of which user roles are active
  • an overview of all the data we gather (and from which user roles)
  • an overview of all the data we display (and to which user roles)

Ideally, we can have an exact overview per user/core feature/element which data is stored and displayed. Or vice versa see for each datapoint where it is retrieved or used and who has access to it.

I honestly have no idea how to structure the DB to allow this. I have acquired a tiny bit of familarity with lookup columns and splitting out data in different DB’s —— but not enough to use it intelligently.

Also I have (as you’ve noticed) no familarity with database design or building apps in general.

How we have structured it now:
We currently have the pages structured after core feature (eg. Recruitment), with subpages explaining different screens in this core feature (eg Applicant Landing Page, Applicant Dashboard etc).

On each main page we have the mockups of the core feature and a general explanation. On each subpage we have a more detailed run-down of the screens of our core feature. As there are quite a few user roles, just writing down the flow quickly makes it hard to keep track and overview.

I really hope this is halfway understandable, and someone in the community can give me some pointers on how to structure this data correctly and intelligenty.

Thanks so much for even getting this far in my post!

Tom

1 Like
2

Okay, I manage to solve quite a bit on my own, which was a good learning process.

I have now structured it as follows:

  • User Roles DB (with all the user groups)
  • Core Feature DB (with User Roles DB as lookup)
  • Screen DB (with wireframes, description and a few other columns and Core Features as lookup)

I have a separate page for each core feature. That page starts with two Table Views:

  • one of User Roles DB, filtered to the specific Core Feature (displays only the users of the feature of the page)
  • one of Screens DB, as cards, filtered to the specific Core Feature (displays the screen img, title & description of the page)

image
image806Ă—1078 97.3 KB

All in all, I am really happy with this result.

Next step is to figure out where I should leave the information for the data I will gather/retrieve in each screen.

I am doubting whether to add it to Screens DB (so I say, for this particular screen I will gather this data), or should I start a separate Elements DB and somehow link that to the screens?

Any suggestions? Thanks for sticking with me here!

3

You’re definitely on the right track with this! :raised_hands:

I think you’ve chosen the right tables, and also have your Lookups flowing in the right direction (I find it’s often hard to decide if they should be A->B or B->A).

For your question about the data gathering, I think the question comes down to how structure/unstructured that data is.

If quite unstructured (i.e. each page is pretty unique in the data it gathers) I would just use a text field

If more structured (i.e. there are only about 20 possible pieces of data to gather, and different pages might gather some of the same data pieces), then I would make a Data Points DB, and have a lookup column to it in the Screens DB. This would also give you the benefit in future of being able to use filters to say, “What pages collect this piece of data”?

Other tips you may find helpful as you go deeper in this journey:

1. Reverse lookup columns
It seems like you’ve got lookup columns working well. You can also use reverse lookup columns to see the other side of the relationship. For example, in your Feature table, I understand you have a Lookup column to User Roles. If you want, you can go into the User Roles table, and insert a column. For column type, select Lookup, and then you’ll see a “Referenced By” area. It should show “Features > User Role” or similar. That will give you a list of all Features that reference this User Role. Similarly, you could see a list of all Screens related to a Core Feature, right in the Core Features table.

2. Nested Table Views
You can hover on a row, and click the fullscreen button to open it. Inside this detail view, if one of your columns is a lookup column, you can choose to display it as an embedded table, and see/edit columns from that other table.

4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.