You first need the correct structure. Look at each group of data and begin drawing on paper, asking these questions:
Can a Project have more than one Step? Yes. Then Steps must be related table for multiple records. Called 1:n one-to-many relationship.
Can one Step ever be in more than one Location for that specific Project? If not then Location should be a field in Steps.
Can one Step have multiple Documents/attachments? Yes. So Documents must be 1:n to Steps.
Can Documents ever be linked to more than one Project? More than one Department? More than one Step?
Tables/fields might look like this:
ProjectID (unique, FM-generated, auto-enter serial number)
... fields for other details specific to a project
DepartmentID (unique, FM-generated, auto-enter serial number)
DeptName (text )
LocationID (unique, FM-generated, auto-enter serial number)
StepID (unique, FM-generated, auto-enter serial number)
StepNo (number) ... this is unique order that you want the steps performed. It is manually entered by management to display sort order etc.
StepName (text ) ??? whatever you want to call your steps
... fields for other details specific to a Step
AttachmentID (unique, FM-generated, auto-enter serial number)
Attachment ( container ) ... store as reference
NoteID (unique, FM-generated, auto-enter serial number)
CreateDate (date) ... I always prefer timestamps
StaffID (text ) created by
* purpose of multiple IDs is so that notes can be related to any table directly but User can search only one Notes table and find notes pertaining to anything written. It also speeds your solution because all of these notes (being in a related table) aren't loaded when your main tables load.
StaffID (unique, FM-generated, auto-enter serial number)
DepartmentID (are staff assigned to a Department?) ... so if you want to find all staff who might have worked with documents assigned to Sales, you could find them. There are general reasons as well, just for displaying your employee hierarchy. I also wonder about you assigning a document Attachment to a Department. Might that be a lookup from the Staff table, pulling Department from staff who created the Attachment record? These are just considerations while designing.
After you have created your tables then you can begin linking them as you think they should go; you can change them if incorrect. I can only assume:
Projects --< Steps --< Attachments
... how to link the rest will be determined by your design on paper where you've asked yourself the same kinds of questions I asked you above. You may also need one or two join tables. Here is a link to a pdf called "Thinking About Solution Design." It was written by Ray Cologon, Ph.D. (aka CobaltSky), one of the world's top Developers. It was written in 2002 but still holds true today.
If you find yourself creating multiple 'like' fields, it means you should split them off to a 'many' relationship as another table with records. Once you get this created, you can easily perform a find 'through your relationships' to display and retrieve the data in any way you wish. Proper foundation is the key. Keep this thought in mind, "When in doubt, split it out."
Thank you SO much for the thorough and thoughtful reply. I'm jumping right into it with gratitude.
this really is a great answer!
it helps me too.