Search code examples
djangoormdjango-modelsrelational-databaseentity-relationship

how to make a relation from several objects/tables with a relation to common objects/tables

I am using django and have three objects: Customer, Location and Department. Each has a related Setting object.

Is it better form to create a single table with optional/null foreign keys?

Or to create a different setting object/table for each of the 3 entities?

Solution

  • There are a few options

    1. Create a separate Settings table and have a nullable ForeignKey from all of your objects to the Settings table. If you choose this option, you should create an abstract base class that has a ForeignKey to the Settings table and inherit from that abstract base class. That way you don't have to add the ForeignKey every time you create a new model.

    2. Create a separate Settings table and use GenericForeignKeys from the Settings table to reference your object (Customer, Location, and Department). This has the advantage of not having an extra column in all of your tables that need settings. However, you can't do DB joins with GenericForeignKeys via the Django ORM's normal API. You'd have to use raw sql. Also, select_related doesn't work on GenericForeignKeys so you'd have to use prefetch_related instead.

    3. Store the settings in a column in the database. You should interact with the data in some format (I like JSON) and then serialize it to a string to store in the DB. Then to read the settings, you could deserialize the string back into JSON and interact with it. With this method, you wouldn't need to join with another table to get settings, and wouldn't need to run migrations every time you added new settings. You also wouldn't need a separate Settings table. However, constructing a query to find objects with certain settings would be a pain the query would probably be slow as well.

    Each option has its pros and cons; so, pick your poison ;)