Adding attributes to a 1:N or 1:1 relationship. Consecuences?

[Misionero]

I have doubts about what happens when adding attributes to a 1:N or 1:1 relationship. I understand that if I add attributes, in the relational model, this relationship would be treated as if it were an N:M relationship, creating a table for this purpose. For example, in a 1:1 relationship between people and identity documents, a person can only have one document, and each document can only belong to one person. The document number is always the same. Since it is renewed every 5 years, the renewal date attribute would go in the relationship "TENER" (to have). I understand that adding this attribute to the relationship, the transition to the relational model requires creating an intermediate table between people and documents, with the fields Idpersona, Iddocumento, and renewal date. Does this require changing the entity-relationship model design to reflect that the relationship is no longer 1:1 but N:M? Thanks for the help.

 

[plog]

A 1:1 relationship is usually wrong to have. And your example doesn't illustrate a need for one:

For example, in a 1:1 relationship between people and identity documents, a person can only have one document, and each document can only belong to one person. The document number is always the same.

The document ID number goes in the Person table. You'd have a field for it like IDNumber.

Since it is renewed every 5 years,

Are you tracking each renewal? Or do you just care when it expires?

If you want to know about each renewal and keep a history you need a new table--IDRenewals. It will be 1-many from Person table linked via IDNumber.

If you just want to know about expirations you add another field to the Person table to hold that--IDNumberExpiration.

No where do you need a 1:1 relationship.

 

[Misionero]

I need a tracking each renewal. This is the picture about this. My question si After this, when i put "date renovation" on relationship, change this the cardinality?, changing 1:1 to n:M?

Thanks

 

[plog]

I don't understand the words of your last post nor your diagram.

The good news is, my initial post covered all cases.

 

[The_Doc_Man]

Let's try it this way. Your question about 1/1 relationships is correct in that if you are adding a significant distinguishing feature you will have to modify the 1/1 to become 1/n. The reason is that for your example, you suddenly have TWO necessary identifying items for the document - the IDPerson number and the date (either the date on which it became valid or the date on which it expires - pick one).

In a two-sided 1/1 relationship, BOTH sides follow the rule that they have a single key that uniquely identifies each record. After this change, the Person table still can have IDPerson as a unique key, a valid PK. However, now the document table no longer uniquely depends on IDPerson. This changes your 1/1 to 1/n where the IDPerson field (probably the PK of that Person table?) becomes a foreign key of the document table and the PK is now <IDPerson, validity date> (unless you autonumber the document record).

Does that help answer your question?

As a side note, I fully agree with plog that you never actually needed a 1/1 relationship here anyway. I've only seen two cases in over 30 years where a 1/1 relationship actually made sense. One was for data security reasons; the other was that there was a limit on the number of fields required and the U.S.Navy needed to split a table into 3 parts - one major part and two minor parts - so that it could work with the major part and ONE of the minor parts together. And let me tell you, that was a total pain in the toches to manage.

 

[gemma-the-husky]

A person table linked to an identification document is 1 to many.

Let's say you store a passport document. Now if you replace the passport you can store both the original passport and the new passport. You may need the old passport reference.

It's not changed from 1:1 to 1:many. It's always been 1:many. it's just that you mistakenly considered it as 1:1 because in most cases you had only one item.

If it's really 1:1 you could store the document detail in the person table. You might decide you only need to store the current passport, in which case that would be an attribute in the person table, and then you can only have one passport.

( If you consider a person's name or address, then you only have a current value. However a person might move house, or change their name, perhaps by a result of marriage. Do you want to store both addresses or both names. If you do, you have to decide how to store that data. You may decide to store just the current details in the person table. You may add a field for previous name or previous address in the person table, or you may go the whole 9 yards, and add an extra table to store all previous names or addresses. If you do that it's 1:many. It may be 1:1 in general, but it's really 1:many. All of these choices are design considerations.

As the developer you have to consider how to store "history". If you are talking about a person's name, it can appear a little academic, but consider something like a price history .

If you sell a widget for £1, but today you change the price to £2, well you have to consider how your ordering system evaluates the price, how it stores the correct price on each order and so on. You most likely need to store a history of previous prices. It can become quite complex. As well as the price you may have different sales tax rates to consider as well. All the tables you design to manage the prices are 1:many, not 1:1)

Some people legitimately have multiple passports. So you might start off thinking you can store the passport attribute in the person table. However you then get a person with 2 valid passports and realise you need a separate table to store the passport data, as it's really a 1:N relationship.

By adding "type" in the document table, well it's clear that the document table is the many side of a 1:many. Now you can have a passport, and a driving licence in the document table.

However I think this is conflating the issue really, as the document table itself contains multiple data types. In some ways it's wrong to put both types of documents into a single table. Perhaps not so much wrong, as just an active design choice.

It wouldn't be wrong to have both a passport document table and a driving licence document table, but both of these would still be "many" child tables.

I hope this is making sense.

 

[gemma-the-husky]

@Misionero

Your original.premise is wrong. It's wrong to say a person can have just one document. A document might belong to one person, but a person can definitely have more than one document.

In point of fact a document can have more than one owner. A deed for a property can have multiple owners. A birth certificate will most likely name multiple parents. Depending on the system you design, you may need a different structure for your table design, but the tables will be 1:many.

 

[GaP42]

I have doubts about what happens when adding attributes to a 1:N or 1:1 relationship. I understand that if I add attributes, in the relational model, this relationship would be treated as if it were an N:M relationship, creating a table for this purpose. For example, in a 1:1 relationship between people and identity documents, a person can only have one document, and each document can only belong to one person. The document number is always the same. Since it is renewed every 5 years, the renewal date attribute would go in the relationship "TENER" (to have). I understand that adding this attribute to the relationship, the transition to the relational model requires creating an intermediate table between people and documents, with the fields Idpersona, Iddocumento, and renewal date. Does this require changing the entity-relationship model design to reflect that the relationship is no longer 1:1 but N:M? Thanks for the help.

Let me preface this: the process of modelling data is both an art and a science (maths) involving discovery and iteration as business rules (explicit and implicit) are uncovered. There are many texts written on this that go much further, espousing methodologies and approaches, than will be discussed in this thread.
Very often examples are simple to illustrate a principle.
In your modelling paradigm, you are describing adding an attribute, which if read as it appears indicates that you have determined that it is specific to the relationship - in the example - the date of renovation (renewal?) of a Personal Identification Document.
Taking this on face value there could be two business rules that need to be understood in answering your question.
In the scope of your design brief does the business:
1. require only one type of Person Identification Document? (eg a Chilean passport) to be recorded?
2. require that for a Personal Identification Document the most recent renewal date is recorded? ( ignoring that when a new passport is issued - as a renewal - the new passport has a different document identifier issued by the government. So do you update the record or create a new replacement record). That is, there is no interest in keeping the history of passport.

Re 1. Your OP model says 1:1 for Person to Personal Identification Document - before going further I could assume the context is for one type of document, and no other - a Passport (issued by a specific country), a birth certificate, a specific-state issued identity card - or any of these however, only one of these Personal identification Documents can be recorded. For this discussion, assume one type of Personal Identification Document is being dealt with. If not then the model should have been 1:M in the first place
Re 2. If it is agreed that only the latest renewal date is to be recorded for the one Personal Identification Document then that attribute is dependent upon the instance of the Personal Identification Document. It is not an attribute of the relationship between them. A new entity is not needed. If however, the business rule was that for such a Personal Identification Document each date of renewal was recorded (say, along with who sighted it and when it was sighted) then those attributes would require a new entity to be created as there would be a M:N relationship between Person and Personal Identity Document - and this new entity would record the Event of Sighting/Verifying the document. The model would then the altered to reflect this.

As @gemma-the-husky indicates, for 1:1 entities, unless there are business requirements that mandate the data must be stored separately for say privacy / security needs then there is no reason why, in implementation, that these two entities would not be merged to one.

 

[GaP42]

Exploring the case of a real 1:1 relationship:
A database may be modelled to store the details of people/employees. It may also require that the users have a userprofile. The users as people may also be in scope of the set of people/employees. Not all people are users. You could merge the tables and hold the user profile attributes against the People record. However this is not typical. Even where the password/login of the user requires a change periodically and the new password cannot be the same as one of the last x passwords used, then the old passwords are not stored in the user profile as a repeating attribute. The People-User Profile relationship remains 1:1

@gemma-the-husky : In health, person identity is very important, as well as the history of addresses for studies of disease incidence by geography and time so our data warehouse held multiple person identities/aliases and current and past addresses matching to episodes of care delivered at different health services (where the data was accumulated for analysis) - that of course had to be very secure and a master identity key assigned to the person, with the identifying data striped out when made available to data analysts.

 

[gemma-the-husky]

@GaP42

An interesting discussion on the peculiar nature that a "simple" address can become in certain cases.

Out of interest where do old passwords get stored. Is there a long encrypted string of them all somewhere? It has to be stored somewhere, and it's really representative of a 1:N relationship.

 

[GPGeorge]

Let's try it this way. Your question about 1/1 relationships is correct in that if you are adding a significant distinguishing feature you will have to modify the 1/1 to become 1/n. The reason is that for your example, you suddenly have TWO necessary identifying items for the document - the IDPerson number and the date (either the date on which it became valid or the date on which it expires - pick one).

In a two-sided 1/1 relationship, BOTH sides follow the rule that they have a single key that uniquely identifies each record. After this change, the Person table still can have IDPerson as a unique key, a valid PK. However, now the document table no longer uniquely depends on IDPerson. This changes your 1/1 to 1/n where the IDPerson field (probably the PK of that Person table?) becomes a foreign key of the document table and the PK is now <IDPerson, validity date> (unless you autonumber the document record).

Does that help answer your question?

As a side note, I fully agree with plog that you never actually needed a 1/1 relationship here anyway. I've only seen two cases in over 30 years where a 1/1 relationship actually made sense. One was for data security reasons; the other was that there was a limit on the number of fields required and the U.S.Navy needed to split a table into 3 parts - one major part and two minor parts - so that it could work with the major part and ONE of the minor parts together. And let me tell you, that was a total pain in the toches to manage.

I think the most defensible use of a 1:1 relationship I've seen was a case where each record in a table could have a lengthy comment associated with it. Rather than including the required Memo, or Long Text, field in that table, the design had a "Comments" table with the Long Text field, in a 1:1 relationship to tie the comment to the appropriate record.

Part of the justification for that design was the argument that Long Text fields might be more susceptible to corruption, as I recall. Mostly, though, it removed the "heavy" comments field from the parent table for other querying.

It does add a level of complication, however minor, to the database and does so unnecessarily in nearly all cases.

 

[GaP42]

@GaP42

An interesting discussion on the peculiar nature that a "simple" address can become in certain cases.

Out of interest where do old passwords get stored. Is there a long encrypted string of them all somewhere? It has to be stored somewhere, and it's really representative of a 1:N relationship.

Addresses were geocoded to population areas - sometimes those boundaries changed. More recently adopted a longer-term level of standardisation: ASGC Mesh Blocks at the most fine-grained level

Re the old passwords - encrypted, stored separately from the userprofile record - as the archived passwords did not require all the attributes of the user record - so 1:1 for People/Employee to UserProfile, 1:N for UserProfile to ArchivedPasswords.

 

[Misionero]

In Spain, people can have one DNI (National Identity Document) throughout their lifetime, and a DNI always belongs to a single person. The DNI cards are renewed every 5 years, and this is mandatory. Attached is the ER model that I think is suitable for this issue. I have included the 2 entities, their attributes, and the relationship. Given that there are 3 attributes that are neither for the person nor the DNI, I have placed them in the relationship.

1. I would like to know your opinion on whether this is viable, or if the various recommendations made to me are better. For example, a 1:N relationship where each renewal date generates a "new" DNI, even though it has the same DNI number.
2. I would like to know, even if you don't agree with this option and prefer others that your experience tells you are better, how it would be resolved in ACCESS. 3 tables? One for persons, another for DNI, and another for the relationship. Would the relationship table have the fields I have in the ER model, the foreign keys for persons and DNI, and its primary key would be the 2 foreign keys plus the date?
3. Would I need to make any changes to the ER model, such as changing the 1:1 relationship to N:M, since in ACCESS I insert a TENER table

On one hand, I want to develop a practical topic, but I’m also conducting theoretical research. I would like to understand the limits and constraints of the ER model (Entity-Relationship Model), even though it is not 100% regulated.

Thanks a lot for your support with your knowledge and professional experience.

 

[The_Doc_Man]

Out of interest where do old passwords get stored. Is there a long encrypted string of them all somewhere?

It varies from one operating system to another, and for individual apps it can be anywhere. For those systems that disallow password re-use for X number of passwords, the file that statically stores the password usually holds the list of old passwords along with whatever else is needed based on the regulations. Or the old HASH of the password, since most systems these days remember a hash value, not the raw password.

 

[The_Doc_Man]

I would like to know your opinion on whether this is viable

I see two tables. One for the person (PK=IDPerson), where the invariant specifics of the identity card are stored; a second table for the history of renewals, all of which are time-sensitive (PK=<IDPerson, date> OR <IDPerson, per-person action sequence number>) .

There is almost NEVER a practical 1:1 situation that doesn't involve something out of the ordinary, like a specific type of security requirement to hide PART of a record but not all of it, or a table that has to be logically split into smaller parts for handling purposes. Any other time, if it is 1:1, it should never have been split out of the original table in the first place. Based on defining what is the particular ID for the record. When you say that a particular pair of tables has a 1:1 relationship, then ALL of the fields in BOTH tables depend on the same exact key field AND key value. By the general rules of normalization, they thus ALSO belong in the same TABLE.

If you want to say there is a security issue, my comment is that the same security rules should apply to the person's record as applies to the person's document record if they were similar. I.e. you don't want Kelly Schmuckatelli editing EITHER chunk of info without proper authorization, so the security reason doesn't seem to apply. If you take the renewal history into a separate table, the "separation of tables for handling issues" doesn't seem to apply either. Therefore, I am hard-pressed to recognize a valid reason to maintain a 1:1 two-table situation.

For example, a 1:N relationship where each renewal date generates a "new" DNI, even though it has the same DNI number.

people can have one DNI (National Identity Document) throughout their lifetime,

Again, I don't see that as a "new" DNI if it has the same DNI number, though if you wanted to generate a unique counter (per person) to record any transactions, there would be no barrier. I'm not seeing a need for an N:M junction table because there is still only a single-person record that is invariant and thus a reasonable "anchor" for a 1:N relationship, where the N-side is the renewal period. Since it is the same ID number, I don't see it as a new ANYTHING other than the abstraction of that "new" renewal period.

 

[GaP42]

Agree with @The_Doc_Man - unless there is some compelling reason for the Spanish DNI to be physically held separately, the Spanish DNI attributes belong with the Person. As no compelling reason has been given then combine them.
The record of renewals / sighting, if history is needed, can be recorded as a "Tener" record.
If the history of renewals is not required, then only the most recent renewal is needed - and these Tener attributes would belong with the Person also.
Design is driven by business requirements - the need for keeping history would need to be explored by the analyst with the business asking for eg what purpose is served by keeping the history? What rules/process is followed when the Renewal Date is > 5 years?

So depending upon the business need:
1 table - Person - with the Spanish DNI attributes and the Tener attributes for the most recent renewal only
OR
2 tables- 1:M - table Person with Spanish DNI attributes and table Tener holding the renewal attributes (and the Person FK)

 

[gemma-the-husky]

@Misionero

What you will find is having lots of child tables can make database development awkward, and can affect performance in some ways.

It makes a lot more sense now you explained what the DNI actually is.

So one way is to have a person, and store the DNI details in the person table. That will be the easiest way as a developer. Do you need to store any more info than the DNi number? Do you need to store the details about the date issued, etc. in some ways this sounds little different to a passport, or maybe even a SSN number especially in the US, as they seem to be regarded as much more sensitive than a UK NI number for instance.

The second way is to have a person, and then store the DNI history in a linked table. Now you need to add a way to determine which DNI record you require every time you read the DNI for the person, so you are adding more development complexity. This DNI table is a 1:N relationship with the person table.

Notice that you never get a 1:1 table. You don't have a 1:1 table until you add a second DNI. It's always a 1:N table, but in many cases you just have 1 related record.

The only reason for a 1:1 table might be that you have the first situation above, but you need to keep the DNI details completely confidential for some reason, and encrypting the values within the person table isn't sufficient. The 1:1 is not really required for systems analysis, just as a development nicety if you will.

This is really just another way of thinking;- about the explanations already given.