“We have Building, Room, and Bed entities. Logically, if this is in the scope of some hypothetical hotel, then each one of those entities is dependent on their parent to exist ... you cannot have a bed without a room. Also, that room wouldn't exist without its parent, Building. So, why have I rarely seen this identifying relationship introduced? When I was learning databases, everything was apparently "non-identifying". When is this type of relationship necessary, if at all? I see the issue arises when that BED can exist without a BUILDING. If you were to INSERT into the BED table, you are constraint [sic] to provide a building_id, as the building_id is part of that BED's primary key. Couldn't you avoid an identifying relationship by giving each table its own surrogate primary key? Is this the correct representation  of an identifying relationship? I could avoid that by just giving each table its own ID. At the end of the day, this is about IDENTIFYING relationships, not their existence, which is how I've been logically determining if something is an "identifying relationship" If that were the case, then any 1:N relationship could be "identifying" but that's not how you define identifying or non-identifying.”

“Interesting -- I’d never heard this term before. I’ve heard it referred to as a cached ID though, as that 2nd ID isn’t required, but may be beneficial for performance purposes. For this example with 3 levels it’s not a huge joint statement, but for some systems with 12 tables the joins get unpleasant. I’ve never started a system with this additional id, but I have added one later on once the need was there and the profiling led to this being the best solution for our specific situation. Usually though, just creating a view that does the joins for me has been easier. I’ll be curious what has led others to use this approach.”

It's not really introduced because it's way more towards academic than functional.”

--Reddit.com

• Multiple rooms per floor (M:1);
• One or more beds per room (M:1), with "single" rooms as a 1:1 special case.

BUILDING {BLDID,BLDNAME,...}
ROOMS {ROOM#,FLOOR,BLDID,...}
BEDS {ROOM#,BLDID,FLOOR,SIZE,...}

• With a single building, the BUILDING relation makes no sense, let alone embedding the BLDID SK in ROOMS or BEDS -- how to represent building properties in this case requires a more complex conceptual model and possibly multiple relations, which is beyond the scope of this discussion (see the multi-building case below);
• BEDS does not have a PK: it looks like it is assumed that if there are multiple beds in a room, they have distinct sizes and, thus, (ROOM#,SIZE) can serve as composite PK. But SIZE represents a property, not a name, and cannot serve as a PK component[3].

ROOMS {ROOM#,FLOOR,...}
BEDS {BEDID,ROOM#,SIZE,...}

BUILDING {BLDNAME,...}
ROOMS {ROOM#,FLOOR,...}
BEDS {BEDID,SIZE,...}
ROOM-BEDS {ROOM#,BEDID}

BUILDINGS {BLDID,BLDNAME,...}
ROOMS {ROOM#,...}
BEDS {BEDID,...}
BLDG-ROOMS {BLDID,ROOM#}
ROOM-BEDS {ROOM#,BEDID}

• Design robustness to changes:

- if rooms have a "bed capacity" (either fixed for fixed bed size, or computed according bed size), and the relationship between beds and rooms changes from 1:1 to M:1, or even M1:M2 (e.g., as in rooms being combined into suites via adjoining rooms with doors between them) (only the constraint may change, if necessary);
- if beds get moved between rooms;
- if room numbering changes (e.g., to create a "members only" or "non-smoking" building", while keeping the room pricing and descriptions the same);
- if some rooms have beds and others do not (e.g., meeting rooms);

• Elimination of the anomaly (and atrocity) of self-reference of the embedded FK design (the common example is the EMPLOYEES relation that includes the EMP# number of the manager -- there must be at least one tuple for which MGR# is inapplicable).

“In short, logical designs using association relations are more robust as 4OP relationships evolve, make their representation more symmetrical (the parent/child distinction is purely semantic, so we can enter the relationship from either "side"), and easier to alter to add properties (not possible with embedded FKs), avoid inapplicable nulls, and the corresponding physical design can be better optimized (more implementation options).

--David McGoveran