ENTITY RELATION ? How to relate when one of the component of entity can be the component of another entity too?

Question

There are OWNERS who owns TAXIS and owners provide taxis to a cab company. The cab company have DRIVERS. TAXIS could have one or more allocated drivers and drivers can drive multiple taxis.

This means there would be many to many relation between TAXIS and DRIVERS. It's quite straight forward. To my assumption Cab company keeps the records of owners, drivers and taxis in three different tables/entity - OWNERS, TAXIS and DRIVERS. Company knows which taxi belongs to which owners and driven by which drivers.

The twist is, most of the OWNERS are themselves drivers. How should I make relation of this particular thing.

overall: How to relate when one of the component of entity can be the component of another entity too ? (here some owners cab be drivers as well)

I am trying to make an ER-diagram and later on normalization.

Answer 1

This means there would be one to many relation from TAXIS to DRIVERS.

Are you sure? What happens when a TAXI is in the shop for repair? Can't the DRIVER drive a different TAXI in the meantime? If so, you actually want a many to many here -- a DRIVER can drive many TAXIS, and a TAXI can be driven by one or more DRIVERS.

One way you could do it is with composition.

public class Person
{
    public virtual Driver AsDriver { get; set; } // can be null (not a driver)
    public virtual Owner AsOwner { get; set; } // can be null (not an owner)
}

public class Driver
{
    // shares same primary key as Person
    public virtual Person AsPerson { get; set; } // cannot be null
    public virtual ICollection<Taxi> TaxisDriven { get; set; }
}

public class Owner 
{
    // shares same primary key as Person
    public virtual Person AsPerson { get; set; } // cannot be null
    public virtual ICollection<Taxi> TaxisOwned { get; set; }
}

public class Taxi
{
    public virtual ICollection<Driver> Drivers { get; set; }
    public virtual ICollection<Owner> Owners { get; set; }
}

var person = LoadSpecificPerson(personId);
var isBothOwnerAndDriver = person.AsDriver != null && person.AsOwner != null;
var drivesOwnTaxi = isBothOwnerAndDriver && person.AsOwner.TaxisOwned.Any(
    x => person.AsDriver.TaxisDriven.Select(y => y.TaxiId).Contains(x.TaxiId));

Answer 2

There are 2 options:

1. Have 2 tables to address drivers. One for drivers that don't own the taxi and the other for those who own the taxi. This is bad because of many reasons. For example the columns would be repeated in 2 separate tables and a search on a driver would have to be run on each of the tables separately to locate a given dirver. I don't recommend this.

2. The other option is to have 1 table that contains owners and non-owners in 1 table (called party in the diagram below). You could then add a role for each type (Driver only, Owner only, Owner and driver).

The model below represent option 2. The asset assignment is used to record the information about who is driving which car at a given shift (you can flesh this more if you choose).

Note that a driver may become an 'driver owner' but the model is not keeping track of this change however, it could be extended to accommodate this also.