Question about Relationships 1-to-Many or 1-to-1 (1 Viewer)

[DBApprentice]

Hello again!

Everytime I am staring at my DB´s relationships I get a feeling that I am creating them wrongly. The screenshot is attached.

For instance, the relationship between tbl1Ratings and tbl1Cards, I related the RatingID from tbl1Ratings to the RatingFK on tbl1Cards.

But I sense that I should use a third table to establish the relationship correctly, because a Card from tbl1Cards can have one and only 1 Rating, but a Rating can be applied to one or more cards. Access seems to daunt me to understand it because on the relationship screen it is linking the 1 side of the relationship from RatingID and the many side is coming from Cards´s RatingFK. :banghead:

Sometimes I think they even should be 1-to-1 but I can´t be sure about it and this very same feeling applies to Categories, StatusCards, Areas, Departments and Companies.

Am I building it correctly or I should follow my instintics?

 

[isladogs]

Having one to many relationships is absolutely fine though it may well be that your table structure could be improved / simplified.
I haven't attempted to look at the diagram in detail but here are some quick observations which may help you clarify your views.

1. TblCards includes a MVF. These are a bad idea and their use will cause you problems
2. TblFeedbacks is at the 'arrow end' of two outer joins. That could cause issues.
3. You have 7 tables with just two fields including a PK. Six of those tables only link to one table.
Do you really need all those tables? In fact, ask yourself whether you need any of them unless you intend to add additional fields later.

 

[plog]

Agree with isla. The issues you've asked about (1 to many) you've done correctly. But things you didn't ask about seem to have issues.

All those 2 table fields should probably go (1 real field + autonumber). Instead, just store whatever value is in those tables in the table it relates to. For example, instead of tbl1Areas, simply store whatever is in the Area field in tbl1Areas in tbl1Cards. Replace AreaFK with an Area field and just store the value.

I see 2 other issues (in addition to isla's 3):

4. Ciruclar paths. Starting at tbl1Cards I can trace 2 ways to get to tbl1TeamSupervisors. That is incorrect. There should be only 1 way to travel from 1 table to another in a relationship. To fix this, you need 2 instances of tbl1Departments in your Relationship. 1 relating only to tbl1Cards and 1 relating only to tbl1TeamSupervisors.

5. tbl1Emails & tbl1Phones should be combined. All 4 of those tables on the far right side of your relationship should become 2 tables. These are the fields those new tables should have:

tblCorrespondence
CorrespondenceID, autonumber, primary key (replaces EmailID and PhoneID)
TeamSupervisorFK, number, same field in both tables now
CorrespondenceInfo, text, replaces EmailAddress and PhoneInfo fields
CorrespondenceTypeFK, number, foreign key to other new table

tblCorrespondenceType
CorrespondenceTypeID, autonumber, primary key
CorrespondenceMedium, text, will determine if Email or Phone
CorrespondenceType, text, will hold whats in EmailType and PhoneType now

 

[DBApprentice]

Hi, Colin!

1. TblCards includes a MVF. These are a bad idea and their use will cause you problems

Does MVF stand for Multi Value Fields? The Team Supervisor field is not MVF, at least not intended to. The user should pick just one supervisor for each feedback...

3. You have 7 tables with just two fields including a PK. Six of those tables only link to one table.
Do you really need all those tables? In fact, ask yourself whether you need any of them unless you intend to add additional fields later.

These fields are intended to be editable by user later, so they can add more. Is it OK to have a table with so little information? If it is not how should I work it around?

Plog, thanks for the input too

All those 2 table fields should probably go (1 real field + autonumber). Instead, just store whatever value is in those tables in the table it relates to. For example, instead of tbl1Areas, simply store whatever is in the Area field in tbl1Areas in tbl1Cards. Replace AreaFK with an Area field and just store the value.

If I store Areas in the tbl1Cards will it be possible for users to add new items later? The same would apply for the rest of the similar tables.

4. Ciruclar paths. Starting at tbl1Cards I can trace 2 ways to get to tbl1TeamSupervisors. That is incorrect. There should be only 1 way to travel from 1 table to another in a relationship. To fix this, you need 2 instances of tbl1Departments in your Relationship. 1 relating only to tbl1Cards and 1 relating only to tbl1TeamSupervisors.

I am not sure if this is what you mean here but I have included another called tbl1Departments1 table on the relationship window and I have related tbl1TeamSupervisors to tbl1Departments and left tbl1Departments1 unused. At first I toutght it was a mistake from my side when establishing the relationships.

5. tbl1Emails & tbl1Phones should be combined. All 4 of those tables on the far right side of your relationship should become 2 tables.

I have followed your instructions! Indeed it is simpler that way!

 

[The_Doc_Man]

I want to answer your question in a different direction - the 1-to-1 relationship. This is a very rare bird indeed. The theory of normalization says that in a properly normalized table, everything in the table depends only on the primary key. If the PK is a proper key, then from a data modeling viewpoint, the non-PK fields are properties of the specific data entity selected by the PK.

But then for the 1:1 case, we have TWO tables that are related by their PKs - AND they are the same PK. So the question would be: Are the things in the second table ALSO properties of the thing selected by the same PK? If so, why are the separate? If not, why is it a 1:1 relationship? (These are rhetorical questions that must be answered on a case-by-case basis.)

There actually are some valid 1:1 cases, but the discussion gets complex because it is an exceedingly rare situation.

A) Because of field size/count limits, it is at least theoretically possible for you to have more than 254 other fields associated with a given PK. It would have to be the most incredibly unique situation I have ever come across for that to happen because having that many fields suggests a problem with the original model. In 99.9%+ of all cases where I have seen this claim, the table was incompletely normalized.

B) For security reasons, you do not include certain data in the main table because the data should not be easily accessible to the general public. As a precaution, you split aside the sensitive stuff to a 1:1 table where code can get to it but hackers might not realize it is there.

A common misuse of 1:1 table relationships occurs when it is possible that a matching record doesn't exist in the second table. But that case is always mistakenly referred to as 1:1 when in fact it should be 1:many - where "many" includes zero as a possible number. Otherwise, if you have a missing record from one table, that PK value will never show up in a 1:1 query.

So ... in summary, your work-horse relationship is 1:many or many:1, and reserve 1:1 for really rare situations.

 

[isladogs]

The 'CopyOfCard' field in tblCards is a multivalued field (MVF)

Most of the 2 field tables are probably redundant in my view e.g. tblAreas/tblCategories/tblEMailType/tblPhoneTypes
Instead use the non-PK field in the main corresponding table

Having said that, if the 2 field tables will be added to later, then I would keep them as separate tables. However, you should be adding all required fields in the first phase of the design process anyway.

Personally, I would have kept the tblEMails & tblPhones as 2 separate tables as there is no reason why a specific email & phone number would always be paired

I agree with Doc's comments about 1:1 joins
In general, tables with 1:1 joins can often be combined

 

[DBApprentice]

The 'CopyOfCard' field in tblCards is a multivalued field (MVF)

I did not noticed that... But then how can I keep a copy of the card in my database? It will not be every single card just the ones awarded as best cards.

Most of the 2 field tables are probably redundant in my view e.g. tblAreas/tblCategories/tblEMailType/tblPhoneTypes
Instead use the non-PK field in the main corresponding table

Having said that, if the 2 field tables will be added to later, then I would keep them as separate tables. However, you should be adding all required fields in the first phase of the design process anyway.

I am sorry if I being too dumb or maybe my English skills are failing me here: I am lost on this answer... You mean I should have them as they are?

The reason I have an Area table is because if tomorrow the ship is comissioned to have a new area , let´s say Smokers Room so I could add them to the DB using that table. It also will be used on reports so that we can assess what areas a safer or more dangerous and also check what areas receives less feedback from supervisors. The same goes for Department and Category.

I see your point of being redundat on StatusCards/StatusUsers, because they will always be 3 I can include them on the tbl1Cards and declare their values in there. Is that the right approach?

The Tbl1TeamSupervisor is a tricky thing for me because it does not have the name of a real person, it is the name of the position in charge for the department and with authority to provide the feedback on the card.For instance OIM, Maintenance Leader, Campboss, Chief Officer or Chief Engineer. Again it may be necessary to add more positions as time passes. But the relation between Department and TeamSupervisor is the weird thing for me and I am not sure if they should be handle via a third table with just two fields TeamSupervisorFK and DepartmentFK. How should I approach it?

I am sorry if I am being a pain with all this comings and goings.

 

[DBApprentice]

I want to answer your question in a different direction - the 1-to-1 relationship. This is a very rare bird indeed. The theory of normalization says that in a properly normalized table, everything in the table depends only on the primary key. If the PK is a proper key, then from a data modeling viewpoint, the non-PK fields are properties of the specific data entity selected by the PK.

But then for the 1:1 case, we have TWO tables that are related by their PKs - AND they are the same PK. So the question would be: Are the things in the second table ALSO properties of the thing selected by the same PK? If so, why are the separate? If not, why is it a 1:1 relationship? (These are rhetorical questions that must be answered on a case-by-case basis.)

There actually are some valid 1:1 cases, but the discussion gets complex because it is an exceedingly rare situation.

A) Because of field size/count limits, it is at least theoretically possible for you to have more than 254 other fields associated with a given PK. It would have to be the most incredibly unique situation I have ever come across for that to happen because having that many fields suggests a problem with the original model. In 99.9%+ of all cases where I have seen this claim, the table was incompletely normalized.

B) For security reasons, you do not include certain data in the main table because the data should not be easily accessible to the general public. As a precaution, you split aside the sensitive stuff to a 1:1 table where code can get to it but hackers might not realize it is there.

A common misuse of 1:1 table relationships occurs when it is possible that a matching record doesn't exist in the second table. But that case is always mistakenly referred to as 1:1 when in fact it should be 1:many - where "many" includes zero as a possible number. Otherwise, if you have a missing record from one table, that PK value will never show up in a 1:1 query.

So ... in summary, your work-horse relationship is 1:many or many:1, and reserve 1:1 for really rare situations.

Thanks for the explanation, The_Doc_Man.

I am trying to relate tbl1Cardsto tbl1Feedbacks but Access keeps pushing for the 1-to-1. I don´t know what to do to make it to be 1-to-many.

 

[isladogs]

You need a separate record for each item in place of the MVF - this almost certainly means a separate table for handling that

I can't give a direct answer to the question about all those 2 field tables.
I'm sure some of them aren't necessary but others may need to stay.
Finalise the design for each of those entities and then decide each on its own merits

For example, tblCompanies will almost certainly need additional fields including address, postcode etc - if so, that means the table should remain separate.
However, both tblPhoneTypes & tblEMailTypes seem to be tables that are superrfluous.

I see your point of being redundant on StatusCards/StatusUsers, because they will always be 3 I can include them on the tbl1Cards and declare their values in there. Is that the right approach?

From your description, I would say yes.

As for departments/supervisors, I would normally include the supervisors details in tblDepartments: DeptID, Dept, TeamSupervisorID but not TeamSupervisor.
That assumes one supervisor per department
I would have a separate tblStaff (is that your tblUsers?) from where I would get the matching staff name for the team supervisors

However, that won't work if you have multiple supervisor records for the same department
In which case you could have 3 tables: tblDepartments, tblStaff & a junction table tblDeptStaff

I suggest you review the design then add some dummy records to see how well it works. Following that, review again. Possibly post here at that point

BTW your English is very good - far better than many of us who have English as our first language.

 

[DBApprentice]

I am very excited with this project because it will be my very first Access DB but at the same time I get frustraded because I cannot harness Access yet.

I suggest you review the design then add some dummy records to see how well it works. Following that, review again. Possibly post here at that point

Colin, I understand it would be hard for you to understand my project. During the begining of the project I have created a briefing about it using the Hernandez DB Design Process and - if you don´t mind -you to take a look at it.

Your insights and advice would be very much appreciated and could indicate me I am on the right path with the project.

Other Access World Forum Masters are welcome to!

BTW your English is very good - far better than many of us who have English as our first language.

Thanks a lot!

Have a happy new year!

 

[The_Doc_Man]

Access keeps pushing for the 1-to-1

Are you using some kind of table-building wizard here? Access can be told to "damn it all" MAKE that a 1:many field. You can override its suggestion. A 1:1 relationship in any practical database that doesn't involve privacy or security issues almost NEVER is justified. (I DID say "almost.") If you were splitting the table into parts for size issues then you have a design problem. If it was for legal issues of privacy or security, it MIGHT make sense - but what you described doesn't seem to go that way. So I don't know what to tell you except that it shouldn't be necessary to go 1:1 for anything I've seen so far in your posts.

By the way, here is a thought for you. There is NO PRACTICAL DIFFERENCE in the behavior of Access for 1:1 and 1:many fields when the "many" side always and only has exactly 1 record corresponding to the "1" side. So even if Access somehow is telling you it should be 1:1, you can get the same effect with 1:many anyway.

The difference in behavior between 1:1 and 1:many comes about when you have any number EXCEPT 1 record on the "many" side. In a true 1:1 relationship, if both sides were declared consistently for that relationship to exist, if you had 2 records, 1 of them could not have been stored because the "1" side of a relationship requires a unique key (no duplicates).

 

[DBApprentice]

Are you using some kind of table-building wizard here?

No. I have made the table using the table design and I am using the Relationships window to link the fields from one table to the other.

If you were splitting the table into parts for size issues then you have a design problem. If it was for legal issues of privacy or security, it MIGHT make sense - but what you described doesn't seem to go that way. So I don't know what to tell you except that it shouldn't be necessary to go 1:1 for anything I've seen so far in your posts.

The reason I made it on a separte table is because I have found an example on RogerAccessLibrary with a similar situation and I thought to be usefull in my case. But seeing your explanation I see that this is not a good thing.

Everything I have on the tbl1Feedbacks can reside on the tbl1Cards, despite the fact that many cards won´t have a feedback they in reality belong there.

 

[DBApprentice]

I can't give a direct answer to the question about all those 2 field tables.
I'm sure some of them aren't necessary but others may need to stay.

Colin, now I know how to call those mini tables, they are lookup or static tables!

The reason I am using it is because they will seldom change, but they can. On the forms the user will pick-up them when adding the card information.

All of them are to be lookup tables on this DB only:

1. tbl1Areas
2. tbl1Categories
3. tbl1StatusCard
4. tbl1Ratings
5. tbl1Companies
6. tbl1Departments
7. tbl1StatusUsers
8. tbl1AccessLevels
9. tbl1TeamSupervisors
10. tbl1ContactType

Specifically in this DB they don´t have to evolve to contain more data.

 

[isladogs]

I was aware that these were intended to be lookup tables to assist users to make selections from combo boxes.
That is a very common approach used by most developers including myself.

However, I'm still dubious that you need so many of them - especially as so many have just two fields including an autonumber PK. Your design has obviously changed since post #1 as tbl1ContactType is new.

I've read your database outline which is very clear.
I still think perhaps wrongly, that you have too many tables.
I don't know what 'Hernandez' refers to however.

If you upload a stripped down copy of your database, I'm sure several people will look at it and give suggestions about the overall structure

Out of interest why do you have a 1 in each table name?

 

[The_Doc_Man]

Diogo, I want to commend you. I am not saying that your choices are right or wrong, but rather you are showing us that you are learning things. Some people take offense when we criticize a design. You have taken our advice and examined it more closely. This openness to modifications is an important factor, particularly when designing something.

Colin's comments about "too many tables" is appropriate only in a limited situation. If you have a static / definition / translation table and you take a coded value as your PK but have a longer text description, that is PERFECTLY normal. I've had them dozens of times. In my biggest project, I must have had a dozen different tables like that because of the U.S. Navy's way of compartmentalization. Particularly when the translation is long, having a two-field translator table is OK (in my book).

Your analysis involving cards and feedback also is important because you are taking a second, harder look. THIS is the time when you want to take the time to get it right, because errors in the design phase come back to haunt you frequently and painfully.

 

[DBApprentice]

I was aware that these were intended to be lookup tables to assist users to make selections from combo boxes.
That is a very common approach used by most developers including myself.
However, I'm still dubious that you need so many of them - especially as so many have just two fields including an autonumber PK.

What would you recommend me to do with them?

Your design has obviously changed since post #1 as tbl1ContactType is new.

I followed Plog´s advice on how to treat them. However when you responded that the other approach is better I decided I would change it later.

I don't know what 'Hernandez' refers to however.

He is the writer of "Database Design for Mere Mortals" and he describes a process to design tables for n00bs.

If you upload a stripped down copy of your database, I'm sure several people will look at it and give suggestions about the overall structure

It is attached.

Out of interest why do you have a 1 in each table name?

The "tbl1" are the tabled I have defined on the begining of the DB design, "tbl2" will held information about the history and audit, "tbl3" should held the "Many-to-Many" juntion tables, but I have got to this DB conundrum when I was trying to define them and decided to post...

 

[The_Doc_Man]

After thinking about it more, there is ONE alternative to the list of translator tables, but it takes some setting up. I'll try to explain so you can see one usable alternative.

If EVERY translator table is autonumbered, then by implication the actual values of the keys do not matter. That is because autonumbering is essentially a provider of what is called a "synthetic key." It gets built on the fly with no implied meaning other than that it is a unique number used to identify a record. (To see more on this, search the forum for discussions on "synthetic" vs. "natural" keys.)

Now here's where an alternative creeps in. If you have a translator table, you can have the PK in one field and a value in another field. If that table is then essentially static, you can add a third column to the table for another translator that otherwise would have had an autonumber and a text field. And you can add other translator columns as needed. The trick is that the number of records has to provide records to match the table with the most possible values, and if that table has a LOT of translations (dozens or hundreds), maybe it is NOT a candidate for being "mingled" with the other tables. But where the translation choices are ALL relatively small in number, then in essence you can have a "parallel" translation.

Of course, it is all about the combo box or JOIN statement if you take this approach. You simply use the same PK column every time, but then use only the column appropriate for that table to which you are joining the lookup. Which means that table 1 has its own column that is different from the one used for lookups from table 2, etc.

This is ONE approach for collapsing the number of tables. Doing it this way simplifies the database in one way. However, it DOES make you have a strange-looking relationship diagram with all of those encoded fields relating back to the single translation table. And in the "purist" viewpoint, this is actually a normalization violation, since each field depends only on the PK, but in each record you have fields that depend on the PK AND the table to which you are joining. Like I said, a "purist" technical viewpoint.

I bring it up because Colin mentioned that he thought you had too many tables and you expressed concern over the lookup tables. I don't see any problem with separate tables but I have seen many cases where people wanted to consolidate their tables. This is one way to do it. It may be more effort to set up than it is worth, but that is always going to be your call anyway.

 

[DBApprentice]

This is ONE approach for collapsing the number of tables. Doing it this way simplifies the database in one way. However, it DOES make you have a strange-looking relationship diagram with all of those encoded fields relating back to the single translation table. And in the "purist" viewpoint, this is actually a normalization violation, since each field depends only on the PK, but in each record you have fields that depend on the PK AND the table to which you are joining. Like I said, a "purist" technical viewpoint.

I appreciate your comments, The_Doc_Man! I have considered this approach but I am not really sure if that would be the correct one in this specific case.

I agree this approach would apply for the UsersStatus, Categories, SatatusCards and AccessLevels because they have the same number of records (3 each) and I could have them under the same table.

Regarding the other tables, my main concerns is: Would I lose the ability to change things in the project later with this?

What if the Safety team decides they will start tracking more information about the Areas, Departments and Companies because they feel it is becoming more dangerous and receiving a lot of cards then I would have to change the static tables to remove them from there and create their own tables and re-work on the code? Then these tables will be normalized because it will have more characteristics for those entities.

In addition, is it that bad having static mini-tables?

 

[DBApprentice]

Diogo, I want to commend you. I am not saying that your choices are right or wrong, but rather you are showing us that you are learning things. Some people take offense when we criticize a design. You have taken our advice and examined it more closely. This openness to modifications is an important factor, particularly when designing something.

Thanks, I am here to absorb knowledge and wisdom from you, if I take offense I would not be a good pupil and also would not learn anything.

 

[isladogs]

What if the Safety team decides they will start tracking more information about the Areas, Departments and Companies because they feel it is becoming more dangerous and receiving a lot of cards then I would have to change the static tables to remove them from there and create their own tables and re-work on the code? Then these tables will be normalized because it will have more characteristics for those entities.

In addition, is it that bad having static mini-tables?

The phrase lookup tables is the standard terminology. As i said before, they are a very useful tool for guiding users to choose from the available items using combo boxes in forms. However they should NEVER be used at table level. - see the evils of lookup fields in tables
All data should in any case only be edited / entered using forms

I’ve made various suggested changes to your database but have kept all except two of the lookup tables.
However, I have removed all table level lookups (combos in tables) – all data should only be edited using forms
I’ve also scrapped your attachment field – I originally assumed it was a MVF – but attachments are equally bad!

OLD versions of all tables with changes have been saved with # prefix e.g. #tbl1Cards. You can delete any you no longer need after reviewing my suggestions ....or restore them if you disagree

Full details of changes I've made are in attached Word doc

Question: Why do all table names include a 1 (or a 2)?

I expect others will have their own ideas for changes.
Without knowing your data, none of us can give definitive answers about the detailed structure needed.
You will need to use your judgement about which advice to follow / ignore