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.

closed as too broad by Colin 't Hart, RDFozz, SqlWorldWide, Marco, mustaccio Aug 23 '17 at 11:45

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.