Database Design
Hello everyone, I'm building a travel reservation system and need help deciding between two approaches for modeling package details.
I already have:
Subtypes like:
Each subtype has its own specific fields (e.g.
Option 1: Inheritance
Make each subtype extend TravelPackage using single-table or joined-table inheritance. That way, each specialized package is a real subclass of TravelPackage.
Option 2: Composition
Keep TravelPackage as a standalone entity and have a related PackageDetails entity (or 4 separate ones) that hold the specific fields. The PackageType would determine which detail table to join.
Option 1:
Option 2:
And for the rest of them..
Which option is better, and how would i contruct my entities and repositories? Will i have inheritance in every case(1.For packages, 2.For PackageDetails)? Thank you in advance
I already have:
TravelPackage (base class/table): common fields like PackageID, Name, Price, PackageTypeSubtypes like:
SeaPackage
MountainPackage
CruisePackage
ExcursionPackage
Each subtype has its own specific fields (e.g.
Activities, Ship, Guide, etcOption 1: Inheritance
Make each subtype extend TravelPackage using single-table or joined-table inheritance. That way, each specialized package is a real subclass of TravelPackage.
Option 2: Composition
Keep TravelPackage as a standalone entity and have a related PackageDetails entity (or 4 separate ones) that hold the specific fields. The PackageType would determine which detail table to join.
Option 1:
Client(ClientID PK, FirstName, LastName, PassportNo, BirthDate, Email, Phone)
TravelPackage(PackageID PK, Name, Price, PackageType)
SeaPackage(PackageID PK/FK→TravelPackage, Destination, TransportType, LodgingType)
MountainPackage(PackageID PK/FK→TravelPackage, Destination, TransportType, LodgingType, Activities)
Excursion(PackageID PK/FK→TravelPackage, Destination, TransportType, Guide, Duration)
Cruise(PackageID PK/FK→TravelPackage, Ship, Route, DepartureDate, CabinType)
Reservation(ReservationID PK, ClientID FK→Client, PackageID FK→TravelPackage, BookingDate, ...)Option 2:
Client(ID PK, FirstName, LastName, PassportNo, BirthDate, Email, Phone)
Packages(ID PK, Name, Price, DetailsID FK->PackageDetails)
PackageDetails(ID PK, PackageDetailsTypeID( fk -> PackageTypes) )
PackageDetailsTypes(ID, PK, Name)
BeachPackageDetails(ID PK, FK -> PackageDetails, DestinationID, TransportTypeID, AccommodationTypeID )And for the rest of them..
Which option is better, and how would i contruct my entities and repositories? Will i have inheritance in every case(1.For packages, 2.For PackageDetails)? Thank you in advance
