Typical situation 1:1 Superidentities with subidentities (1 Viewer)

[Misionero]

Hi. In all my questions you recomendme the 1:1 relationships are bad idea because in the real world dont exist or the implementation are very complicated. Now i am studying eer new types. Superidentities with subidenties. In ER the model would be this.

This model is account bank types from general account bank.

The next is converting this ER model in EER model with ISA

What is your oppinion about this? Do you have any recomendations to altertive resolutions?. Thanks.

 

[The_Doc_Man]

In all my questions you recomendme the 1:1 relationships are bad idea because in the real world dont exist or the implementation are very complicated.

More precisely, a pair of tables with a 1:1 relationship should never have become a pair of tables in the first place. They should have stayed together. This is not an Access rule but a data modeling rule. Things that are 1:1 DO exist but don't belong as separated entities. In general, if you have a 1:1 pair of tables, that means that they align with each other based on one key field that exists in both tables. This leads to the question of why they were ever separated, since the contents of EITHER table depend on the same thing. What is that old rule that says "two things equal to a third thing are also equal to each other"?

In general, the rule is that things belong in the same table if they depend on the same exact thing. What you do to fix that is you merge the fields of the two tables to make single records out of the fields from BOTH of the previously separated tables. When you are done, the fields (all of which now represent properties of that thing being represented) are all dependent on the SINGLE key value.

BEFORE that merge operation, you had properties that depending on TWO things - first, the single key that linked the tables, and second, the name of the table in which they were stored. But that is a violation of the normalization rules. The identity of the table in which an item is found is metadata but the field that is the key is ordinary data. Metadata describes the schema, the layout of data. Using metadata to identify a field's location is what is wrong. I hope that didn't confuse you too badly.

Regarding your banking diagram, I am not familiar with this notation and therefore do not understand it. I defer to any of my colleagues who might have some experience with this particular method of diagramming.

 

[GaP42]

The development of a conceptual / logical model is a step along the path to the physical model. ER / EER models are used to represent these models. The representation of entities involving the ISA construct (is a type of - super/sub entities) is valid at the conceptual / logical stage. They are 1:1 relationships.

Translation to a physical model - implemented within an RDBMS then involves consideration/application of normalisation rules. Having performed the analysis resulting in the diagram, and the assigned attributes, it is simple to see that it is possible to consider each of the specific Accounts are Types of Account and can be subsumed under a generic Account entity, with the associated attributes specific to the types also being held there. The 1:1 relationships disappear. Do you then have issues with allocation of attributes specific to an account type? if specific they are optional - operational rules require the attribute to be populated based on the account type.

Are you hoping to apply an EER model as a physical model without use of normalisation?

(I am not a spanish speaker - so my reading /understanding) of the model is unlikely to be perfect)

 

[Misionero]

The development of a conceptual / logical model is a step along the path to the physical model. ER / EER models are used to represent these models. The representation of entities involving the ISA construct (is a type of - super/sub entities) is valid at the conceptual / logical stage. They are 1:1 relationships.

Translation to a physical model - implemented within an RDBMS then involves consideration/application of normalisation rules. Having performed the analysis resulting in the diagram, and the assigned attributes, it is simple to see that it is possible to consider each of the specific Accounts are Types of Account and can be subsumed under a generic Account entity, with the associated attributes specific to the types also being held there. The 1:1 relationships disappear. Do you then have issues with allocation of attributes specific to an account type? if specific they are optional - operational rules require the attribute to be populated based on the account type.

Are you hoping to apply an EER model as a physical model without use of normalisation?

(I am not a spanish speaker - so my reading /understanding) of the model is unlikely to be perfect)

do you recomend this type of ISA end in Access like only one table ACCOUNTS BANK and the specific atributes of specific accounts can be empty unless we have a lot of accounts with this atribute empty?.... Sorry for my english

 

[Misionero]

More precisely, a pair of tables with a 1:1 relationship should never have become a pair of tables in the first place. They should have stayed together. This is not an Access rule but a data modeling rule. Things that are 1:1 DO exist but don't belong as separated entities. In general, if you have a 1:1 pair of tables, that means that they align with each other based on one key field that exists in both tables. This leads to the question of why they were ever separated, since the contents of EITHER table depend on the same thing. What is that old rule that says "two things equal to a third thing are also equal to each other"?

In general, the rule is that things belong in the same table if they depend on the same exact thing. What you do to fix that is you merge the fields of the two tables to make single records out of the fields from BOTH of the previously separated tables. When you are done, the fields (all of which now represent properties of that thing being represented) are all dependent on the SINGLE key value.

BEFORE that merge operation, you had properties that depending on TWO things - first, the single key that linked the tables, and second, the name of the table in which they were stored. But that is a violation of the normalization rules. The identity of the table in which an item is found is metadata but the field that is the key is ordinary data. Metadata describes the schema, the layout of data. Using metadata to identify a field's location is what is wrong. I hope that didn't confuse you too badly.

Regarding your banking diagram, I am not familiar with this notation and therefore do not understand it. I defer to any of my colleagues who might have some experience with this particular method of diagramming.

Ok, is it possible conceptual design 1:1 and conceptual design other type?

 

[The_Doc_Man]

Ok, is it possible conceptual design 1:1 and conceptual design other type?

It is possible to physically create a 1:1 table pair, which is a pain in the toches to maintain. However, the conceptual design for relational tables is all about the keys. If a group of attributes (fields) depend exactly and ONLY on the same key they belong in the same table as that key because they are describing the thing selected by that key. Remember, that last statement is "for relational tables"!

Therefore, conceptually, the answer is NO, that design is not really conformant with theory for most situations. However, it is both possible and easily imagined that you can have two tables that APPEAR to justify a 1:1 relationship. Usually upon closer examination you find that the 2nd table is something different and you merely hadn't (yet) identified the difference so don't (yet) have the proper distinguishing attributes that allow for identifying the differences in the tables. Omitting the distinguishing properties so that you could create a 1:1 pair of tables is merely hiding the reality of the situation. For instance, consider your person ID number and the government ID card in your prior thread. When we dug into the situation we found that the card had renewal information that a person doesn't have, and therefore you actually had a 1:N relationship with the N-side being the various renewals and other administrative history of the card.

One of the reasons I said you could break the 1:1 rule is for security issues. In one particular U.S. Navy personnel database, we had a couple of 1:1 tables relating to personnel information. One of the two tables tracked "ordinary" data but the other table tracked data controlled by the USA Privacy Act, thus having different security requirements. This was an ORACLE back-end, not an Access back-end, so we could actually impose data-access restrictions selectively.

The other exception is one forced by mechanical limitations. If the REAL record would have had 300 fields in it and you were using an Access front end, there is a 255 field limit on queries. So you would break that long record up into at least three 1:1 parts, each part containing a subset of the real record. Then you could JOIN any two parts at once though you couldn't JOIN all three parts. However, the odds of a 300-field record being meaningful is rather low and therefore unlikely to be a reasonable situation.

I guess it is possible to come up with a contrived scenario that would SEEM to support a 1:1 table pair, but usually what happens is that you eventually recognize that there is something different about one of the tables that justifies the separation but ALSO makes the relationship to no longer be 1:1 - which invalidates the contrived scenario.

Does that help?

 

[xavier.batlle]

I have several relations 1:1 with no issues. It is a way to organize your database. Example:
Person 1:1 <- Clients
Person 1:1 <- Customers
Person 1:1 <- Workers
Person 1:1 <- Contacts
Person 1:1 <- Other stuff...
...
Somebody could say that I could use only 1 table to store all the data but in my database there are a lot of columns all these tables, and 255 columns is the max number on a MS Access database.

 

[GaP42]

do you recomend this type of ISA end in Access like only one table ACCOUNTS BANK and the specific atributes of specific accounts can be empty unless we have a lot of accounts with this atribute empty?.... Sorry for my english

In the absence of any further information / analysis concerning the attributes associated with the types - yes.
A consideration that might influence the decision is how simple it may be to add another type of account. Perhaps with a single accounts table and types, and appropriate attributes in the existing structure a new account type MAY be simple to add. Adding some new attributes is quite straightforward. Compare that to separate tables for each account type. You would need to create a new table and the attributes and establish integrity rules (as a min).

Attributes such as interest rate charge, overdraft limits, expiration dates need to be carefully analysed to determine if they really belong with the Account itself (fixed) or variable and so whether a record of changes is needed. These might then be split off to reflect conditions/constraints applicable to an account (type) for a period of time.

A reference/eg

Mapping Subtypes and Supertypes to a Relational Database

in my database there are a lot of columns all these tables, and 255 columns is the max number on a MS Access database.

as Xavier points out - it is possible to run into limitations - but note that these are TECHNICAL considerations (applied post-hoc to normalisation) and do not arise through appropriate normalisation of the data. Also note that it is quite possible to allow 1:1 relationships in the physical model - as long as you manage the data appropriately - beyond the usual management of data if it were in one table.

 

[mike60smart]

Hi. In all my questions you recomendme the 1:1 relationships are bad idea because in the real world dont exist or the implementation are very complicated. Now i am studying eer new types. Superidentities with subidenties. In ER the model would be this.
View attachment 116620


This model is account bank types from general account bank.

The next is converting this ER model in EER model with ISA


View attachment 116621

What is your oppinion about this? Do you have any recomendations to altertive resolutions?. Thanks.

It would help if you name all of the ER Diagram in English

 

[Misionero]

It would help if you name all of the ER Diagram in English

 

[Misionero]

It is possible to physically create a 1:1 table pair, which is a pain in the toches to maintain. However, the conceptual design for relational tables is all about the keys. If a group of attributes (fields) depend exactly and ONLY on the same key they belong in the same table as that key because they are describing the thing selected by that key. Remember, that last statement is "for relational tables"!

Therefore, conceptually, the answer is NO, that design is not really conformant with theory for most situations. However, it is both possible and easily imagined that you can have two tables that APPEAR to justify a 1:1 relationship. Usually upon closer examination you find that the 2nd table is something different and you merely hadn't (yet) identified the difference so don't (yet) have the proper distinguishing attributes that allow for identifying the differences in the tables. Omitting the distinguishing properties so that you could create a 1:1 pair of tables is merely hiding the reality of the situation. For instance, consider your person ID number and the government ID card in your prior thread. When we dug into the situation we found that the card had renewal information that a person doesn't have, and therefore you actually had a 1:N relationship with the N-side being the various renewals and other administrative history of the card.

One of the reasons I said you could break the 1:1 rule is for security issues. In one particular U.S. Navy personnel database, we had a couple of 1:1 tables relating to personnel information. One of the two tables tracked "ordinary" data but the other table tracked data controlled by the USA Privacy Act, thus having different security requirements. This was an ORACLE back-end, not an Access back-end, so we could actually impose data-access restrictions selectively.

The other exception is one forced by mechanical limitations. If the REAL record would have had 300 fields in it and you were using an Access front end, there is a 255 field limit on queries. So you would break that long record up into at least three 1:1 parts, each part containing a subset of the real record. Then you could JOIN any two parts at once though you couldn't JOIN all three parts. However, the odds of a 300-field record being meaningful is rather low and therefore unlikely to be a reasonable situation.

I guess it is possible to come up with a contrived scenario that would SEEM to support a 1:1 table pair, but usually what happens is that you eventually recognize that there is something different about one of the tables that justifies the separation but ALSO makes the relationship to no longer be 1:1 - which invalidates the contrived scenario.

Does that help?

Yes. your experience is GOLD. Thanks a lot.

 

[mike60smart]

View attachment 116630

Well that should just be a simple Many to Many Relationship

Many People have Many Bank Accounts.

tblPeople
-PeopleID - OK Autonumber
-Firstname
-Lastname

tblBankAccounts
-BankAccountID - PK - Autonumber
-BankName
-BankType
-(Other fields required)

jxtPeopleBank
-PeopleBankID - PK Autonumer
-PeopleID - FK (Linked to PK from tblPeople)
-BankAccountID - FK (linked to PK from tblBankAccounts)

 

[The_Doc_Man]

I have several relations 1:1 with no issues. It is a way to organize your database. Example:
Person 1:1 <- Clients
Person 1:1 <- Customers
Person 1:1 <- Workers
Person 1:1 <- Contacts
Person 1:1 <- Other stuff...
...
Somebody could say that I could use only 1 table to store all the data but in my database there are a lot of columns all these tables, and 255 columns is the max number on a MS Access database.

OK, this is a trivial observation, but ... EVERY ONE of those things has 1 person to a word that is plural, (Clients, Customers, Workers, ...) and would seem that what YOU describe as 1:1, I would describe as 1:N - where a person has many clients or many customers, or is one of many workers, etc.

In the abstract, I don't know what you mean by "Clients" in the context of your app, but in the common English usage of that word, it would surely be plural and thus unlikely to be useful in a true 1:1 context. Not saying you are wrong - but that IS an unusual characterization that I don't understand.

 

[xavier.batlle]

OK, this is a trivial observation, but ... EVERY ONE of those things has 1 person to a word that is plural,

Sorry @The_Doc_Man, you are rigtht, both must be singular. These relations refers that a person can have several roles (client, customer, etc. ) and each role whith different attributes.

 

[The_Doc_Man]

Based on the concept that any of those roles might be OPTIONAL, I can see you omitting them from the 1:1 tables - but you can't have a formal declared relationship for ALL of those cases because if you have a blank, a JOIN will omit the person record that has no matching other record.

We have seen this kind of thing on the forum before. I will respectfully suggest that this is merely a case of hiding attributes in order to have a 1:1 relationship. It violates the rule that says that an attribute cannot be based on both regular data AND metadata at the same time. In other words, if the attribute = (client, customer, worker, etc), that defines in which table the information is located, and yet the 1:1 aspect of the relationship says they are ALL a person, i.e. the same thing from any of the different relationship directions, and therefore are all the same. It is a false, or at least an obscured, differentiation of roles.

Stated another way, an attribute in ANY of those tables refers to the same person but also depends on something that SHOULD be another attribute. Instead, it is the name of a table, which is structural data (metadata). The flaw is that the 1:1 relationship, if actually declared with relational integrity, would complain the moment you found a person who WASN'T in each of those individual role tables. That is the problem with maintenance overhead that I mentioned earlier in the thread. A formal RI declaration would REQUIRE a record to be in a table even if that person shouldn't be counted for that particular role.

 

[Misionero]

Based on the concept that any of those roles might be OPTIONAL, I can see you omitting them from the 1:1 tables - but you can't have a formal declared relationship for ALL of those cases because if you have a blank, a JOIN will omit the person record that has no matching other record.

We have seen this kind of thing on the forum before. I will respectfully suggest that this is merely a case of hiding attributes in order to have a 1:1 relationship. It violates the rule that says that an attribute cannot be based on both regular data AND metadata at the same time. In other words, if the attribute = (client, customer, worker, etc), that defines in which table the information is located, and yet the 1:1 aspect of the relationship says they are ALL a person, i.e. the same thing from any of the different relationship directions, and therefore are all the same. It is a false, or at least an obscured, differentiation of roles.

Stated another way, an attribute in ANY of those tables refers to the same person but also depends on something that SHOULD be another attribute. Instead, it is the name of a table, which is structural data (metadata). The flaw is that the 1:1 relationship, if actually declared with relational integrity, would complain the moment you found a person who WASN'T in each of those individual role tables. That is the problem with maintenance overhead that I mentioned earlier in the thread. A formal RI declaration would REQUIRE a record to be in a table even if that person shouldn't be counted for that particular role.

Thanks.

 

[Misionero]

Well that should just be a simple Many to Many Relationship

Many People have Many Bank Accounts.

tblPeople
-PeopleID - OK Autonumber
-Firstname
-Lastname

tblBankAccounts
-BankAccountID - PK - Autonumber
-BankName
-BankType
-(Other fields required)

jxtPeopleBank
-PeopleBankID - PK Autonumer
-PeopleID - FK (Linked to PK from tblPeople)
-BankAccountID - FK (linked to PK from tblBankAccounts)

In this case, the bank offer only one account to retired people

 

[Misionero]

Thanks for the answers. I am learning a lot of things with your help.

 

[Misionero]

In the absence of any further information / analysis concerning the attributes associated with the types - yes.
A consideration that might influence the decision is how simple it may be to add another type of account. Perhaps with a single accounts table and types, and appropriate attributes in the existing structure a new account type MAY be simple to add. Adding some new attributes is quite straightforward. Compare that to separate tables for each account type. You would need to create a new table and the attributes and establish integrity rules (as a min).

Attributes such as interest rate charge, overdraft limits, expiration dates need to be carefully analysed to determine if they really belong with the Account itself (fixed) or variable and so whether a record of changes is needed. These might then be split off to reflect conditions/constraints applicable to an account (type) for a period of time.

A reference/eg




as Xavier points out - it is possible to run into limitations - but note that these are TECHNICAL considerations (applied post-hoc to normalisation) and do not arise through appropriate normalisation of the data. Also note that it is quite possible to allow 1:1 relationships in the physical model - as long as you manage the data appropriately - beyond the usual management of data if it were in one table.

Thanks for the link.

 

[GregDataReno]

I believe there is a legitimate role for 1:1 tables in the physical implementation. I've used such a design successfully when subtyping complex entities with many attributes. All common attributes are held in a super table along with a subtype category, and columns specific to each subtype (and the assigned subtype category) are held in the respective subtype table. A 1:1 relationship links supertype to subtypes.

Advantages are:

1. simpler to design forms (1:1 join query is the source for each subtype form (or use a subform)
2. reduced proliferation of NULLs (ie "bloat") in columns that are specific to only subtypes
3. easy to later add new subtypes with out needing to change the super structure
4. isolate subtype data using views that include only that subtype in a 1:1 join with the super type
   

Disadvantages are that it's complex to convey to users if they want to understand how their database is configured.

Done well, super/subtypes are an elegant design solution.

See Joe Celko's contributions on the place of NULLs as it relates to NULL columns.
I recall David Hay (Data Models and Patterns) makes extensive use of super/subtype design.