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.
I wouldstart with three tables
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.
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!
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.
" 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.
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.
"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:
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]
Sorry for all the typos, the entry filed won't resize for me and you can't edit once posted.
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.
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!