Help with Query

I have a db of following tables (with example data)

years
{
    "id": 1,
    "name": 2011,
    "created_at": "2022-03-09 13:18:47",
    "updated_at": "2022-03-09 13:18:47"
  }

makes
{
    "id": 1,
    "name": "Toyota",
    "created_at": "2022-03-09 13:19:09",
    "updated_at": "2022-03-09 13:19:09"
  }

vehicle_models
{
    "id": 1,
    "name": "Corolla",
    "created_at": "2022-03-09 13:19:58",
    "updated_at": "2022-03-09 13:19:58",
    "make_id": 1
  }


PIVOT :
make_year
{
    "id": 1,
    "make_id": 2,
    "year_id": 1,
    "created_at": null,
    "updated_at": null
  }
vehicle_model_year
{
    "id": 1,
    "vehicle_model_id": 1,
    "year_id": 1,
    "created_at": null,
    "updated_at": null
  }


year_model_products
{
    "id": 3,
    "created_at": "2022-03-11 03:52:32",
    "updated_at": "2022-03-11 03:52:32",
    "model_id": 3,
    "year_id": 2,
    "prod_ids": "[632910392,921728736]"
  }

How do I query : 2011 then get a list of makes on year 2011 along with the vehicle_models of that year and makes , then ultimately get the list of product ids with the specific year make models?

Tray2

4 years ago

Level 73

Dude, that is one of the worst database models I've seen in a long while.

You are mixing one-to-many and many-to-many and I would guess one-to-one as well.

I suggest you take some time to read this post

https://tray2.se/posts/database-design

Then you need to ask your self these two questions

Can x have more than one y?
Can y have more than one x?

So can a make have more than one year?

Can a year have more than one make?

If one of those two questions is yes then use a one-to-many relation if, both are yes, use a many-to-many.

I suggest going through the columns of your tables one by one and ask those questions.

But first make sure to read the post I linked.

1 like

ntn0de

4 years ago

Level 1

@Tray2 Read the post, that's a really nice explanation on the db design you got there. Pinned it as a reference.

In my case, (please read my use case and suggest alternative)

So can a make have more than one year?

YES. -- A Toyota is released on 2012 and 2013, that's on my store

Can a year have more than one make?

YES. -- 2012 has Toyota and Audi , in my store

I designed the db in rush really appreciate the honest feedback.

My use case is.

I let a user select years first. say 2011
it will list out a list of "makes" available in my store. say toyota
upon selection of make, it will list out the list of models available in my store of 2011's Toyota.
after selecting the model (say Corolla), it will list out the list of accessories that will fit on 2011 Toyota Corolla.

I didn't want to repeat the years, make and vehicle-models; at the end, I need is products list(should be an array of ids, per requirement) hence used the year_model_products table to store it.

Tray2

4 years ago

Level 73

@ntn0de I would make a cars table the has the foreign keys for make and year. Trying to keep it kiss

Please or to participate in this conversation.