Relationship Issues

[MTSPEER]

I have a database that has 4 tables called: Customer, Survey Info, Form Info, and Health plan. The customer table has the name, id number, address, phone number. the Survey info table has the same customer name, and id number fields, and then has all the survey questions and answers. The Health plan table has the same customer name and id number with the line of business plus the health plan. And the form table has all the fields I want to be in my form. I want it so when I update someonething on one table all the other tables will be updted, and so will the form.



-I understand that I can't have the same primary key for each table but how can I fix this? The customer has its own unique ID. And the reason I put customer name and the ID number in every table was so I can enter data easier instead of crossing over tables to see the name.

[Dal Jeanis]

That's exactly the reason that you *don't* put that data in every table. That kind of design would mean that every time you have to do an update, you have to run an update query against all the different tables.

Instead, you should take all that out and put only the Customer ID in those tables. Then, for each form that updates a different table, you write a query that joins the table to the Customer table (and to any other tables you need data from). That design is MUCH easier to learn, easier to code and easier to modify than having to learn how to update all the other tables from every possible form and every possible event that something can get updated.

[MTSPEER]

I already have all the survey information into my table though. I changed it up a little bit. Tell me what you think?

[Dal Jeanis]

Please see private email message ASAP.

[Dal Jeanis]

About form design

A form is about displaying information visually in an easy to read, easy to understand, easy to update format. As such, the form should be laid out to make the job easy, and to present the information in the most effective way that you can design.

Each field should take up only the amount of visual real estate that you expect it to actually use. Also, fields should be grouped in a meaningful manner.
* Customer ID number should take up enough space to put a 10 to 15 characters into, Customer name should allow for maybe 40-50 characters of space, and they should be grouped together.
* AAC, LOB and plan should be grouped together.
* Date mailed and date received should be grouped together.

At a guess, the first seven items on your form shouldn't take up more than 2 inches at the top.

If the answer ratings are going to be on a simple Likert scale (1-5) translated to short terms like "Satisfactory", then allow the label to go across the screen, but put the actual answer in a small box to the left. The remainder of the space can be used for the Other-please specify or for the optional comment. That way, each answer takes up just over an inch of vertical, and you can drop the overall form length from 21 inches to maybe 11-12 inches.

This isn't really code, but it will provide a quick visual example.

L----------------------------------------------------------------------------L
L(label) - Communicated Clearly explaining my condition(s)/health care needs L 
L----------------------------------------------------------------------------L
X---------------X L----------------------------------------------------------L
X(data)         X L  (label)   Comment(s)                                    L
XSatisfactory   X L----------------------------------------------------------L
X---------------X X----------------------------------------------------------X
                  X  (data)                                                  X
                  X                                                          X
                  X                                                          X
                  X----------------------------------------------------------X

[Dal Jeanis]

Will the same customer ever be filling out a second survey? If so, will they use the same customer ID, or a new one?

[MTSPEER]

No the same customer will not be filling out a second form. I kind of fixed my tables. But i don't really know how to link them. Since I cant set my datatype to number for my Number ID. I have Customer Name in all 3 tables. So I linked them together through 3 tables. Is that good? But I have Primary Keys for the following: In table Customer, field Customer ID. Table Survey Info, field Number ID(I just autonumbered it). And for table HealthPlan, field Customer Name.