Solved

Access 2007: "Am I normal yet?" a question about if my table relationships are normal and correct

Posted on 2011-03-16
17
684 Views
Last Modified: 2012-05-11
Hello, I have been working on an Access Database with some of the folks in this site and I wanted to get feedback about my table structure to see if it is normal.  I also wanted to see if, as laid out, my table design could make my forms/output look how I want.

 ee example
Here is the basic gist of the form:  I want to describe academic articles on cancer each with a unique [Call Number].  Four tables are related to call number unique key in a one-to-many relationship (PDF_Files, tblStudyGrading, tblGroup and tblStudyOutcomes).  Because they are the most confusing, I would like the most help regarding tblGroup and its relationships as well as tblStudyOutcomes.
So we have a bunch of articles with some information about the article in tblPtCharacteristics.  I want to describe the article further in tblGroup (which has a unique key GroupNumber, which relates to subsequent tables) by creating groups of patients who have data separated out in the article (for example a Control group and an Experimental group).  After a user has described groups [GroupNumber] describing an article [Call Number] he or she can then describe the groups by entering characteristics about them in the tables tblAgeDesc, tblRaceDesc, and many outcomes (these are linked on tblGroup.[GroupNumber] AND tblstudyoutcomes.[OutcomeID]).

I want the database to work in this way.  A user goes through the many forms necessary for a specific article (unique [call number]) and each will modify only those groups or outcomes related to the call number in question.  Are the forms set up in a normal way that allows for this type of relationship?  The way I am wanting the form controls to function is that a user selects a 'call number' from a combo box.  After that selection he or she enters the remaining information on frmPtCharacteristics and then moves on to frmGroups and its associated forms (frmagedesc frmrace frmdisease) all the while, a filter is in place so that he or she only enters information pertaining to the original [call number] as linked to Group etc...
Of particular concern to me is the tblStudyOutcomes and the tables linking to both [OutcomeID] and [GroupNumber].  have I made these relationships appropriately?  For better understanding an outcome could be something like 'survival at 10 years' and values for each group such as 'survival at 10 years for control' & 'survival at 10 years for experimental'.

Any help would be so welcome as I find this a confusing and difficult topic.
EE-ABS-Example.accdb
0
Comment
Question by:Bevos
  • 8
  • 7
  • 2
17 Comments
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 100 total points
ID: 35155455
hi,

"I want the database to work in this way. " Don't design after you work flow. You need to design it to store the data in the correct entities and you need to use normalization to avoid different kind of errors:

http://en.wikipedia.org/wiki/Database_normalization ff.

Some rules of thumb: Don't use meaningful keys. Don't use abbreviations in table and column names. Every table should have an ID AutoNumber column, even if it is not the primary key, always named ID. Don't use special characters in table and column names as spaces, percentage signs and start names with descriptive word.

your most important requirement: "I want to describe academic articles on cancer each with a unique [Call Number]." This means you need a table, e.g.

Article:
ID Autonumber PK,
Author Text(255) Not Null,
CallNumber Number(Long Int) or Text(255) Not Null Unique,
PdfFileName Text(255) Not Null Unique,
PublishedAt Date/Time Not Null,
ShortDescription Text(255) Not Null

When you not have the file always at hand then you need to remove the Not Null constraint.  This is fully normalized, but you will get later on often a technical problem which requires a different solution.

The Author may be a problem, as the studies I know have always in most cases more then one author. Here you need to make a important decision: Is the author information important for your problem? Do you like to create later on stats based on authors? In this case you need two more tables:

Article:
ID AutoNumber PK,
CallNumber Number(Long) or Text(255) Not Null Unique,
PdfFileName Text(255) Not Null Unique,
PublishedAt Date/Time Not Null,
ShortDescription Text(255) Not Null

Author:
ID AutoNumber PK,
Name Text(255) Not Null Unique
(DOB etc..)

Article_Author:
ID AutoNumber,
idArticel Number(Long) PK FK,
idAuthor Number(Long) PK FK

Now you need you characteristics table:

Characteristic
ID AutoNumber,
idArticel Number(Long) PK FK,
your characteristics

your Group table:

Group
ID AutoNumber,
idArticel Number(Long) PK FK,
your group columns

The rest looks quite fine, with one exception starting at StudyOutcomes. Storing multiple values as you do, e.g. Primary/Secondary Outcome, 1yrPer/../5yrPer per one row may be a problem. Where do these values come from?

In short: your basic data model is okay, but not perfect.

mfG
--> stefan <--

0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 400 total points
ID: 35157966
I have to disagree with Stefan on the following point:
always named ID
This can lead to inappropriate linking because of the assumption that an ID in one table is the same as the ID in another.  Since you will have to qualify the ID when it is used as a Foreign Key, I would contend that you should qualify it in the original table, among other things, this will cause ACCESS to tend to join tables in a query based upon the identical names.

Re: Your StudyOutcomes table:
I would definitely modify that table so that you have a flag (OutcomeType) that lets you identify the Primary Outcome (OutcomeType = P), Secondary Outcome(s) (OutcomeType = S), and Independent Assessors (OutcomeType = I), with a value and a comment for each entry.  If you have more than one of a particular type of Outcome, then you should have a separate row for each of those.

I think you may need to do the same sort of refactoring for the tables PLOutcomes, SubPLOutcomes, AVEOutcomes, RegOutcomes, TTEOutcomes, and any others where you are currently anticipating using comma separated lists of something.  

By the way, you may have noticed that I have referred to table names without the tbl prefix.  That is because I seriously despise the practice of prefacing table names in that way.  You don[t preface the column names with col, do you?  If you name tables with plural nouns and columns with singlular nouns, then you know which is which.  
0
 

Author Comment

by:Bevos
ID: 35158354
Hi Stefan and 8080. Thanks for the helpful comments.  Maybe I can give a little more information which can help clarify things?  
I didn't think having the [Call Number] a meaningful field would be a problem as the key, but I will take your advice and restructure those tables to have an autokey.  I will also remove the special characters (space, slash, parenthesis).  Is underscore a special character as well?  If so I will change those too.
I like that you moved the PDF Filename to the Pt Characteristics table, I think that makes sense. As for the author thing, I don't believe it will be important (it is mainly present so that when I export these as an excel table I can have a citation with the authors there).   So I understand correctly Stefan, you are suggesting I take some of the descriptive items from tblPtCharacteristics and move them to another table (tblCharacteristics and then tblArticle for the [call number] [author] etc...)?

8080 Driver, thanks for the suggestion on auto ID field.  I think that is a sensible suggestion and I will keep the current descriptive field names as you like.  Haha, sorry about the tbl frm prefixes.  I didn't use them in earlier projects but every database I see has stuff like that (I have no formal training in this kind of design) so I just started adopting it.  For me at least, it can make things a bit easier looking at the database elements but it is a bit silly.

On to your comments about the outcomes tables.  I am VERY interested in what you have to say here (I think it was hard to make these correctly related etc...) but I am not sure I understand what you mean.  What do you mean outcometype flag? I'm not familiar with the term.  Right now, primary outcomes and secondary outcomes are multi-select boxes where you will get a list of the outcomes (such as survival, adverse events, secondary cancers etc...).  Each of the following tables will describe more fully the outcomes, but I think you've probably touched on something important and I'm not understanding.  These following outcomes will describe tblStudyOutcomes, for example tblTTEOutcomes will have a row for each combination of group and outcome ([OutcomeID and GroupID] defined for a specific [call number]).  So lets say in a record with call number of 012 I have the outcomes survival and time til tumor relapse (both TTE outcomes) and 3 groups Drug1 Drug2 Placebo I would have six rows in the TTEOutcomes table describing record 012.  Does the layout, as we've suggested thus far, allow for this to work?  At the end I basically want to be able to compare via query different [Call Numbers] characteristics and  outcomes with one another.

Thanks so much,
Bevo S.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 400 total points
ID: 35159266
Okay, let's start with the outcome flag: I would probably implement this as a single character column (OutcomeTypeCode) containing, for example, P, S, or I, that is an FK to an OutcomeTypes table (containing an OutcomeTypeID, OutcomeTypeCode, and OutcomeTypeDescription with OutcomeTypeID as PK and OutcomeTypeCode as a Unique Index).  

If you have the following columnsin the various xxxOutcomes tables, yiou can do what you are wanting to do:
xxxOutcomeID,
PtCharacteristicID (or, if you will, CallNumber)
GroupID
Outcome or OutcomeID (if you have a standard set of Outcome Descriptions, I'd make a table for those, too ;-)
Comment or Note

That layyout lets you use the various "ID" columns as FK's that link to the supporting tables.  For example, you can JOIN from the PtCharacteristics table to the TTEOutcomes via the CharacteristicID and get all of the recorded outcomes for that Call Number.  By JOINing to the Groups table, you can include or constrain the results on the information from that table.  Similarly, if you are only wanting PrimaryOutcomes, you can JOIN to the StudyOutcomes table and constrain the results by that OutcomeTypeCode.
0
 

Author Comment

by:Bevos
ID: 35165324
Thanks so much 8080 Driver! I'm going to make these changes today and then post a new screen shot of the relationship status.  After I do, could you let me know if I've gotten everything down correctly?

Thanks,
Bevo S.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165853
Bevo,

Gladly.

By the way, the handle is 8080 Diver (not Driver) . . . the reference is to the fact that I prefer SCUBA diving in 80 degree water where there is 80 feet of visibility (or greater). ;-)
0
 
LVL 32

Expert Comment

by:ste5an
ID: 35165911
hi 8080_diver,

puh, pretty hot, isn't it? I know people bathing in colder Jaccuzis .) btw, I would assume that you have such temperatures at snorkel depth... c--8)

0
 

Author Comment

by:Bevos
ID: 35165923
Haha... sorry I thought it was a thing about early intel processors.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165935
Well, i did that, too . . . but "The worst day of SCUBA diving is better than the best day in the office!" ;-)
0
 

Author Comment

by:Bevos
ID: 35181160
Hi Diver and Stefan, sorry for the late reply but I have been a bit under the weather this last week.  I made the changes we talked about and I was hoping you could give me one last confirmation that things are okay before I close out the question.

Thanks again for all your wonderful help.  I feel like I am beginning to understand relationships in Access much more than before.

Bevo S.
EE-example-relationships.bmp
EE-ABS-Example.accdb
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35183864
It's a little hard to absoultely discern which of the relationships are/should be 1 to many.  For instance if there are multiple TTEOutcomes rows potentially associated with one PtCharacteristics row, you may want to check that relationship.  Similarly, unless you are sticking with the comma delimited list in the Outcome column, I would suspect that the relationship between PTCharacteristics and PrePostOutcomes is, again, a 1 to many and not a 1 to 1.  (I you are planning on sticking with the co0mma separated list, I would highly advizse you to reconsider that. ;-)

In fact, it appears that virtually every relationship from the PtCharacteristics table is being shown as a 1 to 1 . . . and, from my experience with hiospital systems, I just have to question that.
0
 

Author Comment

by:Bevos
ID: 35183924
Thanks so much for the reply 8080 Diver, I thought these were set to 1 to many relationships! I need to go back and do the 'enforce referential integrity' to make this work.  (Note, this doesn't seem to work and now I'm stuck on how to make the relationships one to many)
On the topic of PrimaryOutcome and SecondaryOutcome how do you suggest I change this?  On the subsequent forms, the user is selecting the outcome from a combo box (single selection) but on this form they were just giving a comma separated list of all the outcomes in the study (this might go in a different table than those specific outcomes giving a brief summary of the study data).
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 400 total points
ID: 35184004
Re: PrimaryOutcome and SecondaryOutcome
Well, if it were me, I would Look at how the particular form with the comma separated list is used.  However, one of the primary points that I would be looking at is the fact that you should not be storing any data in more than one place, especially where one is a comma separated list in one column and the other is multiple rows (i.e. a non-normalized and a normalized presentation).  (Think about it.  If you have the comma separated list and the tables with the outcomes in separate rows, how do you make absolutely certain that an update to one or the other also results in the other one being updated?)

Why not present the Outcome data in the that form in a small, continuous subform?
0
 

Author Comment

by:Bevos
ID: 35184051
Ok 8080 Diver, I will try to make that change.  Do you know why my relationships aren't one to many?  I know that I stopped having Call Number be the primary key on PtCharacteristics, but I thought because it was a unique field (indexed, no duplicates) this would let me make 1 to many relationships as well?
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 400 total points
ID: 35184444
You should be able to create the 1 to many relationships.  However, you probably need to have the FK columns in a non-unique index.
0
 

Accepted Solution

by:
Bevos earned 0 total points
ID: 35225605
Hi 8080, I opened another question about this topic :) thanks for all of your help so far.
0
 

Author Closing Comment

by:Bevos
ID: 35292432
Posted for remaining issues in another question
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now