How to structure my reports builder?

Hi all, love what Xano is doing. I've got a bit of experience with MySQL and I need some guidance on how to structure my tool.

It is a reports builder and each Report is made up of the following:

  1. Rooms

    1. inside each Room you can add Items

    2. each Item will have a set number of data points such as Title, Cost, Image etc.

  2. Utilities

    1. each Utility will have some data points such as Location, Date etc.

  3. Health and Safety Compliance

    1. This is a series of questions which the user has to answer (yes, no, n/a)

  4. Documents

    1. User can attach some documents to the report

  5. Signature

    1. The user has to sign the doc

So I'm trying to understand how to structure my database. I think Rooms and Items will be separate tables so that's fairly easy.

However, what about Health and Safety Compliance? Each Report will have a series of questions and answers but these will depend on the Type of Report it is. For example an Entry Report will have 5 questions, an Exit Report will have a different set of questions. Should I save these questions in a separate table or create separate tables for each type of Report Questions? Confused over this?

Finally Docs and Signatures, would it be best to save those in the Reports table themselves or in a separate table and refer to them?

Just thinking of speed as well since each Report may have hundreds of items.

Any help massively appreciated thanks!

4 replies