relational model query for simple person-car example

Question

I'd like some insight on a relational model I have created for PostgreSQL. It has to do with person and car relationships.

CREATE TABLE "person" (
"id" serial NOT NULL PRIMARY KEY,
"name" varchar(300) NOT NULL,
"car_id" integer REFERENCES car (id));

CREATE TABLE "car" (
"id" serial NOT NULL PRIMARY KEY,
"type" varchar(50) NOT NULL);

 CREATE TABLE "car_person_relations" (
"id" serial NOT NULL PRIMARY KEY,
"car_type" varchar(50) NOT NULL REFERENCES "car" ("type"),
"person_id" integer NOT NULL REFERENCES "person" ("id"));

Ultimately, I'd like to get the most popular car type based on how many people have it, i.e. how many "person"s it is associated with. What query can I use to achieve this? And is this relational table (car_person_relations) sufficient to achieve it?

Any insight would be much appreciated

Answer 1

If I understand correctly, this is a simple aggregation with some limiting:

select car_type, count(*) as num_persons
from car_person_relations cpr
group by car_type
order by count(*) desc
limit 1;

Answer 2

First off take a look at your data model, it has a couple fundamental problems. In CAR_PERSONS_RELATIONS table you are attempting to create a FK on car_type. However, to do so car_type would have to be unique in the car table. From the documentation section 5.4.5. Foreign Keys .

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. ... A foreign key must reference columns that either are a primary key or form a unique constraint.

But this would there could only be 1 car for a given type in the table.

Now look at the PERSON table. This table contains a FK to car_id, thus establishing a M:1 to CAR. Basically this relationship says "A person can own only 1 car, but a car can be owned by many persons". Your description and table name indicate this is NOT the relationship you are trying to define.

Correcting requires the following changes:

1. Add car_id to CAR_PERSON_RELATIONS.
2. Remove car_id from PERSON.
3. Remove car_type from CAR_PERSON_RELATIONS.
4. Redefine CAR_PERSON_RELATIONS PK to car_id, person_id.
   Alternately leave the surrogate PK and create UK on car_id,person_id).
5. Create the FKs from CAR_PERSON_RELATIONS to CAR and PERSON.

Revised Model

create table person (
   id   serial       
  ,name varchar(300) not null
  ,constraint person_pk primary key (id) 
);

create table car (
   id serial  
  ,type varchar(50) not null
  ,constraint car_pk primary key (id)  
  );

create table car_person_relations (
   car_id    integer   
  ,person_id integer 
  ,constraint car_person_relations primary key (car_id, person_id)
  ,constraint car_person_relations_2_car_fk
              foreign key (car_id) 
              references  (car.id)
  ,constraint car_person_relations_2_person_fk
              foreign key (person_id) 
              references  (person.id)
): 

And then the necessary query becomes:

select cpr.car_type, count(*) as num_persons
  from car_person_relations cpr
  join car                  c
    on cpr.car_id = c.id 
 group by cpr.car_type
 order by count(*) desc
 limit 1;