May we ask about design basics here?

[stuartnz]

I haven't started building my database yet, but was hoping it would be alright to ask about design basics first. If this post is inappropriate, please feel free to delete it. Here goes:

I'm an area supervisor for a market research company here in NZ that specializes in large-scale government and social policy research. We have several major projects coming up, and my job is to monitor progress of the field staff, assigning work areas as they become available and keeping track of completion rates.
Currently, the field staff email me progress reports in Excel form. This is inefficient, since they send me a spreadsheet containing both new and previously submitted data. I then have to manually copy new data onto an existing spreadsheet on my PC, and work from there. So, I've decided on a database where they can enter their results as they go, during weekly debrief sessions. The database could then be used to track progress of each work area allotment, and also to compile a record of the performance of each member of the field team, for each of the projects they're working on. It would also enable me to monitor the various projects and assign team resources as needed. I was going to make this a web-based DB, but staff technophobia, and some staff's not having internet access forced me to abandon that.

I've read some primers on normalisation, and have started building a model in Azzurri's Clay DB modelling plug-in. I was wondering if anybody could take a look at what I've done so far and let me know if I'm heading in vaguely the right direction, or if I should scrap it and start again. Here's a shot of my (very preliminary) model:
http://maxqnzs.com/screenshots/model1.jpg

One of the things I'm trying to get my head around is that as it stands, I can see how I could run a query by project to find the interviewers working on that project, but not the other way around, a query on interviewers to find the projects they're involved with. It's possible that one staff member might be working on up to 4 different projects, and be responsible for more than one meshblock in one or more of those projects. This is the bit that I'm having trouble grokking.

[Steve Workings]

Ask away here! Design can be an issue that takes lots of words, and time. I don't have enough of all that right now, but your question at the end provides a chance to show you something that I think can be quite helpful.

When constructing a relational database, you need to think about the links between tables. I havn't studied your model but I think you understand that much already. Linking tables in A5 is to make "Sets".

Something important to understand is that any one table can be included in any number of sets.

For instance, from the AlphaSports model, you can link each product (e.g. inventory item) to its vendor.

Product
|--------->Vendor

This would be useful for a listing of Products where you might want to also show a bit of vendor info.

But you can use the two tables in an different set for a different purpose:

Vendor
|=======>Products

This would allow you to query for a particular vendor, and also have access to all the products associated with the vendor.

Once you understand how you can construct different sets like this, the opportunities to create and get exactly what you want are dramatically increased.

Hope this helps.

[davej]

Design Basics

Stuart,

You are doing the absolutely right thing. Thinking through the design phase before you start banging on the keyboard will pay great dividends; in the broad perspective, you will finish sooner AND you will not have to back up and redesign your tables/sets, all of which yields much less frustration.

One suggestion I would make: as a general rule do not use numeric fields (NUMERIC/INT on your model) unless you are doing math with the field. Set them as character and restrict the input to 0 through 9 only. If you take a look at the table structure in design mode, you will see that Alpha offers you 13 different data types.

Also, Alpha doesn't differentiate between numeric and integer.

Dave

[stuartnz]

Stuart,

You are doing the absolutely right thing. Thinking through the design phase before you start banging on the keyboard will pay great dividends; in the broad perspective, you will finish sooner AND you will not have to back up and redesign your tables/sets, all of which yields much less frustration.

One suggestion I would make: as a general rule do not use numeric fields (NUMERIC/INT on your model) unless you are doing math with the field. Set them as character and restrict the input to 0 through 9 only. If you take a look at the table structure in design mode, you will see that Alpha offers you 13 different data types.

Also, Alpha doesn't differentiate between numeric and integer.

Dave

Thanks, Dave. Most of those INT fields are planned as auto-increment primary key ID fields. The one table with a lot of "non-key" INT fields is the "Meshblocks" table in which will be recorded the number of houses that fit into each of 3 different categories. Being able to crunch the numbers could conceivably be useful for determining if certain staff do well with certain catergories or have problems with certain categories. That said, there are a couple of those INT fields that I will build as CHAR instead, so thanks again.

One or two more questions, If I may. In my earlier post, I said
I can see how I could run a query by project to find the interviewers working on that project, but not the other way around, a query on interviewers to find the projects they're involved with. It's possible that one staff member might be working on up to 4 different projects, and be responsible for more than one meshblock in one or more of those projects. This is the bit that I'm having trouble grokking.

I have a 1:M relationship planned between "Interviewer" and 'Meshblock" and a 1:M between "Project" and "Meshblock". Would this be sufficient to be able to run queries to find out which projects any given interviewer is working on?

The other question is about historical data. Do I need a whole new bunch of tables to record data about previous projects? This information will be important in allocating staff to projects, since their previous effectiveness will largely determine how much of the available work they get assigned.

[Tom Cone Jr]

Stuart,

You're using some illegal characters in a few of your field names. The DBF format Alpha Five has been around a long long time. This is a good thing. However, it's naming rules are not as flexible as more recent database formats. The pound sign (#) and the forward slash (/) are both illegal.

My personal practice also is to eschew blank spaces in any object name. If I were doing your database, my field names would contain hard underscores (_) instead of blanks.

I can't tell the hierarchy of relationships from your diagram. Presently I don't see any way to link Projects and Interviewers except through the Meshblock table. This will work well only if

Interviewers can have multiple linked Meshblocks, and
Meshblocks will have multiple linked Projects.

Your post suggests that you actually need:

Interviewers will have multiple linked Projects, and
Projects will have multiple linked Meshblocks.

If the latter is the case, then you'll need the Staff_id field in the Projects table so that it can be linked to the Interviewers table.

--tom

[stuartnz]

Stuart,

Interviewers can have multiple linked Meshblocks, and
Meshblocks will have multiple linked Projects.

Your post suggests that you actually need:

Interviewers will have multiple linked Projects, and
Projects will have multiple linked Meshblocks.

If the latter is the case, then you'll need the Staff_id field in the Projects table so that it can be linked to the Interviewers table.

--tom

Thanks for that, Tom. You're right about the relationship planned, it's as I said earlier 1:M Interviewer-Meshblock and 1:M Project-Meshblock. I really appreciate your reply, since it's confirmed my thought that I would need another 1:M Interviewer-Project. It's very gratifying to realise that I am on the right track, after all.

As for the naming of the fields, that's only in the modelling software I'm using. The plan is, grok the structure, then devour the Alpha manuals and then build. The thing about spaces being verboten in FIELDNAME I'm actually very familiar with, since the only database software I've spent any real time with is Paradox, which is similarly rigid in its naming conventions. The model is more about clearing my head and sorting out my thinking. Once I've got that down, I'll settle on final fieldnames, etc.

[stuartnz]

Stuart,

Interviewers can have multiple linked Meshblocks, and
Meshblocks will have multiple linked Projects.

Your post suggests that you actually need:

Interviewers will have multiple linked Projects, and
Projects will have multiple linked Meshblocks.

If the latter is the case, then you'll need the Staff_id field in the Projects table so that it can be linked to the Interviewers table.

--tom

It occurred to me on re-reading this that there's a bit of both in the actual scenario. An interviewer CAN have multiple linked meshblocks (meshblocks on the same project), projects WILL have multiple linked meshblocks, and an interviewer CAN have multiple linked projects.

I also realised that it's a good idea to kill bad habits early, so I've renamed the fields in my model, and linked the Projects and Interviewer tables by means of the StaffID as a foreign key in the Projects table. Thanks for the reminders! In the modelling software I use, the arrow on the links points FROM the child TO the parent. That seems a little back-to-front to me, but it means that the link indicates a 1:M relationship in the opposite direction to the arrow, as here:
http://maxqnzs.com/screenshots/model1.jpg

Now all I need to do is figure out how to incorporate the historical data, and then I can start reading the Alpha Guides.

[davej]

Historical Data

Stuart,

You can iedentify historical data quite easily (which is what I think you are asking about). You can add a logical field labeled 'Current?' with 'N' meaning historical which will let you differentiate between current and historical all within the same table. Or, you could create a table with identical structure to the active table, name it 'History', and simply copy the records that meet your definition of historical to History.

The benefit of two tables is that it will be very obvious which records are current and which are historical and your current table will not contain any unnecessary data. The benefits of simply marking the historical records are that all the data is in one table and can be accessed slightly easier, but there is a bit of needless content in that when you are manipulating the active records, it will take a few seconds (or less) to look at the historical records. Of course, you could simply filter the single table and only look at whichever classification you prefer.

All things being equal, I would probably go with a seperate historical table.

Dave

[stuartnz]

Stuart,

You can iedentify historical data quite easily (which is what I think you are asking about). You can add a logical field labeled 'Current?' with 'N' meaning historical which will let you differentiate between current and historical all within the same table. Or, you could create a table with identical structure to the active table, name it 'History', and simply copy the records that meet your definition of historical to History.

...

All things being equal, I would probably go with a seperate historical table.

Dave

Thanks, Dave. I was leaning toward the two tables approach before I asked here, so it's nice to get a reply that supports that. However, your post made me realise that the sort of analyses I wish to perform may include both current and historical records. I also realised that both the "Project" and "Meshblock" tables have start/finish date fields, so I can already include projects and meshblocks that are finished. think I might try it both ways, with dummy data, and see which works best. To you, and everybody else who's helped me get this far, a very big thank you.

[stuartnz]

Having sorted out what I needed, or at least most of it, I decided to try building a small sample database. Almost immediately, I've found a problem with my design.

I have a one-to-many link between "Interviewers" and "Projects" since each interviewer can be working on more than one project. However, each project will also have many interviewers working on it, and as it stands, each time I enter a new staff member int to the "Interviewers table and assign them to a project, a new record is created in "Projects". This creates multiple listings of the same Job, each with a new ID KEY number, as shown in the screenshot linked to below:
http://maxqnzs.com/images/setproblem1.jpg

This suggests to me that I need to work harder at understanding the sort of links I need. Since each interviewer can be have many projects, and each project will have many interviewers, do I need to create a many-to-many relationship? If so, how? If not, how do I work around this?

[Tom Cone Jr]

Stuart, I do something similar in an event registration app here.

I have 3 tables. Members, Event_log, and Event_Registrations.

I use the Event_log to store one record per event. Each has a unique Event_id field. This would be analogous to a Projects_log table for your app.

Members can sign up for multiple events (projects). Each time they register (are assigned to) for an event a new record is entered in the Event_Registrations table. This is facilitated by a table lookup field rule which permits the user to simply pick the desired Event off a lookup list populated from the Event_log table. Each Event_Registration table record contains the Event_ld AND the Member_id.

I wind up with two different sets. In the first, Members are linked one to many to Event Registrations, using the Member_id field as link.

By inverting the design I create a second set, with Event Registrations as primary, linked one-to-many to Members using the Event_id field as link.

This permits me to work with two different set based forms. In the set with members as primary, I can step through the members (or fetch one instantly), and see all their linked event registration records. In the second set, I can step through the events, and see all the members who have signed up.

This seems directly applicable to your app. I can imagine a 3 table arrangement there using: Interviewers, Project_log, and Proj_Assignments instead of Members, Event_Log, Event_Registrations. Hope this helps.

--tom

[stuartnz]

Stuart, I do something similar in an event registration app here.

I have 3 tables. Members, Event_log, and Event_Registrations.

I wind up with two different sets. In the first, Members are linked one to many to Event Registrations, using the Member_id field as link.

By inverting the design I create a second set, with Event Registrations as primary, linked one-to-many to Members using the Event_id field as link.

This permits me to work with two different set based forms. In the set with members as primary, I can step through the members (or fetch one instantly), and see all their linked event registration records. In the second set, I can step through the events, and see all the members who have signed up.

This seems directly applicable to your app. I can imagine a 3 table arrangement there using: Interviewers, Project_log, and Proj_Assignments instead of Members, Event_Log, Event_Registrations. Hope this helps.

--tom

Thanks, Tom, that does seem a pretty exact parallel. I'll give it a go.

[stuartnz]

Stuart, I do something similar in an event registration app here.

I have 3 tables. Members, Event_log, and Event_Registrations.

I use the Event_log to store one record per event. Each has a unique Event_id field. This would be analogous to a Projects_log table for your app.

Members can sign up for multiple events (projects). Each time they register (are assigned to) for an event a new record is entered in the Event_Registrations table. This is facilitated by a table lookup field rule which permits the user to simply pick the desired Event off a lookup list populated from the Event_log table. Each Event_Registration table record contains the Event_ld AND the Member_id.


This seems directly applicable to your app. I can imagine a 3 table arrangement there using: Interviewers, Project_log, and Proj_Assignments instead of Members, Event_Log, Event_Registrations. Hope this helps.

--tom

I want to see if I have got this right. Here's a shot of my new tables, not yet put into sets.
http://maxqnzs.com/screenshots/linktable.jpg

If I have got it, that raises another question: I was planning to include "StaffID" and "JobID" links in the "Meshblocks" and "Respondents" tables. Would I be able to replace those with simply the "UniqueID" field from "Project-assignments"?

[stuartnz]

I may be groping toward sentience here. I now have these sets:
http://maxqnzs.com/screenshots/gettingthere.jpg

"Projects" is linked 1:M to "P_A" on "JobID",(p_a.set) "P_A" is linked to "Staff" 1:M on "JobID" and "Staff" is linked to "P_A" 1:M on "StaffID"(s_a.set)
This is working partly.
The default form view of "p_a.set" shows me the Job details and underneath that a list of the StaffID assigned to that job. The default form view of "s_a.set" shows me the Staff members details, and the list of the job IDs they're associated with. However, there's something I don't quite understand. In the form, the "JobID" field is blank, in two of the three sample records I've created, as in this shot:
http://maxqnzs.com/screenshots/smallproblem.jpg

The listing for this staff member shows correctly which jobs she's assigned to. In my sample, she's the only one assigned to two projects. THe other two are both assigned to "JobID" 1. However, one of the other Staffmembers has that JobID field on the "s_a.set" default form view empty (as in that screenshot), but the other has a "1" in that field. What am I doing wrong?

[Tom Cone Jr]

Stuart, this is how I'd approach it.

-- tom