Bevos
asked on
Access 2007: Table Relationships Normalization Assistance:
Hello, several members of this site have been helping me try to make the attached form Normalized. I've made many changes, but I'm still not there yet. In particular, I'm not sure I understand how relationships should be created between the tables. Before, I simply had several 'large, non-normalized tables' which were related to one another based on the unique identifier [Call Number].
Now, I have many smaller, hopefully normal, tables.
The relationships should be as follows:
Each table, with the exception of tables which exist only to populate combo/list boxes and do not have [call number], is related by the identifier call number.
The table tblPtCharacteristics will be modified first, and after this, patients will move onto tblGroup and describe subsets of the population in tblPtCharacteristics.
TblGroup is related to the subsequent tables of tblRaceDesc, tblDisease, and tblStageCatDesc.
through the [Group] field.
TblDiseaseDesc is also related to tblStageCatDesc because it will describe a specific [Group], and [Disease].
I'm attaching an image of how these are laid out now in the relationships window (I am very new to this part so any help would be greatly appreciated.)
In addition to these relationships i have 12 'unrelated' forms that are used to populate some of the fields in this form via lookup values in table design. How should these be handled in normalization?
As always thanks again for all the help and advice,
Bevos
EE-ABS-Example.accdb
Now, I have many smaller, hopefully normal, tables.
The relationships should be as follows:
Each table, with the exception of tables which exist only to populate combo/list boxes and do not have [call number], is related by the identifier call number.
The table tblPtCharacteristics will be modified first, and after this, patients will move onto tblGroup and describe subsets of the population in tblPtCharacteristics.
TblGroup is related to the subsequent tables of tblRaceDesc, tblDisease, and tblStageCatDesc.
through the [Group] field.
TblDiseaseDesc is also related to tblStageCatDesc because it will describe a specific [Group], and [Disease].
I'm attaching an image of how these are laid out now in the relationships window (I am very new to this part so any help would be greatly appreciated.)
In addition to these relationships i have 12 'unrelated' forms that are used to populate some of the fields in this form via lookup values in table design. How should these be handled in normalization?
As always thanks again for all the help and advice,
Bevos
EE-ABS-Example.accdb
If tables are unrelated, then by definition, you could consider them normalized or that it is not possible to normalize them.
Talk to me a bit about your application/project. Medical study? Historical archiving? Just help me understand the big big broad picture (all technical points layed aside). This is important before I can help you normalize.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello everyone thanks for your timely and informative responses. To address a question raised by some people "What is the purpose of this database?"
The purpose is to track data from academic literature on medical conditions (primarily cancer) for subsequent analysis.
So the unit of measurement in this is the [Call Number] which corresponds uniquely to each piece of academic literature.
The other variables listed in this table describe characteristics of the literature pertaining to the patient population. So within each article there are several groups, defined by [Group].
This could be treatment/control or other distinguishing aspects of the study (this bunch of patients got 1.5g of medication x, etc...) and these can vary extensively by study.
The other tables contain information about each group, so for example tblRaceDesc will have a description of each race within a group (i.e. in [call number] 00001 we have a treatment [group] composed of 10 Hispanic persons and 10 White persons).
I've tried to remove all redundant data to make the forms normal and [Call Number] is how each table will be related. The goal after all of this is done is to control tables based on the sum of article information collected. For example you should report the number of persons with a [disease] being stage IV in a group of studies etc... The hope is that if data is collected in this way it can make it easier to write up a paper.
Please let me know if I should try to explain more, or did a poor job explaining.
Any advice is helpful and thanks again, Bevo
The purpose is to track data from academic literature on medical conditions (primarily cancer) for subsequent analysis.
So the unit of measurement in this is the [Call Number] which corresponds uniquely to each piece of academic literature.
The other variables listed in this table describe characteristics of the literature pertaining to the patient population. So within each article there are several groups, defined by [Group].
This could be treatment/control or other distinguishing aspects of the study (this bunch of patients got 1.5g of medication x, etc...) and these can vary extensively by study.
The other tables contain information about each group, so for example tblRaceDesc will have a description of each race within a group (i.e. in [call number] 00001 we have a treatment [group] composed of 10 Hispanic persons and 10 White persons).
I've tried to remove all redundant data to make the forms normal and [Call Number] is how each table will be related. The goal after all of this is done is to control tables based on the sum of article information collected. For example you should report the number of persons with a [disease] being stage IV in a group of studies etc... The hope is that if data is collected in this way it can make it easier to write up a paper.
Please let me know if I should try to explain more, or did a poor job explaining.
Any advice is helpful and thanks again, Bevo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Boag, I think the relationship between each of the tables and tblptCharacteristics will be 'one to many.' Each of these related tables can have many rows describing different groups and characteristics of groups for each [call number] but there will only be 1 entry in the master tblPtCharacteristics.
On the subject of primary key, I didn't know if I could have a primary key in each table as [Call Number] because in the other tables, for example tblDiseaseDesc, there might be many of the same [Call Number]. Because of this, should I make another field, such as an auto number field, to be the primary key for these tables? Please let me know what would be good practice.
Thank you,
bevo
On the subject of primary key, I didn't know if I could have a primary key in each table as [Call Number] because in the other tables, for example tblDiseaseDesc, there might be many of the same [Call Number]. Because of this, should I make another field, such as an auto number field, to be the primary key for these tables? Please let me know what would be good practice.
Thank you,
bevo
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the great advice everyone! I've had exams so I haven't been able to look at this post until today. I spent some time thinking about what was said and redesigned my tables. Call Number is now only present in the tblGroup which relates to the primary table tblPtCharacteristics. 4 other tables (tblMale, tblAgeDesc, tblRaceDesc, tblDiseaseDesc) describe groups currently and are associated with the [GroupNumber] attribute in tblGroup. 1 table now describe disease which is stage/category which is related to the primary key of tblDiseaseDesc [DiseaseID]. I created a new primary key for each of the subordinate tables to tblGroup and enforced-referential integrity on a one-to-many relationship.
Right now, I see the user going through this table first entering the characteristics of the book/paper in tblPtCharacteristics. Next, they would define groups in tblGroup (i.e. Adult Control Pts, Adult Experimental Pts, Senior Citizen Control Pts... or any other example). Then they would select a group and enter other attributes, racial composition, age, ratio men to women, diseases stages (with further description on category/stage).
My big question now is do you think I've normalized this thus far? Does the data relate to one another like I've described? At the end, I want to generate an excel or word table output that is filtered on some of the categories (like disease, group, study design etc). The data would be nested such that it would be:
Call Number contains Groups which contain attributes describing the patient and disease characteristics.
Here is an image of the associated relationships.
Thanks again for all the help!
Bevo
ee-relationships-2.bmp
Right now, I see the user going through this table first entering the characteristics of the book/paper in tblPtCharacteristics. Next, they would define groups in tblGroup (i.e. Adult Control Pts, Adult Experimental Pts, Senior Citizen Control Pts... or any other example). Then they would select a group and enter other attributes, racial composition, age, ratio men to women, diseases stages (with further description on category/stage).
My big question now is do you think I've normalized this thus far? Does the data relate to one another like I've described? At the end, I want to generate an excel or word table output that is filtered on some of the categories (like disease, group, study design etc). The data would be nested such that it would be:
Call Number contains Groups which contain attributes describing the patient and disease characteristics.
Here is an image of the associated relationships.
Thanks again for all the help!
Bevo
ee-relationships-2.bmp
I would not agree with keeping study-specific details in the table with book-specific information. Author, call number, etc. belongs in a table for books/publications. Type of study, Time frame of study, etc. belongs in another table. You can add a groupID to the book/publication table and get rid of the call number in the groups table. That being said, if you ever have more than one study of interest in a single book, you are sunk. So, to prepare for such an eventuality just drop in a bridge-table (TblStudy perhaps) to bridge books and groups. It would have call number and group id attributes (note: you would leave the group_id out of the book table in this case and still remove the call number from the groups table). Those two keys in the study table would tie any given study to its corresponding book and group.
ASKER
Hi sl8rz, thanks for the information. For this database book/study are equivalent things just different terms. So I don't think I need to change that first table (each of those categories will correspond 1:1 with [Call Number]). Based on your feedback I'll close the question now. I'm bound to have a bunch of other issues as this thing develops and I'd be very welcoming to any of the commenters' continued feedback.
Thanks
Bevo S.
Thanks
Bevo S.