I am using django and have three objects: Customer, Location and Department. Each has a related Setting object.
我使用django并有三个对象:客户,位置和部门。每个都有一个相关的Setting对象。
Is it better form to create a single table with optional/null foreign keys?
使用可选/ null外键创建单个表是否更好?
Or to create a different setting object/table for each of the 3 entities?
或者为3个实体中的每一个创建不同的设置对象/表?
1 个解决方案
#1
1
There are a few options
有几个选择
-
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.
创建一个单独的Settings表,并从所有对象到Settings表具有可空的ForeignKey。如果选择此选项,则应创建一个抽象基类,该类具有到Settings表的ForeignKey并从该抽象基类继承。这样,每次创建新模型时都不必添加ForeignKey。
-
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.
创建一个单独的Settings表,并使用Settings表中的GenericForeignKeys来引用您的对象(Customer,Location和Department)。这样做的好处是不需要在所有需要设置的表中添加额外的列。但是,您无法通过Django ORM的普通API与GenericForeignKeys进行数据库连接。你必须使用原始的SQL。此外,select_related不适用于GenericForeignKeys,因此您必须使用prefetch_related。
-
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.
将设置存储在数据库的列中。您应该以某种格式(我喜欢JSON)与数据交互,然后将其序列化为字符串以存储在数据库中。然后,要读取设置,您可以将字符串反序列化为JSON并与之交互。使用此方法,您无需与另一个表联接即可获取设置,并且无需在每次添加新设置时都运行迁移。您也不需要单独的“设置”表。但是,构建查询以查找具有特定设置的对象将是一个痛苦,查询可能也会很慢。
Each option has its pros and cons; so, pick your poison ;)
每种选择都有其优点和缺点;所以,选择你的毒药;)
#1
1
There are a few options
有几个选择
-
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.
创建一个单独的Settings表,并从所有对象到Settings表具有可空的ForeignKey。如果选择此选项,则应创建一个抽象基类,该类具有到Settings表的ForeignKey并从该抽象基类继承。这样,每次创建新模型时都不必添加ForeignKey。
-
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.
创建一个单独的Settings表,并使用Settings表中的GenericForeignKeys来引用您的对象(Customer,Location和Department)。这样做的好处是不需要在所有需要设置的表中添加额外的列。但是,您无法通过Django ORM的普通API与GenericForeignKeys进行数据库连接。你必须使用原始的SQL。此外,select_related不适用于GenericForeignKeys,因此您必须使用prefetch_related。
-
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.
将设置存储在数据库的列中。您应该以某种格式(我喜欢JSON)与数据交互,然后将其序列化为字符串以存储在数据库中。然后,要读取设置,您可以将字符串反序列化为JSON并与之交互。使用此方法,您无需与另一个表联接即可获取设置,并且无需在每次添加新设置时都运行迁移。您也不需要单独的“设置”表。但是,构建查询以查找具有特定设置的对象将是一个痛苦,查询可能也会很慢。
Each option has its pros and cons; so, pick your poison ;)
每种选择都有其优点和缺点;所以,选择你的毒药;)