2

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.

  • As you could notice, I'm just a newcomer to SQL world. I have created a FK but, I dont know how to relate it with the child tables (in this case I'm assuming car and boat are child of vehicle) in a mutally exclusive way. In other hand I was able to create a FK from orders to vehicle, but that isn't enough. I don't have idea in how to accomplish this OOP like operation inside a RDBMS. I know how to code that in Python, but not how to represent it RDMBS, I don't even know how to start. – Lin Aug 22 '17 at 21:31
  • 1
    @Lin - is this for a specific DBMS? – Max Vernon Aug 22 '17 at 21:43
  • No, is more theoretical, I could use sqlite3, MySQL or PostgreSQL. In fact I could use any free DBMS. Since I'm a pythonic person, I'm trying to look by the perspective of SQLAlchemy. – Lin Aug 22 '17 at 21:59
2

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.