Should I be using a foreign key?

Hello,

I’m working on a django project and it allows users to enter some information about trees they have planted on their property. In my model I have a ‘species’ field. I expect that a lot of users will ulitmatlely plant the same trees resulting in the duplication of information entered in this field. Oak as an example might reoccur many times. My thinking is that I should make this a foreign key to a ‘species’ model so that the species information isn’t repeated over and over.

Some things I’m thinking about:

  1. I don’t want to pre-populate all of the possible species myself (because that would result in people asking me regularly to add new species).
  2. I don’t want allow users to enter species themselves which are then able to be selected by other users (because what if they enter bogus species, typos etc).
  3. I think the best option might be to allow users to enter what they like as a text input field on a form. If the foreign key exists then it references the PK of that. If it doesn’t exist it creates it. This way a user can’t see what they, or other users have typed previously, but if it has been entered previously it isn’t stored twice.

I’ve attempted this approach and find that when submitting the form I get an error “Select a valid choice. That choice is not one of the available choices.”. I think this effectively means that it is expecting an integer (relating to the foreign key) rather than text. Is there anything built into django that can facilitate this, or would I need to build some get_or_create logic into the view itself when the form is submitted?

Any help or guidance would be appreciated. I feel like I’m making this more complicated than it might be, but I think it is probably best to get this right from the start (ie set it up in a way which is scalable, and prevents the same content being store in the DB over and over).

Bottom line, this really doesn’t make any sense to me.

If a person can enter in any arbitrary value, what’s the purpose of having a foreign key?

Is there ancillary data associated with a species besides just the name?

Let’s say you enter Oka instead of Oak. Are you then able to add ancillary information associated with an Oka? Now, if I come along later and also enter Oka, do I see the ancillary data that you entered? If not, am I able to enter that data myself? If so, what happens to the data you entered?

Now, leaving all that aside…

More accurately, an FK-based selection list is limited by the queryset defined for that field.

If I were doing this, I’d separate the logic of the select box / text entry Form Field from the Model. Set up a text / select box as a form field, then when the form is posted, do whatever work you want to do with what was entered.

Thanks for getting back to me.

I guess this is where I also need some guidance! I haven’t done a lot with databases and maybe some of these basics are lost on me. My thinking is only this - if ten thousand people enter ‘Oak’ in a standard CharField field, isn’t it better / more efficient to have those referencing a foreign key to another model rather than all that repeated information? That reference is likely to consume a bunch less data (particularly where the species is something longwinded - Oak probably wasn’t a good example in this respect). I expect no ancillary data to be provided at this stage.

Technically, you’re going to have repeated data either way. Either the name of the species or the foreign key reference. Yes, having multiple instances of “Norwegian Blue Spruce” is going to use more space. Yes, there are multiple cases where having that as an FK is going to be more efficient.

The issues are surrounding what I’m interpreting as your intended means of managing those foreign keys. If you don’t display them as selectable options, you may end up with “Norweigian Blue Spruce”, “Norweigan Blue Spruce”, “Norwegian Blu Spruce”, “Norwegian Blue Spruse”, and every other odd combination you can come up with.

If you’re going to let people enter new species, then perhaps provide a separate form for entry. At least that way, it’s an intentional activity. But generally speaking, I would always enforce the selection of a foreign key field in a form referencing it.

1 Like