Anyone willing to offer me up some help on this would be a life saver.
Just before i go into detail, I'll be using OpenOffice's Base, which is fairly new to me as I'm used to Access, so if there's any advice that is given where you may know the location of a tool, let me know as i'm still learning it all.
I'm looking to create a quality assessment database. For this, I have attached the relelvant spreadsheet that is used to general the hand-written copy of the assessment side of things. I hope by including this it will help any issues. I'd suggest having a look at it prior to the explanation.
From where i see it, I'll be splitting the database into 5 relational integrity tables.
An Info table holding the non relational data such as supervisor etc where there will only require one record. Primary Key Likly to be a non-descript ID reference
A Comments table - Purely as there will be many records generated otherwise, I figured this would just be a very large field table. Not all comments fields will be filled out, so it would end up holding very little data. I dont personally see a problem with this, however if there are other suggestions, please feel free. Again, a non descript Primary Key. This would also include a ListID child Key for referencing.
A Listing Table, that would hold the information of the critera assessed (like Toilets Cleaned) and what section it falls under (Bathroom or Living/Communal Areas). I thought, that this would just be like a ID tag table for the data table. Therefore, ListID as a primary key.
A Standards Table, where the relevant standard would be entered against the Listing - I'm assuming that ListID would need to be a Child Key in this table for reference. I'm not sure what to do about the Primary key in this table.
Finally a Data table to pull all the Primary keys together for the referential integrity, with each Primary key from each table having a field as a child key and again, a Primary Key which is non-descript if it is needed.
Note this is the first time i'll be working with primary and child keys so its all a little new to me.
Ideally, I would like the form to be as close as a replica to the form as possible, this way, it would ease the input process for the user, as its a direct input from the form. Therefore the standards section (exceeds expectations etc) would be in the form of some sort of tick boxes, with an IF statement attached that would entered a number against the grade - this is to eliminate the Yes/No feature and make the stats portion I will eventually build up easier to create and track.
I'm not sure if i'm over complicating this, or if i'm even going about it in the right way, so any help would be appriciated,
Info table may not be needed, as these values can simply be typed in
Comments table also may not be needed, as the comments should accompany the assessment, and splitting off as a separate table with a key relationship is extra work for indeterminate benefit
Listing Table is excllent
Standards Table is not right, at least i don't see it related to the Listings
Data table is right, this will hold the detailed rating which will be a single value (exceeds standards, standards met, etc.), for each Listing on a specific assessment, including the Comments field, defined as NULL
so you also need an Assessments table, where each row is an instance of a form filled out, giving location (room number or area checked), date, and cleaner responsible
the PK of Assessments can be an autonumber, and this key will be referenced by a FK in the Data table