Typical situation 1:1 Superidentities with subidentities

[Misionero]

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.

Thanks a lot for your answer. I can see this world is very complicated. No only the experience is very important, moreover they exist a lot of schools of notations. When i see all information in Internet, my head has a big explossion. I have a big luck finding this forum where all of you share your experiences and knowledge, with me.....I am a beginner in this world. Thanks.

In your answer tell us only advantage is explain client as DB is configurated. If they dont need this explanation, i understand you say there arent more negative points.

 

[The_Doc_Man]

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.

If you are describing what I think you are describing, you cannot implement Relational Integrity on your 1:1 tables because you will have at least some entries in the supertype table for which there will be no corresponding subtype entry. That CANNOT happen in a true and enforced 1:1 relationship, which is bidirectional. The DB engine will (or at least SHOULD) complain about "can't save data because there is no corresponding entry in a related table." If RI is turned off, then you could do what you described, but you lose the ability to have the DB automatically watch out for orphan records.

However, if these were 1:N relationships (where N will be either 1 or 0 and no other number), THAT kind of relationship allows RI to be established. This perhaps seems like a nit-pick, but it IS important to recognize the limitations of this design.

 

[GregDataReno]

If you are describing what I think you are describing, you cannot implement Relational Integrity on your 1:1 tables because you will have at least some entries in the supertype table for which there will be no corresponding subtype entry. That CANNOT happen in a true and enforced 1:1 relationship, which is bidirectional. The DB engine will (or at least SHOULD) complain about "can't save data because there is no corresponding entry in a related table." If RI is turned off, then you could do what you described, but you lose the ability to have the DB automatically watch out for orphan records.

However, if these were 1:N relationships (where N will be either 1 or 0 and no other number), THAT kind of relationship allows RI to be established. This perhaps seems like a nit-pick, but it IS important to recognize the limitations of this design.

Lots to consider, but in brief, I should declare I'm really advocating a 1:[0-1] relation.

A lot of the work I do is for scientific monitoring where differents groups of attributes apply to different subtypes of the same concept. Super/Sub typing offers a cleaner design by reducing the NULLs and simplifies adding new subtype tables (eg "Mammal", "Arachnid") concepts at a later date.

SuperTable: RecordID, RecordClass, <Common attributes>
SubTable: RecordID (FK), RecordClass (FK), <Specific attributes>

(Adding RecordClass to PK is arguably redundant, but it does gaurantee RI in the subtype table)

An example [1]:

SuperTaxa: 1001,Fish,River,Big Amazon Fish
SuperTaxa: 1002,Bird,Forest,Blue Lapwing​

​

FishTaxa: 1001,Fish,Edible,Catadromous,...​

BirdTaxa: 1002,Bird,Can talk,Flightless,...​

​

This is a non-overlapping, exclusive model defined by ensuring RecordClass is mandatory (eg default = "Bird" in the BirdTaxa table) and cannot be changed.

A super type record can be added, but not necesarily to the subtype table, and this won't contravene RI.

CRUD is done via a 1:1 View - to add a Bird, we can just add it to the SuperTaxa if there's no mandatory subtype values, or we can use a view joining SuperTaxa and BirdTaxa; this may require some slight of hand but I recall Access and SQL Server can create new records using a Join.

I tend to avoid using surrogate keys (ie IDENTITY, AutoNumber etc) in a Super/Sub model where there's a perfectly suitable natural key. In my example case, I'd replace the RecordID with parts of the scientific name Genus, Species and Class (there are hundreds of instances where different taxa share the same scientific name, so Class is the discriminator - see Shipunov, Alexey (2013) "The problem of hemihomonyms and the on-line hemihomonyms database (HHDB)").

[1] An example only, and not well suited to modelling Taxonomy.

 

[Misionero]

Lots to consider, but in brief, I should declare I'm really advocating a 1:[0-1] relation.

A lot of the work I do is for scientific monitoring where differents groups of attributes apply to different subtypes of the same concept. Super/Sub typing offers a cleaner design by reducing the NULLs and simplifies adding new subtype tables (eg "Mammal", "Arachnid") concepts at a later date.

SuperTable: RecordID, RecordClass, <Common attributes>
SubTable: RecordID (FK), RecordClass (FK), <Specific attributes>

(Adding RecordClass to PK is arguably redundant, but it does gaurantee RI in the subtype table)

An example [1]:

​

SuperTaxa: 1001,Fish,River,Big Amazon Fish​

SuperTaxa: 1002,Bird,Forest,Blue Lapwing​

​

FishTaxa: 1001,Fish,Edible,Catadromous,...​

​

BirdTaxa: 1002,Bird,Can talk,Flightless,...​

​

This is a non-overlapping, exclusive model defined by ensuring RecordClass is mandatory (eg default = "Bird" in the BirdTaxa table) and cannot be changed.

A super type record can be added, but not necesarily to the subtype table, and this won't contravene RI.

CRUD is done via a 1:1 View - to add a Bird, we can just add it to the SuperTaxa if there's no mandatory subtype values, or we can use a view joining SuperTaxa and BirdTaxa; this may require some slight of hand but I recall Access and SQL Server can create new records using a Join.

I tend to avoid using surrogate keys (ie IDENTITY, AutoNumber etc) in a Super/Sub model where there's a perfectly suitable natural key. In my example case, I'd replace the RecordID with parts of the scientific name Genus, Species and Class (there are hundreds of instances where different taxa share the same scientific name, so Class is the discriminator - see Shipunov, Alexey (2013) "The problem of hemihomonyms and the on-line hemihomonyms database (HHDB)").

[1] An example only, and not well suited to modelling Taxonomy.

This level is high level for me

 

[The_Doc_Man]

@GregDataReno

Figured it had to be 1:[0-1]. There are still issues to consider but as long as all JOINs involving the supertype key start based in the supertype table, you would be able to build a reasonable set of queries. There is still the issue that in theory, you have items that APPEAR to depend only on the PK but that in fact also depend on the table in which they appear, i.e. there is a secondary key derived from metadata, namely the table name (or in your case, RecordClass). This is a different comment than the issue of relational integrity.

Have you ever looked at EAV table layouts for this? It's a bit more complex to put things together, but Entity-Attribute-Value design totally avoids those theoretical "gotcha" cases caused by using meta-data.

 

[GregDataReno]

@GregDataReno


Have you ever looked at EAV table layouts for this? It's a bit more complex to put things together, but Entity-Attribute-Value design totally avoids those theoretical "gotcha" cases caused by using meta-data.

Yes to EAV's in the past, but since an EAV can't enforce type/range/allowed values etc at the storage level, EAVs require slight-of-hand - need to use the attribute type to know how to QA each entry. Also, the need to cast from text (the only reliable, universal data 'type') to output data types is slow with big datasets and complex types. EAVs definitely have their place, and I've used them successfully in the past.

Interestingly, EAVs are a candidate for super/subtyping - supertype being the Attribute name, then store nominal/ordinal/interval/ratio types to respective tables for easy validation. An alternative, simpler, form would be store text EAVs in a 'text' table, numerics to numerics etc.

(Of course the subtype table names in my example don't need to be the name of the class - just a convention - tblAVES could be "Birds" etc.).

 

[gemma-the-husky]

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.

What on earth do you mean by the above? Are you trying to say you have a master table for persons that appear in other tables? I think I would rather have the duplication by having the same person appear in more than one table, without the master table at all.