-1

I have an application where the requirements are to show a list of (just for example) car manufacturers. When you click on one of the manufacturers, a list of car models show up. You can then click on the car model to show other data related to the model.

My problem is that one of the manufacturers is to be treated as a car model. There will be only one car model under this manufacturer. The below is an example of the current database structure. As you can see, there is a MegaCar manufacturer and there is only one model under this manufacturer (with the same name). When you click on the MegaCar manufacturer, it should directly load the MegaCar model instead of showing the list of models.

+---------------+
| manufacturers |
+---------------+
| id  | name    |
+-----+---------+
| 1   | Ford    |
+-----+---------+
| 2   | Toyota  |
+-----+---------+
| 3   | MegaCar |
+-----+---------+

+--------------------------------+
| models                         |
+--------------------------------+
| id | manufacturer_id | name    |
+----+-----------------+---------+
| 1  | 1               | Focus   |
+----+-----------------+---------+
| 2  | 1               | Mustang |
+----+-----------------+---------+
| 3  | 2               | Camry   |
+----+-----------------+---------+
| 4  | 2               | Corolla |
+----+-----------------+---------+
| 5  | 3               | MegaCar |
+----+-----------------+---------+

I'm trying to decide on a database design and application design to represent this relationship. Basically when someone clicks on the manufacturer MegaCar, I will need to load the model page for MegaCar (where models.id=5), not the manufacturer page (where manufacturers.id=3). I will need to update the database schema for this. My current line of thinking is to add a isModel flag to the manufacturers table and my code would look something like this:

object = getSelectedManufacturer();
if (object.isModel) {
    singleModel = query('
        SELECT models.id FROM models 
        INNER JOIN manufacturers ON models.manufacturers_id = manufacturers.id
        WHERE manufacturers.id = [object.id]
        LIMIT 1
    ')
    renderModelPage(singleModel)
}
else {
    listOfModels = query('
        SELECT * FROM models 
        WHERE models.manufacturer_id = [object.id]
    ')
    renderManufacturerPage(listOfModels) // model list page
}

The problem I have with this is it might be confusing to other people who see my code. Also it seems wrong that the relationship between these tables is one-to-many, but I'm making an exception for one record.

I had another idea to add a nullable modelId column to the manufacturers table, but I didn't like this for the same reason as above. Also, I've seen some Q&As around here advising against using NULLs.

What would be the proper way to design this application?

  • 1
    I imagine the requirement for "one of the manufacturers is to be treated as a car model" will probably not be very long-lived. For example, on the page that shows all the models for a given manufacturer, if anything else about the manufacturer is added to that page/screen, then automatically jumping past that screen into the model details will no longer be desirable. – Graham Apr 22 at 17:04
  • @Graham you are probably right, but this is how the application was designed so I have to implement this accordingly. If this is eventually changed, all we would have to do is turn off the isModel flag. – Kodos Johnson Apr 24 at 19:39
  • 1
    One-to-one is just a one-to-many where there is only one row on the foreign-key side. – Robert Harvey Apr 25 at 5:01
5

My problem is that one of the manufacturers is to be treated as a car model. There will be only one car model under this manufacturer.

Ok.

When you click on the MegaCar manufacturer, it should directly load the MegaCar model instead of showing the list of models.

Stop! You are mixing up concerns here, persistence and presentation.

Both things are very different concerns each of which faces different problems that change at different times and for different reasons due to different actors. You should not couple them so tightly.

The persistence shouldn't depend on the view because changing the view is cheap and it happens oftener than we think: new features, new UX, rebranding, marketing, overhauling... The view layer is terribly volatile and the persistence cannot follow such volatility.

I'm trying to decide on database design and application design to represent this relationship.

You already have it and it's ok.

To me, object.isModel is out of place because it doesn't only couples persistence and view-specific requirements, it also puts at the same level two different abstractions models and manufacturers. To my experience, object.isModel carries the seed of the evil.

Why? Because it's prone to cause a proliferation if/else blocks all over the source code. Something that will extend up to the SQL statements too. Maybe it's not obvious yet, but it will be.

Finally, it opens a window for other developers to solve similar issues with the same solution: mix up of abstractions, coupling and code segmentation. Going that way will make the solution (as a whole) less consistent, harder to reason about and harder to maintain in the long run.

Q: What would be the proper way to design this application?

If consistency and cleanliness matter, treat these cases as any other manufacturers-models relationship. If the view layer demands direct access to the model bypassing the manufacturer; make the application layer to solve this.2

The solution of bypassing manufacturer should be implemented and encapsulated in the view layer and be configurable. This way, the whole application gains in resilience and versatility.


1: The view is only used for querying. It doesn't need to be mapped to its own entity in the source code.

2: Don't worry yet about the number of calls to DB. Implement the number of calls you need. Solve the problem first, optimize later.

9
+50

Think longer term - eventually... there will be a MegaCar 2.0 Or there will be another new car company with only the one model. Or one model companies all go out of business.

Keep it the way you have it as if there were multiple models for each manufacturer, and deal with it when you get the results of your select statement. Check number of rows returned, if 1 then forward immediately, otherwise build/show the list.

object = getSelectedManufacturer();
if (object.isModel) {
    listOfModels = query('
        SELECT * FROM models 
        WHERE models.manufacturer_id = [object.id]
    ')
    if(listOfModels.length()==1){
        renderModelPage(listOfModels)
    }else{
        renderManufacturerPage(listOfModels) 
    }
}
  • Thanks for the suggestion. I was actually thinking of going the same route but in our situation only a specific record from the first table should load the corresponding record from the second table directly. I.e. in our hypothetical car model/manufacturer example, we only want the MegaCar manufacturer specifically to load the MegaCar model and still show the manufacturer page for other manufacturers even if they only have one model. – Kodos Johnson Apr 19 at 7:13
  • 2
    This is the correct answer... Don't hard code the manufacturer/model Ids into your code, instead account for the situation that triggers an automatic loading of the model. If you don't want the trigger for auto-loading to be a manufacturer having a single model, then add an attribute to the manufacturer (perhaps autoloadModel or similar) that indicates that the auto-loading should happen, then code to that. This will make the code both more understandable and more flexible. Because we know MegaCar2 is gonna happen, despite your current plans. – Eric King Apr 22 at 16:59
  • 1
    ivanivan I noticed that you put everything under the check for if (object.isModel) so it checks both that the flag is on and the number of models is 1. But presumably there would be an else {renderManufacturerPage()} at the end of the pseudo code. This what I ended up doing. I just simplified it to if (object.isModel && number of models ==1 ) – Kodos Johnson Apr 24 at 19:44
  • 1
    What happens when this rule (object.isModel && number of models ==1 ) changes because new manufacturers-models appears with different needs? – Laiv Apr 25 at 13:23
  • 1
    The question was addressed to make you think about that isModel. If rules changes you have to get rid of It or keep bloating the table with other flags or parameters. These fields or settings are totally out of place in "manufacturer". – Laiv Apr 26 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.