0

I want to have a list that consists of default and custom entries. So, for example, on my software when a dealership is created and want to put a car in their inventory I want them to be able to select from a list of types of cars that already exists in the database and also from types of cars they will be adding as needed but only for that dealership.

How should I build this?

Should I have a table with the default types and another one with the custom types of that dealership and when requested I return both? -> each query will have to access 2 tables.

Should I have a single table with a column with the dealership ID and when requested return all that have the dealership ID and all that don't have any dealership ID (the default ones)? -> each query will have to return results with both no ID and dealership ID.

Should I have a single table with a column with the dealership ID and every time a new dealership is created I duplicate the default entries but their ID on it? -> Can really become monstrous and with duplicate entries.

Is there a better way to do this?

Example:

I just created a dealership and when I try to add a car to my inventory I see the options Mercedes, Volkswagen and Kia. But I want to add a Jaguar and it's not on the list. I want to add it only to my dealerships's list, so the next time I try to add a Jaguar the option is already there.

PS: I have tried searching for a solution for this but couldn't find anything.

Improve this question
5
  • Could you add an example? Commented Nov 16, 2017 at 11:26
  • @juergend example added. Commented Nov 16, 2017 at 11:33
  • @Strawberry Dealership sorry, bad translation. Commented Nov 16, 2017 at 11:33
  • How many manufacturers will there be in the default list? Commented Nov 16, 2017 at 11:38
  • Around 25 probably Commented Nov 16, 2017 at 11:41

1 Answer 1

Reset to default
1

You definitely should avoid creating a separate table for each dealership.

Your second choice is best. You may want a table with columns like this:

  id
  zone_id
  dealership_id 
  maker_id
  maker_name
  model_id
  model_name
  model_number

Then you can run queries like this:

SELECT model_name, model_number
  FROM tbl
 WHERE (dealership_id = :current_dealer: OR dealership_id IS NULL)
   AND (zone_id = :current_zone: OR zone_id IS NULL) 
   AND (maker_id = :current_maker: OR maker_id IS NULL)

You can write a query like this to express the precise business rules for the choices for each dealer site.

Why add zones? Some vehicle makers have regional variations on their products. For example, internal combustion engines in California have more stringent regulations about exhaust than the rest of the US. The vehicle makers have zone offices in California to help coordinate that. Subaru sells a version of its vehicles specifically designed for New England in the Eastern US.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.