Access Relational Structure?

[Chrisb1221]

Hello, I have read quite a bit about the "normalized forms" and how to implement them. I have worked in IT for over 10 years and recently made the jump to building a DB from scratch. The organization I work for currently stores all data in excel. I have been normalizing the data, and slowely merging it into Access. I just hit my first major wall today with the 32 index/R.I. relationships. I would like to know if this is due to bad design, or am I just trying to use Access for a SQL job.

Basically I have an Individual table "One" Table which holds relative info such as:
ID (PK), Name, Date of Birth, etc.

Then I have many "Many" tables that link back to ID as a FK.
Examples of those tables are:
Assessment Participation
Trial Participation
Program Participation
Assessment1Data
(through)
Assessment15Data


Insurances
Birth History
Allergies
Medical History
Many other "Many" tables linking back to ID in the Individual table

I have looked at many "Sample" databases, and they are definitely not built with all the relationships going to one table. Currently my db has over 50 tables in it, not all of the linking back to the "Individual" table, There is also a few "Detail" tables, and a "Family" table for family specific information. I basically went in to building the DB thinking as long as I created "one to many" relationships I should be in good shape...

So back to my question from above, does this seem like a plausible schema and I have outgrown Access, or should I go back to the drawing board?

[Stingaway]

I haven't hit the 32 indexes limitation before, though typically I go on "gut feel" when deciding Access vs. SQL.

There are some who would say that you need to check your normalization (my first thought), but then others who legitimately run into issues based on the complexity of their table design and process requirements, so that said you may need to do a bit of research to decide which issue you are running into.

There is an similar discussion about it that can be found here which offers the above and some creative suggestions around it:
http://stackoverflow.com/questions/1...imit-in-access

Hope this helps.

[Chrisb1221]

Thank you for your reply Stingaway! I did actually read that link prior to posting. It is possible for me to remove some of the referential integrity and validate it through forms. I'm not necessarily looking for a workaround, I am most curious in finding if I have not designed the structure properly. I would be willing to provide a copy of the database I built, after removing some of the business identifying specific information.

[Stingaway]

Sure, post it up I will take a look.

[Chrisb1221]

Sure, post it up I will take a look.

The design seems to work for what we have used it for at this point. This is the first database I have built. I use Infopath as a front end and have been using this system for a few months with the parts of the database that have been merged. I appreciate any feedback.

[CraigDolphin]

Just a brief comment: I think Stingaway's first instinct is correct. I strongly suspect you haven't quite got the normalization figured out.

Bear in mind, you haven't explained anything about the real world situation that your table structure is attempting to model, but based solely on the table names you've listed I see four types of information:

A.Participation
B.History
C.AssessmentData
D.Insurance

I would bet that the fields in each of your AssessmentData tables are all (mostly) the same names/types of information. You could replace those 15 tables with two:
1.AssessmentData
2.AssessmentType

The AssessmentData table would have all the fields that are common to all 15 tables, plus one extra 'AssessmentTypeID' FK field that is linked to the PK of the AssessmentType table.

And possibly, you may find that if you are measuring different metrics in different assessments you might need to go further with normalizing the data.

Perhaps something like:
table AssessmentEvent
-AssessmentEventID (pk, auto)
-AssessmentTypeID (fk)
-ID (fk to your 'One' table which looks like it should be named something like client, or subject, or person, or whatever)
-AssesmentEventDate (maybe?)

table AssessmentType
-AssessmentTypeID (auto, pk)
-AssessmentType (text)

table AssessmentResults
-AssessmentResultID (auto,pk)
-AssessmentDataEventID (fk)
-MetricID (fk)
-MetricValue

table Metrics
-MetricID (auto, pk)
-MetricName (text)

This structure allows you to add only the metrics that apply in each stage of the assessment process, and you're adding rows, not fields. This means that if different metrics are used in the future, you won't be having to add new fields to tables, modify forms to accomodate the new fields, or rewrite code etc.

I strongly suspect that a similar analysis might identify a much more normalized structure would be possible for the other areas I mentioned at the start. And it may also turn out that your other tables aren't quite hitting the nail on the head either.

Perhaps it would be most helpful to provide a plain english explanation of what kinds of data you are trying to track -- on a general level -- and what you hope to be able to do with it. That should help scope out what your table structure needs to be.

[Chrisb1221]

Just a brief comment: I think Stingaway's first instinct is correct. I strongly suspect you haven't quite got the normalization figured out.

Bear in mind, you haven't explained anything about the real world situation that your table structure is attempting to model, but based solely on the table names you've listed I see four types of information:

A.Participation
B.History
C.AssessmentData
D.Insurance

I would bet that the fields in each of your AssessmentData tables are all (mostly) the same names/types of information. You could replace those 15 tables with two:
1.AssessmentData
2.AssessmentType

The AssessmentData table would have all the fields that are common to all 15 tables, plus one extra 'AssessmentTypeID' FK field that is linked to the PK of the AssessmentType table.

And possibly, you may find that if you are measuring different metrics in different assessments you might need to go further with normalizing the data.

Perhaps something like:
table AssessmentEvent
-AssessmentEventID (pk, auto)
-AssessmentTypeID (fk)
-ID (fk to your 'One' table which looks like it should be named something like client, or subject, or person, or whatever)
-AssesmentEventDate (maybe?)

table AssessmentType
-AssessmentTypeID (auto, pk)
-AssessmentType (text)

table AssessmentResults
-AssessmentResultID (auto,pk)
-AssessmentDataEventID (fk)
-MetricID (fk)
-MetricValue

table Metrics
-MetricID (auto, pk)
-MetricName (text)

This structure allows you to add only the metrics that apply in each stage of the assessment process, and you're adding rows, not fields. This means that if different metrics are used in the future, you won't be having to add new fields to tables, modify forms to accomodate the new fields, or rewrite code etc.

I strongly suspect that a similar analysis might identify a much more normalized structure would be possible for the other areas I mentioned at the start. And it may also turn out that your other tables aren't quite hitting the nail on the head either.

Perhaps it would be most helpful to provide a plain english explanation of what kinds of data you are trying to track -- on a general level -- and what you hope to be able to do with it. That should help scope out what your table structure needs to be.

CraigDolphin I really appreciate you taking the time to explain in such detail. I do see a few flaws in my design based on your explanation. I am taking existing data trying to make access bend to it. As I stated previously thus far the design I posted has been working to properly store and retrieve data using Infopath as a front end. I will take your explanation to heart and think about how my data relates.

[Stingaway]

Quote from the poster on the back of my door:

"The only glory most of us have to hope for is the glory of being normal." - Katherine Fullerton Gerould

If you haven't seen the five rules of data normalization poster, it's a must read...

[ssanfu]

If you haven't seen the five rules of data normalization poster, it's a must read...

Here are the rules (without the poster - sorry):

Rule 1: Eliminate Repeating Groups. If you have a set of fields that have the same name, followed by a number (such as skill1, skill2, skill3), remove these repeating groups and create a new table for them.

Rule 2: Eliminate Redundant Data. Don't store the same data in two different locations. This can lead to update and delete errors. If the same data element is stored in two places, remove the second data element, create a new table with the element and a key field, and then place the key field in the locations that used to hold the data element.

Rule 3: Eliminate Columns not Dependent on Keys. If you have data elements that are not directly related to the primary key of the table, these elements should be removed to their own data table. Only store data elements that are directly related to the primary key of the table.

Rule 4: Isolate Independent Multiple Relationships. Use this rule to improve database design when you are dealing with more than one one-to-many relationships in the database. Before you add a new field to a table, ask yourself whether this field is really dependent upon the other fields in the table. If not, you should create a new table with the independent data.

Rule 5: Isolate Related Multiple Relationships. Use this rule to improve database design when you are dealing with more than one many-to-many relationship in the database. If you have database rules that require multiple references to the same field or sets of fields, isolate the fields into smaller tables and construct one or more link tables that contain the required constraints to enforce database integrity.

[Stingaway]

Ideally these are read aloud each day in a ceremony, with reverence and humility.

[CraigDolphin]

Ideally these are read aloud each day in a ceremony, with reverence and humility.

You people are Strange








And here i thought I was the only one