New Guy Structure questions

[cgjames]

Hello!

I am a therapist, not a programmer, but due to various unfortunate budgetary situations, I have been drafted into building a database for collecting our grant money. As such, we have a series of forms we use and specific data to collect for our grant submission. I am building the DB on 2010, but the system will run on 2003. I don't have access to Access 2003, so I am using the file format and hoping it'll work.

I'm going to try to ask questions as specifically as I can make them. My first deals with the structure of the database. I am currently working with one section of the agency that uses four forms, several of which have the same data. I have broken down the database into like-data tables (rather than form-based tables) and plan to use both Client ID's and form ID's to connect the tables (so that we can save and retrieve the data that was input at any given meeting).

I have read that one goal is to minimize redundancy of data in different tables (normalization I think it's called). As such, I want to have the therapist doing the interview to type "Form Number" into the top of the form, and have it write that number to each of the tables that that form uses. Right now I am getting an error message that is telling me that "there must be a related record" in when I try to create a new record. I suspect that referential integrity cannot create new records?

Currently I have one main table which holds form id, client id and type of form. I then have five tables which each hold around 20-30 variables and form id. I think the terminology there is a parent table and five child tables? Or perhaps five parent tables and a linking table... Anyway, the five are linked to the main one in a one-to-one relationship with referential integrity enforced. Will it be necessary to do something like create a query to write a new entry into each of the sub tables every time I want to write a new entry? I suspect that the reason I couldnt find any help on this question is that I'm either structuring my database in a really weird way, or it is so basic that I have just missed something really small.

Also, I'm not in love with any of the structure, so I'm happy to tear chunks out or completely revamp everything if this design isn't sound!

Thanks!

[ajetrumpet]

a few issues for you to consider:

As such, I want to have the therapist doing the interview to type "Form Number" into the top of the form, and have it write that number to each of the tables that that form uses.

In general, if your form entry process requires code to write the data to tables, the structure is not completely normalized. Now, with that said, it doesn't really matter, but that's basically the way it is.

Right now I am getting an error message that is telling me that "there must be a related record" in when I try to create a new record.

RI refers to the enforcement of no children when a parent is not present. What that means is ''if no matching record is on the left, you can't enter a record on the right''. Left side recs are parents, right side recs are children. In a 1-1 relationship, the difference between the 'left and right' sides basically depends on which way the relationship was specified in the relationship manager when you created it.

Currently I have one main table which holds form id, client id and type of form. I then have five tables which each hold around 20-30 variables and form id. I think the terminology there is a parent table and five child tables? Or perhaps five parent tables and a linking table...

the terminology doesn't matter, but this is a tad bit off-kilter, IMO. But it is by no means unproductive or complex.

Anyway, the five are linked to the main one in a one-to-one relationship with referential integrity enforced.

this is probably what's causing the error, above.

Will it be necessary to do something like create a query to write a new entry into each of the sub tables every time I want to write a new entry?

of course not. queries should have nothing to do with entering data in your type of scenario.

Also, I'm not in love with any of the structure, so I'm happy to tear chunks out or completely revamp everything if this design isn't sound!

I don't think it's horrible, but it could certainly be easier for you to use, speaking from a users prospective. But revamping it completely, I would hope would be something that should be paid for.

[cgjames]

Ah hah! That was the trick. I didn't realize that a table being on the right or left hand of a relationship connection made a difference! It seems to be working now. Hooray!

Given the fields I'm after (20ish for demographics, 15ish for data on the call to the crisis hotline, 15ish for contact info, 35ish for history of the sexual assault that brought them etc) what would be a more efficient method of structuring the database?

One of my main goals in this project is to learn the best/most standardized way to build a database.

[ajetrumpet]

Given the fields I'm after (20ish for demographics, 15ish for data on the call to the crisis hotline, 15ish for contact info, 35ish for history of the sexual assault that brought them etc)

this is the most common mistake when building one. IMO, more than 10 fields, or maybe 15 for big business, implies that the developer is not using the relational concept to its full extent.

so this is a crisis center? well, with your ambitions of the number of fields, you need to break up the whole task correctly. If you do, you'll immediately see the power that Access has over Excel, because it'll be able to handle megabytes of your required data entry in one single location.

So to get started, I would recommend only a few tables, which is all you should really need, believe it or not. From what I know so far, your tables should be something like (tables in CAPS, fields in lowercase):

CLIENTS
clientID
any more demographics about them
all personal information about them, including contact info

CALLS
clientID (FK of above table)
all information about the call


Aside from the above tables, you should have lookup tables present as well. These are very common, because it lets users choose options for inputs in the tables that actually store relevant data. Lookup tables shouldn't be used for anything other than referencing. They don't hold any data that's relevant to operations of a business, or record keeping. So for instance, one of yours should probably be called ISSUES. I'm guessing there are categories of issues in the crisis business? So classifications to be specified in the CALLS table would obviously need to be there. So the table would look like:

ISSUES
issueID
issuename
any other relevant information about it

and on your data entry form, the table is simply used to provide these options when entering info about a call. Usually, you'll find the information displayed in a dropdown, provided by sql in that control's properties.

[cgjames]

That is definitely helpful, thanks! To clarify, we are a sexual assault treatment center. We have a therapy side, an immigration side, one for advocacy, case management, legal support, crisis calls, and a few other miscellaneous things. We have about 20 forms I hope to eventually add and annually renew 16 unique state, federal, city and county grants.

I have used one lookup table for staff name, staff position (therapist, advocate, admin, etc), and percent of each grant that pays that staff's salary. That seems to be working nicely. For most of my combo boxes I've just been telling it to read the "values" I assign and pick from the list.

Are you suggesting then that I should combine the tables Services provided, referrals made, plan set up, call data and client satisfaction and also combine demographics, contact info and information about their sexual assault into two giant, 30-60 field tables? I can certainly do it, and it will make things a bit simpler, but it seems counter to the goal of normalization. Also, currently each form is divided into sections. One may have referrals and plan but no demos. Another may have contact info and call data and nothing else. They are mixed and matched in all variety of ways. With one giant table, I'll have a lot of blanks and much longer entries than I would if I separated the tables wouldnt I? I attached a copy of the database in case it would help to take a look! A lot of the forms are designed just to test functionality and not as real forms.

[ajetrumpet]

well first of all, let me say that your naming conventions could even put MS to shame. I love it! it reminds me of writing mysql queries.

at any rate though, the first thing that you absolutely need to do is get rid of the 1-1 relationships. see here: http://support.microsoft.com/kb/304466. Specifically, read the section on 1-1 relay's. That will tell you exactly why this setup won't work for a call center environment.

and the second thing that needs done is a re-evaluation of your business drivers. e.g. - what drives what.

A form is not needed unless there is a call and a client needs something, like a service, right? Well, if that is the case, then the only business driver your database is subject to is the 'call'. I don't think I've ever explained it this way before, but business drivers will generally always be on the left side of a 1-many relationship somewhere, and a lot of times they will be the ''one'' side of many different relationships in the app. In your case, the 'call' table will be at the top of the hierarchy of relationships. It's at the top of the chain.

I guess, aside from the drivers, the other objects I will call ''blocks''. I just got done configuring a Drupal site (which was a bug-ridden, piece of junk, BTW), so I'm hung up on that word right now. a ''block'' of data is data that is related. so, your blocks (tables) would probably be:

• clients
• services
• forms
• referrals
• staffinfo

there are others, but they are wildcards, as far as placement and access. Meaning, there's more than one right way to use these resources, and 10 different people will tell you 10 different things.

Each block, IF it is directly affected by a driver, should be related to that driver. Here, the only block affected by a call to your organization would be 'referrals'. And thus, it should be related to your calls table. Here's why: Every call involves a referral possibility, regardless if one was made or not It exists immediately when a call comes in.

Now, for the other blocks. They are obviously not related to any drivers, because I said so (), so these can sit by themselves. And these are also exactly what you were asking about earlier. The data from these are entered into your 'calls' table, but that is done by sql lookups through forms, and data displayed through boxes. The sql source of a multi-value control has absolutely nothing to do with binding that control to a table (validating the data entry of it), although I'm surprised at how many people still don't get this!

At any rate though, To get you started, the image attached shows the changes I would personally make first if I was doing this. Notice that the only relationship needed is the one between a call and a referral. Everything else is only designed to hold unique records of their own. Also, notice the 'ID' field in every table. Those fields are all autonumbers and PK's. That should get you started at least.

And one last thing (for this chapter of my novel anyway), that also still surprises me. A lot of people make the mistake of putting tables on the right side of 1-many's that shouldn't be there. For instance, in your situation, if you put the tables staff, forms, and services on the right side of a relay, all related to the calls table, it would completely defeat the purpose of Access itself. I can't think of an explainable reason why right now, but give me time and I'm sure I can explain it in English. But trust me, it's not legit to do that sort of thing.

[ajetrumpet]

james,

I just read through your last post again. Aside from everything else I've said,which goes along with a call center situation, you might consider providing your people with more than one database. One per department. Or if you don't want to do that, an extra table for 'departments' or the equivalent. I didn't remember reading how complex your org. was.

But you know, the structure in the beginning completely depends on what you want out of it in the end. And that rule is the same for anyone in any industry.

and if the call center stuff was helpful to you, consider looking at samples via google. the only thing I don't like about access samples is that some of the sights that offer them are only doing so to boost their chances of getting new and more complex business than what they've posted as freebies. Be wary of those things (some of them) because as I'm sure you're aware, if profit is a motivation, the instant gratification problem can seriously hinder good guidance and direction through sample databases.

[ssanfu]

Also, I'm not in love with any of the structure, so I'm happy to tear chunks out or completely revamp everything if this design isn't sound!

I have reservations about your structure also. Very, very seldom would you have a 1 to 1 relationship between two tables, let alone 5 tables.

First, I would recommend always turning off the "Name AutoCorrect" option. It is a known cause of corruption.
(See http://allenbrowne.com/bug-03.html)

Second, read up on "Look up Fields" (http://www.mvps.org/access/lookupfields.htm). They hide the true data stored in the field, they are only of (limited) use if you are entering data directly into a table (a no-no unless it is a mdb for your private use) and if you ever want to up-size, you will have to redesign your tables.

Third, it looks like you are committing "spreadsheet" or designing your tables based on the paper forms. Attached is a modified mdb. I'm not the best (or even in sight of the best) mdb designer, but I did set up an example. Not knowing what the forms look like or what your requirements are makes it harder to suggest modifications, but take a look.

I didn't use any code in my example. There are other ways to enter the data: one would be to have an unbound form with unbound checkboxes (duh) and write a lot of code to create the records for the selected options.

I think you really need to normalize your table structure..

[cgjames]

Oh wow, thanks a ton for all of your feedback!

First off, Ssanfu, your example helped a ton. I can see now that just conceptualizing the way a referential database is designed is way different than I had thought. I understood that lookup tables existed, but I didn't realize how powerful they could be! My main trouble is that the form I am using uses mostly check boxes and radio boxes for everything that was included in the lookup tables. The therapists that are filing out the forms are, in many cases, not even tech-literate enough to know how to navigate windows explorer, so any variation from the paper form will freak them out. I guess that is where your unbound checkbox suggestion comes in, but given that I have no VB background whatsoever (aside from programming Pong into a cheap commercial spinoff called Dark Basic when I was 15 or so) that seems like it would be tricky. I will do some research on it and try to figure out if it is in my range of comprehension. I really like this example though, it gives me a much better idea how a referential database is so completely different from something like Excel!

Ajetrumpet- Yes, I was definitely planning to split the project into four self-contained databases. This will cause some trouble generating reports that span across multiple parts of the agency, but I think it is entirely a worthwhile sacrifice. It's a bit unfortunate that this book did such a minimal job of explaining the whole process of structuring these tables and how to design a database in Access, since that was its whole job! I'm going to use the design structure that Ssanfu suggested and examine how it works, then I think I will have a better understanding of what you suggested.

[ssanfu]

--snip---
My main trouble is that the form I am using uses mostly check boxes and radio boxes for everything that was included in the lookup tables. The therapists that are filing out the forms are, in many cases, not even tech-literate enough to know how to navigate windows explorer, so any variation from the paper form will freak them out. I guess that is where your unbound checkbox suggestion comes in, but given that I have no VB background whatsoever (aside from programming Pong into a cheap commercial spinoff called Dark Basic when I was 15 or so) that seems like it would be tricky.
--snip---

Yes it is a lot harder to use unbound forms and controls. You have to write code to add, edit and delete records.

Attached is an example of the unbound form option. Starting with the previous example mdb, this took me several hours.... it doesn't help that I am at work (night shift)

The example only does ADDS!!! No edits or deletes. Selecting a different Form number (in the details section of the main form) updates the subform. It needs a lot more work to be functional. But you can see that it can look like a paper form, but the structure can be a normalized database structure.

[cgjames]

Wow, thanks a ton for all of that work!

I spent the past few days trying to apply it, but found that the scope of this database is going to be way beyond my current skills.

A "version" of the database already exists, but the guy that built it clearly had no clue what he was doing. There isn't a single relationship in the whole thing. It's basically about 100 excel sheets linked by queries. It collects and modifies the data, but he didn't built any reports and then charged $60/hour for 30-40 hours every 3 months to compile reports from it, effectively scamming my agency out of thousands. It took about 4 years before they realized they were getting robbed, so I tried to build this new database. Instead I'm going to try to start with his code (which works for data collection) and just build reports on it. I suspect it will be very tedious and the most un-normalized database imaginable, if it's even possible, but I'm more likely to get -something- out by the time my volunteer time is up!

[ssanfu]

100 Excel sheets??? Ugh!!!

So $60/hr X 40 hrs X 4 times a year X 4 years = a little over $38,000 !!! Wow.

My advice is to start asking for references for local database programmers. Check them out. Ask for testimonials from their clients. You need to get the excel data moved to a real database. With reports. By a professional database programmer!!!! After all, would you want to get therapy from a programmer???

In the long run, it will be less expensive to hire a database programmer than for you to try to learn the skills to develop a relational database.

Good luck