子类与实体、子类间关系映射咨询：房东-房产及学生-房东关联处理

Hey there! Let's work through these two mapping questions since you already know your way around one-to-many relationships—solid foundation to build on.

Since Landlord is a subclass of Person and uses Person's primary key (nic) as its own, your approach to using nic as the foreign key in the Property table is spot-on. Let's clarify the details to make it airtight:

• The Landlord entity doesn't have a separate primary key—it reuses Person's nic. So any property owned by a landlord should link back to that nic (which uniquely identifies both the base Person and the Landlord subclass instance).
• Your proposed Property table structure is almost correct, but let's formalize the keys:
  • Idno should be the primary key of the Property table (each property needs its own unique identifier)
  • NIC acts as the foreign key referencing Person(NIC) (since Landlord shares this key with its parent Person table)

Here's the cleaned-up table definition (SQL-like syntax for clarity):

Property(
  Idno INT PRIMARY KEY, -- Unique property identifier
  Street VARCHAR(100),
  City VARCHAR(50),
  Fee DECIMAL(10,2),
  Amount DECIMAL(10,2),
  NIC VARCHAR(20) FOREIGN KEY REFERENCES Person(NIC) -- Links to the landlord (a Person subclass)
)

This setup properly enforces the one-to-many rule: one landlord (via their NIC) can own multiple properties, but each property belongs to exactly one landlord.

First, let's fix your Student table design—you're absolutely right that having duplicate NIC columns is incorrect. Since Student is also a subclass of Person, it should reuse Person's NIC as its primary key (just like Landlord does).

Correct Student Table Structure #

Student(
  NIC VARCHAR(20) PRIMARY KEY FOREIGN KEY REFERENCES Person(NIC), -- Reuses Person's PK as own PK + foreign key
  STID VARCHAR(20), -- Unique student ID (if required, separate from NIC)
  Gender CHAR(1),
  DOB DATE
)

No need for a second NIC column—this single entry acts as both the student's unique identifier and the link back to the parent Person table.

Mapping the Student-Landlord Relationship #

The exact mapping depends on the nature of their relationship:

• Indirect relationship (most common): If students rent properties from landlords, you don't need a direct link between Student and Landlord. Instead, connect Student to Property (e.g., add a Tenant_NIC foreign key to Property), and Property already links to Landlord via NIC.
• Direct relationship: If you need a direct association (e.g., a student has a preferred landlord independent of a rental), create a junction table for the many-to-many relationship (one student could have multiple landlords, and vice versa):

  Student_Landlord_Association(
    Student_NIC VARCHAR(20) FOREIGN KEY REFERENCES Student(NIC),
    Landlord_NIC VARCHAR(20) FOREIGN KEY REFERENCES Person(NIC), -- Landlord uses Person's NIC
    PRIMARY KEY (Student_NIC, Landlord_NIC) -- Composite key to avoid duplicate links
  )
  

Since Landlord is a subclass of Person, referencing Person(NIC) works perfectly here—you can add a PersonType column to the Person table (e.g., 'STUDENT'/'LANDLORD') to filter records if needed.

内容的提问来源于stack exchange，提问作者IroCurie