0

Advice wanted for database design (modelling?)

I'm used to flat databases like Bento so I'm watching the 'Ninox and Nioxus Product Training Channel' on YouTube and seem to be learning that I could use a relational database. Here's what I want to do, I'd love some advice or pointers which can set me off in the right direction.

I have (in real life) a shelf full of folders containing documents. There are (let's say) 26 folders named 'A' through to 'Z'. Each folder has 99 pages and each of those pages needs a number of tasks. I want a database to help my workflow, checking off tasks as they are done. I want to view each folder as a table with its list of pages and other information such as description and progress.

I've created a single table database and that seems messy( I have more data per page than I'm admitting to) and complex. Its also had to create a table view I'd like.

So I want a seperate table for my folder names (A, B, C etc) and another for the pages and then I can create a link from Folders to Pages (or is it the other way around?) and I have a table where I can view each Folder and its pages in the view I want.

So I think I'm asking how many tables will I need, what data will go on which table?

I'm hoping theis is a simple 'new user' question! Any advice apreaciated.

Thanks.

10 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Davie

     

    I wouldstart with three tables

     

    Folders

    Pages

    Tasks

     

    Folders can have many pages so there is a 1 to many relationship between these two

    Pages can have many tasks so there is a 1 to many relationship between these two

     

    Your data model should look like this

     

    Shout out if you need further help.

     

    Regards John

    • Davie
    • 2 yrs ago
    • Reported - view

    Thanks John, very helpful.

     

    I know I have a road to travel but tell me how you see the destination. Where will I view my table list of pages? Will that be in one of the three tables (if so which?)

     

    So far I had created the Folders and Pages tables. I'm just learning how to create the releationship and only show the fields I need. I need to get my head around the left and right arrows and it seems the arrow on the data model goes the wrong way. Its not sending data from one table to the other but asking. An yway I'm sure the confusion will pass!

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Davie

     

    Yes it's a bit confusing at first. Here's a screenshot of the Pages table. The arrow going the right points to the Folders table and there will be just one for each Page, the Tasks arrow points to the left, where there will be many Tasks per Page. Relationships are always created from the Many side.

     

    Regards John

    • Davie
    • 2 yrs ago
    • Reported - view

    " Relationships are always created from the Many side. "

     

    I think this is the best bit of advice I've had all year! Didn't know that. Thanks.

    • Fred
    • 2 yrs ago
    • Reported - view

    You can view your pages from either the Folder table or the Pages table.

     

    You said, "I want a database to help my workflow, checking off tasks as they are done."

     

    That is a good use of a database. If that is the goal, it seems to me you want to only view tasks that are not complete. Do you give your tasks priority? Are some tasks more important than others? Maybe you want to consider looking at your Tasks table.

    • Davie
    • 2 yrs ago
    • Reported - view

    "it seems to me you want to only view tasks that are not complete. Do you give your tasks priority? Are some tasks more important than others?"

     

    No, not really. Let me expand on the puropse of this database.

     

    The 'Folders' are photgraph albums, the 'Pages' are pages of photgraphs in the albums. The photographs are to be digitalised. This requires a number of tasks to be completed. Some of these tasks require to be done in order, but generally there is no importance.

     

    I wonder id I do really need the seperate 'Tasks' table suggested? I'm not yet clear on the advantage / disadvantage.

     

    What I want ( and seem to have created) is an 'Index Page' for my Folders. This is a spreadsheet style table:

     

    Folder   Description

    A          Year 2000 - Some other info

    B          Details about theis folder

     

    When I click on a Folder entry I'm taken to a spreadsheet type view which lists all the pages in that Folder with some basic detail

    Page     Description              Date             Note

    A/1      Photos from Event     Jan 2000      Short note

    A/2      Some other event     Feb 2000      Another note

     

    Now I click on a Page entry and go to a page with all the details I need relating to that Page. This will include the type of film (B&W or colour), other detail and then the 'Tasks'. The Page need to be Scanned then captioned and a couple of other workflow items.

     

    So the Tasks are really part of the Page. If I have a seperate Ninox Table for Tasks I need to bring them to my Page and that makes the design a bit messy (as they appear as a spredsheet style table). If the Tasks were fields in the Page I can just enter the data there and arrange the design as I wish.

     

    When I get clever at this I'd like to have an 'at a glance' data on the spreadsheet style Page. Perhaps some colour denoting progress

     

    Page     Description              Date             Note            Scanned    Captioned

    A/1      Photos from Event     Jan 2000      Short note    [green]     [red]

     

    Thanks.

    • Davie
    • 2 yrs ago
    • Reported - view

    Sorry for all the typos, the entry filed won't resize for me and you can't edit once posted.

    • Fred
    • 2 yrs ago
    • Reported - view

    The question of do you need a separate task table will depend on the data being kept.

     

    Are you doing the same task to every photo?

     

    Could you be adding more tasks in the future?

     

    Could you have a different amount of tasks for each photo?

     

    Using your example above of Scanned and Captioned. If those are the only two tasks you do to each photo then I don't see why you would need a separate Tasks table.

    • Davie
    • 2 yrs ago
    • Reported - view

    Earlier I was thinking I don't need the seperate Table. But as I add data I can see its easy to have a view of all the Tasks and I may want to build a list showing which Pages haven't been scanned - for example.

     

    The Tasks are: Scan, Date, Caption, Keyword and Rate.

     

    The same Tasks for each Page, not necessarily in order but all need completed. Will there be more? Never say never.

     

    Some Tasks are Choice fields. Scan is Yes/No but other fields can be No / Some / Yes.

     

    I may not need but I think I will keep Tasks in their own Table.

     

    Thanks and soz for any typos!

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Davie

     

    If you want to add the same tasks to each page you can automate this. Use either a template table or a flag in your Tasks table to show they are going to be used as a template and use some code in the Trigger on new record: section of your Pages table along these lines

     

    let a := this;
    for b in Template do
    let c := (create Tasks);
    c.(
    Pages := a;
    Name := b.Name;

    Done := 0)
    end;

     

    Regards John

Login to reply

Content aside

  • 2 yrs agoLast active
  • 10Replies
  • 335Views