Many to many in many ways? (Search subcategory)
I have a many-to-many relationship, linked by a table " farmer2crop
" linking farmers to crop types (eg rice, wheat, corn, spices, etc.).
eg.
FARMER2CROP
f2c_id
farmer_id
crop_id
However, some cultures require further search for subcategories, eg. spices (cumin, pepper, etc.)
Partly I think all spice subtypes should be part of the crop table, and maybe there should be a parent category, but the client wants the spice to be secondary and most crops are not parent.
So I can have a table with many for many sides connecting farmers2crops:
FARMERS2CROPS2SPICES
f2c_id
spice_id
But it seems a little confusing, are there any suggestions for better design here - or is this a better compromise?
Thanks in advance.
source to share
Logically, you should have the following tables:
FARMER (farmer_id, ...)
CROP (crop_id, farmer_id, ...)
CROPSUB (cropub_id, crop_id ...)
With these three tables, you can connect across all connections (three dots represent other possible fields in the table).
I hope this is helpful to you.
source to share
I would forget the parameter farmers2crops2spices
too complicated for IMO.
Simple option: add column subcategory
table + crop.subcategory_id
(NULLable). However, you can get tables subsubcategory
, subsubsubcategory
etc. (+ crop.subsubcategory_id
etc.).
But my guess is that you will need a nested set for your crop categories in the long run . I suggest using an ORM like Propel (if you are using PHP), which will abstract all the heavy stuff to use such a structure effectively.
source to share