Database relationship inheritance [closed]

Question

I have a vehicle sales store. In my store I sale many kind of vehicles for example cars and boats. And I would like to keep track of the sales.

My vehicles has some attributes as:

• Arrive on store at (a date)
• Brand
• Model
• Color
• Type (is a car or is a boat)

A Boat has some other special attributes as:

• Registry
• Capacity
• Footspace

A car has other attributes as:

• Registry
• Seats

My orders has the following attributes

• Vehicle (should be related with a some vehicle that is related
• Date of contract
• Value
• Buyer ID number (yes could add a data structure for buyer data).

I never have two of the same vehicle, since each vehicle is completely different of another one (even if it was of same brand/color/model), I'm not selling cookies.

How can I represent that in a SQL relational database?

How can a order be expressed?

In special Im trying to undertand how this work inside SQLAlchemy.

Answer 1

Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start.

A vehicle can be either a boat or a car. A good place to start is to think of what identifies a vehicle? Since I don't know your business I'll assume that there exists some kind of vehicle_id, note that this can be a combination of attributes:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
);

If you want type safe sub-types (to prevent someone to add a boat in the car table), you can add a super-key in the vehicle table:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
      UNIQUE (VEHICLE_TYPE, VEHICLE_ID)
);

A boat can be represented by:

CREATE TABLE BOAT
( VEHICLE_ID ... NOT NULL
, other attributes such as registry
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'BOAT')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

and a similar one for car:

CREATE TABLE CAR
( VEHICLE_ID ... NOT NULL
, other attributes such as seats
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'CAR')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

Since registry is an attribute of both car and boat you might concider adding that attribute to vehicle.

An order could be represented as ( I assume the is an order_id that can be used to identify an order):

CREATE TABLE ORDER
( ORDER_ID ... NOT NULL PRIMARY KEY 
, VEHICLE_ID ... NOT NULL
      REFERENCES VEHICLE (VEHICLE_ID)
, BUYER_ID ... NOT NULL
      REFERENCES BUYERS (BUYER_ID) 
, other attributes such as date_of_contract
);

This is just a rough sketch and probably full of false assumptions. I would start with identifying what tables that are involved. Once that is done, start thinking about what identifies a row in each table.