Question Creating strong relationships between tables (1 Viewer)

Hi all. I am a self taught Access user.
I am building a medical database used for research (there is no identifying information stored in this just FYI- anonymity). Can anyone give me advice if these relationships seem strong enough? Or perhaps any other ideas? (I know this is all weird information- it is for a university medical program).
Each individual has one "Intake" which is entered first which is why I have the remaining tables linked to the IntakeID.
Appreciate any feedback, guys!

Firstly welcome to AWF.
What exactly do you mean by 'strong' relationships?

You have applied referential integrity which is a good thing. Do you also have cascade update/delete?

Some general advice.
1. Do not use spaces or special characters such as () in table or field names
2. Do not use multivalued fields or attachment fields. Both will cause you major difficulties but for different reasons.
Also avoid calculated fields

Without seeing some sample anonymised data, it is hard to tell if the structure is appropriate. There are a lot of photograph fields (attachments?) which indicates your data isn't normalised

I agree with others comments re multivalue/lookup/calculated fields, spaces etc.

With regards cascade update/delete. If your ID primary keys are autonumbers there is no point in cascade updates since they cannot be changed in the first place.

With regards cascade delete, personally I would not use them - there is a risk that a user might delete in error and although there is a prompt that deleting the primary record will result in deleting all the secondary tables users can still click yes in error. Better to not allow deletes by users but have a 'deleted' flag that admin can undelete if necessary. Admin can then run a regular routine to remove those records flagged to be deleted perhaps after a review.

Looks like you might need some additional tables for lookups - (medical) type, city/state/country etc. Note that Type is a reserved word, better to use a more descriptive field name such as 'incidentType'.

Not a problem to keep separate but not sure of the benefit of having a one to one relationship to some of the other tables, just include them in the main table.

I do agree with the suggestion that the photograph fields implies a denormalised table. e.g. phtographscars - what if the cadaver has more than one scar? and not conveniently close enough to be cover with one photo? If there is an administrative requirement that specifies a particular range of photographs required, these should be listed in a separate table.

CJL makes a good point regarding cascades but there are also good reasons for their use.
I’ve written a lengthy article about relationships and referential integrity which also covers the cascade options. See http://www.mendipdatasystems.co.uk/relationships1/4594533224


Sent from my iPhone using Tapatalk

I agree with the comments and advice offered by Colin and CJ. I would also add that for one to advise more meaningfully on your relationships (and tables) we need to see a description of the "business rules" (facts) that your database is intended to support.
Welcome to the forum and good luck with your project.

I'll elaborate on "strong." All my e-learning has taught me that redundancy is frowned upon so I wasn't sure if using the IntakeID for each table was a "strong" way to build the relationships.

As for the photograph content, that's actually a yes/no question, not an attachment or actual photo. It is more or less on the form just to remind people to take each photograph (check when completed).

Each donor that comes to the facility is given a unique number (TXSTDonorNumber). Otherwise, there is no other identifier for each individual beyond the auto-numbers.

CJ & Isladogs- thank you for the imput on cascade updates. That's a really good point.
In terms of creating another table for look ups-- CJ, can you elaborate why I would want to create another table for medical? Each person can have zero to many medical conditions (same for occupational and geographic history).


I work at a University and cannot find a single person on campus that is familiar with Access-- this forum is amazing. Thanks so much for the feedback!

We don't really know how your data is coming to you but will additional "instances" from the same subject have the same IntakeID? If not, then there will never be 1-m relationships.

Also, think carefully about creating 1-1 relationships. They are extremely rare in the real world and are usually made for the wrong reason.

CJ, can you elaborate why I would want to create another table for medical?

Click to expand...

not knowing the background to the requirements of the db I was suggesting that as far as medical is concerned for the type field you may require analysis of different types of medical conditions - so better if these are consistent in terms of descriptions. Same thought applies to city/state etc

Are you aware of the reasons for avoiding multivalue fields? If not, see http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

In your Identity Form table (confusing name), is there ever more than one record for each donor? Would there ever be more than one if MVFs were scrapped? If not, use the DonorNumber as the PK field and scrap the IdentityID field. Ditto for its use as a FK in other tables.

As for 'strong' relationships, in a perhaps 'similar' example, my school data apps have PupilID as primary key in several tables as well as a foreign key in others. The table relationships include referential integrity and cascade delete. Where both tables use it as a PK field, the relationship is 1:1. Arguably, the data could be in one table with a very large number of fields but for performance reasons I split them into separate categories so only the data required for a particular query/form/report is loaded

2. Do not use multivalued fields or attachment fields

Click to expand...

you can only suggest not enforce.
even ms doesn't have such statement.

arnelgp said:

you can only suggest not enforce.
even ms doesn't have such statement.

Click to expand...

If you read the entire statement, I preceded that with 'Some general advice'.
However, that advice is very commonly given and most experienced developers will make the same point.
Of course MS won't say those features shouldn't be used but, if they are used, it is likely they will cause issues later

If you are still in the layout and design phase, I have some very general advice for you. My comments will go more towards "mindset" than specific things to do. I'll try to be brief (but those who know me probably WOULD bet against brevity.)

#1 If you can't do it on paper, you can't do it in Access

This means you need a good, thorough analysis ahead of time because Access knows NOTHING about medicine. It is a tool that builds databases. YOU will be supplying the subject-matter expertise. "Doing it on paper" almost always involves a discussion of how to approach given situations - one situation at a time - and taking really good notes.

By doing your analysis first, you save yourself from having to back-track large blocks of code. Ideally, your up-front analysis will generate some kind of document (of non-trivial size) that tells you - and anyone else who reads it - about the choices you made and why. This is your "roadmap." If this project is like a journey, you will need a roadmap - if for no other reason than to recognize when you finally reached your intended destination.

The direct meaning of the statement is simple. If you have a design guideline, you can start implementing stuff in Access. If you have no idea why you want to implement something, you are just piddling around. Don't call in Access until you know what data features you need.

#2 Access won't tell you anything you didn't tell it first.

Access isn't a search engine per se. All it can tell you is what is already inside of it. If you programmed a search feature, it would be limited to database content. Therefore, when you went through that design process (see #1 above) you laid out what you were doing. Part of that layout is defining / enumerating what you want to see. Actions specific to my #2 guideline are that for EVERY output you need to verify that you have a data source for that output. If this means working backwards through your design to verify data sources, so be it. If you wanted output XYZ, then either you need a data source of XYZ ... or you need data sources for X, Y, and Z and the formula you can use to combine them.

#3 Access is the map, your business is the territory

Your business rules should have existed long before you started this project. You do not want to change your business in any way due to this project EXCEPT for efficiency or accuracy in record-keeping. The point here is that if you have a disparity between what your business rules would do and what your database would do, the database is usually wrong. At NO TIME do you ever want the tail to wag the dog.

If you let the DB app control what you do or what your users do, it has to be because it accurately implements a model of your business. I have seen too many cases where people had to change what they did because the DB app they had couldn't track certain actions / processes. So they had to change their business flow. I have seen DB problems because the process implemented by the DB doesn't accurately reflect what the end user of the process wants to do to just get the job done.

At the same time, having done a thorough analysis of this project, you might have found a spot or two where you had to decide what to do in reality because you never thought about that situation before the analysis project. I.e. analysis can find "holes" in your business model and give you luxury of deciding ahead of time rather than do a tap-dance around something you never thought about. If you change your model (and your roadmap) because you discovered a gap, that is a case of "no harm, no foul."

The above principles are design-level guidelines to help orient your way of thinking when you work on this project. They are general in nature but based on 40+ years worth of IT work for private industry as well as U.S. Military operations.

The reasons for not using the two data types in question are different.

The Attachment data type has no correspondence in other RDBMS so if you use it, you will need to make application changes if you ever want to move the BE to SQL Server or some other RDBMS.

The Multi-value fields were added as a crutch for people who didn't know how to create this type of 1-many relationship by themselves. There are implementation anomalies (as referenced in the link) and custom SQL that also will not work if you ever have to upsize.

Since both have alternatives, it is probably best to avoid them.

I work at a University and cannot find a single person on campus that is familiar with Access-- this forum is amazing

Click to expand...

Find someone who understands SQL Server. a Relational database is a relational database. Access has been left to languish in this regard so it hasn't keep up with newer features that have been added to other RDBMS' in the past 25 years but at the schema design level, if you wouldn't do it in SQL Server, you wouldn't do it with Access (actually Jet or ACE are the database engines and Access is the application development platform)

That's a good article-- all my e-learning definitely did not warn about MVF confusion. In fact they encourage it.

I think you read the names of my tables wrong. It's an "Intake Form." Each individual who donates their body to our program has ONE "Intake Form" and is assigned a "TXSTDonorNumber." From here, they only can have ONE "Life history" and ONE "Facts of death." However, each individual can have MANY "Medical history," (for each medical issue they have, it is a new entry) "Occupation history," (for each job they have, it is a new entry) and "Geographic history" (for each location they've lived, it is a new entry).

I guess I could still you the TXSTDonorNumber as the PK and create those same 1:1 and 1:many relationships using it, right?-- it would be easier for entry purposes since the autonumber (in this case the IntakeID) is just arbitrary.

I agree-- it sounds like your Pupil tables are connected in a similar manner.

Thank you IslaDogs & everyone!

Pat Hartman said:

We don't really know how your data is coming to you but will additional "instances" from the same subject have the same IntakeID? If not, then there will never be 1-m relationships.

Also, think carefully about creating 1-1 relationships. They are extremely rare in the real world and are usually made for the wrong reason.

Click to expand...

In this case, I really do think it is a 1:1 relationship. For instance "Facts of Death" is basically death certificate information... A person can only die once and if the same donor has two "Facts of Death" this would cause very unreliable research. Same goes for "Life History" information. One donor can only have ONE "Life History."

Pat Hartman said:

The reasons for not using the two data types in question are different.

The Attachment data type has no correspondence in other RDBMS so if you use it, you will need to make application changes if you ever want to move the BE to SQL Server or some other RDBMS.

The Multi-value fields were added as a crutch for people who didn't know how to create this type of 1-many relationship by themselves. There are implementation anomalies (as referenced in the link) and custom SQL that also will not work if you ever have to upsize.

Since both have alternatives, it is probably best to avoid them.

Find someone who understands SQL Server. a Relational database is a relational database. Access has been left to languish in this regard so it hasn't keep up with newer features that have been added to other RDBMS' in the past 25 years but at the schema design level, if you wouldn't do it in SQL Server, you wouldn't do it with Access (actually Jet or ACE are the database engines and Access is the application development platform)

Click to expand...

I don't even really understand SQL language. HA! I mean, I'm starting to understand the theory and how it's what actually makes Access tick, but that stuff is still over my head. Access is such a powerful tool-- I'm just shocked more people (like a major university) don't use it. Meanwhile people be creating Excel spreadsheets and I'm just over here :banghead:

Hi LaneyRenee
Your last post was moderated for some reason. Posting this to trigger email notifications.

A small bit of advice - where you aren't quoting a particular member, it isn't always obvious which post you are replying to. I think post #14 was replying to me but I may be wrong. I said the table name Intake Form was confusing as its a table...not a form.
If the IdentityID field is superfluous, I would probably scrap it.
If there was something else I should have replied to, do let me know

It isn't wrong to create 1-1 relationships, it is simply that the data could be all stored in the same table. Everything in the first table occurs once as well as everything in the second table. Hence both sides of the relationship are 1's. In the case of the medical history, many events can be recorded and so that qualifies as a "m".