Inheriting Parent Table with identifier (Postgres)

Question

Sorry if this is a relatively easy problem to solve; I read the docs on inheritance and I'm still confused on how I would do this.

Let's say I have the parent table being car_model, which has the name of the car and some of it's features as the columns (e.g. car_name, car_description, car_year, etc). Basically a list of cars.

I have the child table being car_user, which has the column user_id.

Basically, I want to link a car to the car_user, so when I call SELECT car_name FROM car_user WHERE user_id = "name", I could retrieve the car_name. I would need a linking component that links car_user to the car.

How would I do this?

I was thinking of doing something like having car_name column in car_user, so when I create a new data row in car_user, it could link the 2 together.

What's the best way to solve this problem?

It almost sounds like you want a foreign key relationship, not an inherited table. Maybe you know this already, but an inherited table essentially will add rows from inheirted tables to the base table when queried (unless you prefix the base table with "only"). It does not guarantee any records exists in the base table — Hambone
– Hambone, Commented Jan 30, 2020 at 22:59

Vesa Karjalainen · Accepted Answer · 2020-01-30 23:38:56Z

1

Inheritance is something completely different. You should read about foreign keys and joins.

If one user drives only one car, but many users can drive same car, you need to build one-to-many -relation. Add car_name to your user table and JOIN using that field.

edited Jan 30, 2020 at 23:38

answered Jan 30, 2020 at 23:01

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lego490 Over a year ago

What if I wanted a user to be able to drive many different cars? And many users can drive the same car as well? Thanks, I think that's what I wanted to do!

Vesa Karjalainen Over a year ago

If one car can have many users and one user can drive many cars, you need a many-to-many -relationship. This is usually done with a third table between the two you already have. Any basic textbook on relational databases teaches these two relationships.

Lego490 Over a year ago

The thing is - I only need to add an user_id column to the car_model (car_user is really just car_model with user_id attached). Would it be better to just copy the columns from car_model into car_users and then just use INSERT INTO? Since if I want to retrieve the data from the joined table, I would have to do basically the same thing (access joined table, then either make columns have everything or pull additional data from car_model table)

Vesa Karjalainen Over a year ago

No. Read a textbook or do an online course on relational theory, learn 3rd normal form (further than 3rd are for special cases only) and learn everything on foreign keys and joins.

Collectives™ on Stack Overflow

Inheriting Parent Table with identifier (Postgres)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related