0

Understanding file attachment strategies

I have a few use cases that involve managing inventory items - like my photography and RV equipment. I want to link a variable number of pdf/photos/other (e.g. receipts, manuals, spec sheets, condition photos) to an inventory record.

I see that I can set the inventory table "File Attachments" checkbox and attach files that way. However, I don't see how those attached files are readily visible in a record form - you have to go to the Attachment tab to see them.

To make the presence of attached files more visible and accessible - and enable potential reuse of a file, I have created a table called "File Repository" with a "Type" field (Choice of values like Manual, Receipt, Photo, etc.) "Description" (Text) field and a "File" (Image) field. I created a Table Reference from the File Repository table to the Inventory table. This works really well as the list of all files associated with a record easily displayed in a form. I have experimented with using the "File Repository" table with multiple parent tables in the same database, that is creating a Table Reference to multiple parent tables. This seems to work fine.

Some questions:

  • Am I missing some non-obvious (to me) reason to use the table "File Attachments" approach?
  • Will having the "File Repository" table Reference multiple parent tables cause me problems?
  • What are the implications of one or more of the parent tables Referenced by the "File Repository" table being in a Composition mode? For example, if two parent tables, one in Composition relationship and one not, linked to the same "File Repository" record, will the linked record be deleted if the Composition parent record is deleted even though "File Repository" record has a non-Composition relationship with another parent table? (that was twisty but hopefully made sense)
  • Will I experience performance issues by the "File Repository" table size getting relatively big in terms of rows and disk space used?
  • I have yet to find any information on Ninox db or table capacities, or for that matter, what DB engine Ninox uses - can anyone point me to that information?
  • Can a table in one database be Referenced to a table in a second database?

Well, that was a lot - but this is fun stuff! Thanks for any help.

Cheers

Rob

5 replies

null
    • rob_young
    • 2 yrs ago
    • Reported - view

    Just answered the Ninox DB engine question I had - looks like SQLite 3 - no surprise there...

    • Fred
    • 2 yrs ago
    • Reported - view

    I'm wondering why you would have a table that is a child to two different tables. Ninox will allow you to set Composition to Yes on two different reference fields, so that was interesting. Though I think it kind of defeats the purpose of a child table. A record in a child table can not exist without a record in the parent table. Unless you talking about a table that is a true child and then linked to another. I tested this out and if you delete a record in the parent any records in the child table also get deleted.

    If you don't make it the file repository as a child to any other table then you don't have to worry.

      • rob_young
      • 2 yrs ago
      • Reported - view

      Fred My question about a child table related to two or more parent tables is really driven by curiosity about Ninox performance. Will performance of the parent tables be better if there is just a foreign key relationship to the child table rather than exploding the parent table size with many medium to large files in Image fields? 

      • Fred
      • 2 yrs ago
      • Reported - view

      Just being nit-picky, but Ninox does not use a "foreign key" relationship in the sense of other DBs. You don't pick the field to use as a key. Ninox does it for you when you set the relationship.

      I would think that storing all images in their own table would be a better way of handling such records. I do remember something in a Nioxus webinar about the best way of dealing with images but I forgot what it was.

      • rob_young
      • 2 yrs ago
      • Reported - view

      Fred I like a little nit-picky. I would say that as Ninox seems to use SQLite - which is a very standard SQL DBMS implementation that implements most if not all of Codd's rules - we are shielded from the technical details of things like foreign key parent/child keys and on-delete constraints by the Ninox wrapper. This is a very good thing as it puts the power of a fully functional relational database in the hands of consumers without them having to worry about that stuff. 

      I'm trying the "file repository" approach of a single table for all the files to be referenced from other tables. I'll report back as I learn.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 582Views
  • 2 Following